Mysql-sqoop-HDFS 数据交换实验

感谢@吴飚飚哥的细心指导，向飚哥精益求精的精神学习SQOOP：Apache基金会下一个开源产品，Hadoop家族的一个产品，关系型数据库与HDFS文件系统之间进行数据交换，

感谢@吴飚 飚哥的细心指导，向飚哥精益求精的精神学习

SQOOP：Apache基金会下一个开源产品，Hadoop家族的一个产品，关系型数据库与HDFS文件系统之间进行数据交换，数据迁移的一个工具。

一、环境描述

Mysql版本：mysql-installer-community-5.5.27.1 32位

Mysql for Windows 7 32位：我把mysql数据库安装在了自己win7的笔记本上，这样的好处就是减少了虚拟机 master slave的开销和使用空间还可以多利用一台机器的资源，如果你的虚拟机资源很紧张的话也可以这样部署。

Linux ISO：CentOS-6.0-i386-bin-DVD.iso 32位

JDK version："1.6.0_25-ea" for linux

Hadoop software version：hadoop-0.20.205.0.tar.gz for linux

Mysql version：mysql-installer-community-5.5.27.1 32位 for windows

sqoop version：sqoop-1.2.0-CDH3B4.tar.gz for linux

主机名

IP

节点名

备注

h1

192.168.2.102

master

namenode和jobtracker

h2

192.168.2.103

slave1

datanode和tasktracker

H4

192.168.2.105

slave2

datanode和tasktracker

MySQL部署在宿主环境中： 参考飚哥风靡版

二、下载软件安装包

帖子名：hadoop第十周cloudera版sqoop包和hadoop-core-jar包下载

帖子网址：?mod=viewthread&tid=36867&fromuid=303

欢迎大家下载使用
sqoop-1.2.0-CDH3B4.tar.gz 和 hadoop-core-jar包 mysql-connector-java-5.1.22-bin.jar 包 是我们这次用到的

三、把下载好的文件加载到linux并解压

下载

[grid@h1 ~]$ pwd

/home/grid/

-rwxrw-rw-. 1 grid hadoop 67339212 4月 12 2011 hadoop-0.20.2-CDH3B4.tar.gz

-rwxrw-rw-. 1 grid hadoop 832960 11月 19 16:06 mysql-connector-java-5.1.22-bin.jar

-rwxrw-rw-. 1 grid hadoop 1543137 4月 12 2011 sqoop-1.2.0-CDH3B4.tar.gz

解压包

[grid@h1 ~]$ tar -zxvf hadoop-0.20.2-CDH3B4.tar.gz

[grid@h1 ~]$ tar -zxvf sqoop-1.2.0-CDH3B4.tar.gz

[grid@h1 ~]$ pwd

/home/grid/

drwxr-xr-x. 15 grid hadoop 4096 2月 22 2011 hadoop-0.20.2-CDH3B4 解压后目录

-rwxrw-rw-. 1 grid hadoop 67339212 4月 12 2011 hadoop-0.20.2-CDH3B4.tar.gz

-rwxrw-rw-. 1 grid hadoop 832960 11月 19 16:06 mysql-connector-java-5.1.22-bin.jar

drwxr-xr-x. 11 grid hadoop 4096 2月 22 2011 sqoop-1.2.0-CDH3B4 解压后目录

-rwxrw-rw-. 1 grid hadoop 1543137 4月 12 2011 sqoop-1.2.0-CDH3B4.tar.gz

四、拷贝hadoop-core-0.20.2-CDH3B4.jar和mysql-connector-java-5.1.22-bin.jar到/home/grid/sqoop-1.2.0-CDH3B4/lib/目录下

[grid@h1 ~]$ cd hadoop-0.20.2-CDH3B4

[grid@h1 hadoop-0.20.2-CDH3B4]$ cp hadoop-core-0.20.2-CDH3B4.jar /home/grid/sqoop-1.2.0-CDH3B4/lib/

[grid@h1 grid]$ cp mysql-connector-java-5.1.22-bin.jar /home/grid/sqoop-1.2.0-CDH3B4/lib/

五、配置sqoop-1.2.0-CDH3B4/bin/configure-sqoop文件

[grid@h1 conf]$ cd ../bin

[grid@h1 bin]$ pwd

/home/grid/sqoop-1.2.0-CDH3B4/bin

[grid@h1 bin]$ vim configure-sqoop

注释掉hbase和zookeeper检查（除非你准备使用HABASE等HADOOP上的组件）

# Check: If we can't find our dependencies, give up here.

if [ ! -d "${HADOOP_HOME}" ]; then

echo "Error: $HADOOP_HOME does not exist!"

echo 'Please set $HADOOP_HOME to the root of your Hadoop installation.'

exit 1

fi 只有红色需要修改

#if [ ! -d "${HBASE_HOME}" ]; then

#echo "Error: $HBASE_HOME does not exist!"

#echo 'Please set $HBASE_HOME to the root of your HBase installation.'

#exit 1

#fi

#if [ ! -d "${ZOOKEEPER_HOME}" ]; then

# echo "Error: $ZOOKEEPER_HOME does not exist!"

# echo 'Please set $ZOOKEEPER_HOME to the root of your ZooKeeper installation.'

# exit 1

#fi

六、配置所需环境变量

在哪里执行sqoop，就在哪台机器上设置一下

[grid@h1 grid]$ vim .bashrc 添加

export JAVA_HOME=/usr

export JRE_HOME=/usr/java/jdk1.6.0_25/jre

export PATH=/usr/java/jdk1.6.0_25/bin:/home/grid/hadoop-0.20.2/bin:/home/grid/pig-0.9.2/bin:$PATH

export CLASSPATH=./:/usr/java/jdk1.6.0_25/lib:/usr/java/jdk1.6.0_25/jre/lib

export PIG_CLASSPATH=/home/grid/hadoop-0.20.2/conf

export HIVE_HOME=/home/grid/hive-0.8.1

export HIVE_CONF_DIR=$HIVE_HOME/conf

export HADOOP_HOME=/home/grid/hadoop-0.20.2

作用：让sqoop程序从环境变量里找到hadoop的位置，从而找到hadoop配置文件，知道集群的部署情况

[grid@h1 grid]$ echo $HADOOP_HOME 检查一下没有问题

/home/grid/hadoop-0.20.2

七、配置启动HADOOP集群

H1机器 master

[grid@h1 bin]$ pwd

/home/grid/hadoop-0.20.2/bin

[grid@h1 bin]$ ./start-all.sh

starting namenode, logging to /home/grid/hadoop-0.20.2/bin/../logs/hadoop-grid-namenode-h1.out

h2: starting datanode, logging to /home/grid/hadoop-0.20.2/bin/../logs/hadoop-grid-datanode-h2.out

h4: starting datanode, logging to /home/grid/hadoop-0.20.2/bin/../logs/hadoop-grid-datanode-h4.out

h1: starting secondarynamenode, logging to /home/grid/hadoop-0.20.2/bin/../logs/hadoop-grid-secondarynamenode-h1.out

starting jobtracker, logging to /home/grid/hadoop-0.20.2/bin/../logs/hadoop-grid-jobtracker-h1.out

h2: starting tasktracker, logging to /home/grid/hadoop-0.20.2/bin/../logs/hadoop-grid-tasktracker-h2.out

h4: starting tasktracker, logging to /home/grid/hadoop-0.20.2/bin/../logs/hadoop-grid-tasktracker-h4.out

[grid@h1 bin]$ jps

17191 JobTracker

16955 NameNode

17442 Jps

17121 SecondaryNameNode

H2机器 slave

[grid@h2 ~]$ jps

32523 Jps

17188 TaskTracker

13727 HQuorumPeer

17077 DataNode

H4机器 slave

[grid@h4 ~]$ jps

27829 TaskTracker

26875 Jps

17119 DataNode

31083 Jps

11557 HQuorumPeer

[grid@h1 bin]$ ./hadoop dfsadmin –report 检查hadoop集群状态

Configured Capacity: 19865944064 (18.5 GB)

Present Capacity: 8741523456 (8.14 GB)

DFS Remaining: 8726482944 (8.13 GB)

DFS Used: 15040512 (14.34 MB)

DFS Used%: 0.17%

Under replicated blocks: 4

Blocks with corrupt replicas: 0

Missing blocks: 0

-------------------------------------------------

Datanodes available: 2 (2 total, 0 dead) --2个节点存活无shutdown

Name: 192.168.2.103:50010 -- slaves h2

Decommission Status : Normal --状态正常

Configured Capacity: 9932972032 (9.25 GB)

DFS Used: 7520256 (7.17 MB)

Non DFS Used: 5447561216 (5.07 GB)

DFS Remaining: 4477890560(4.17 GB)

DFS Used%: 0.08%

DFS Remaining%: 45.08%

Last contact: Fri Dec 14 18:10:11 CST 2012

Name: 192.168.2.105:50010 -- slaves h4

Decommission Status : Normal --状态正常

Configured Capacity: 9932972032 (9.25 GB)

DFS Used: 7520256 (7.17 MB)

Non DFS Used: 5676859392 (5.29 GB)

DFS Remaining: 4248592384(3.96 GB)

DFS Used%: 0.08%

DFS Remaining%: 42.77%

Last contact: Fri Dec 14 18:10:11 CST 2012

集群正常启动了

八、启动mysql，创建leo用户进行sqoop连接

1. 必须启动服务才能操作数据库

数据库端口：3306

Mysqll服务名：MySQL55

Mysql状态：已经启动

创建leo用户

grant all privileges on *.* to 'leo'@'%' identified by 'leo' with grant option;

select * from mysql.user;

flush privileges;

九、mysql 中建立sqoop库，网站空间，test表，添加数据

[grid@h1 bin]$ ping 192.168.2.110 检查linux for windows 的连接性

PING 192.168.2.110 (192.168.2.110) 56(84) bytes of data.

64 bytes from 192.168.2.110: icmp_seq=1 ttl=64 time=14.5 ms

64 bytes from 192.168.2.110: icmp_seq=2 ttl=64 time=3.43 ms

64 bytes from 192.168.2.110: icmp_seq=3 ttl=64 time=9.68 ms

64 bytes from 192.168.2.110: icmp_seq=4 ttl=64 time=0.549 ms

^C

--- 192.168.2.110 ping statistics ---

4 packets transmitted, 4 received, 0% packet loss, time 3630ms

rtt min/avg/max/mdev = 0.549/7.063/14.577/5.453 ms

[grid@h1 grid]$ mysql -h192.168.2.110 -uleo –pleo 使用leo用户登录数据库

命令列表

show databases; 显示当前有哪些数据库

create database sqoop; 创建sqoop数据库