欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Mysql-sqoop-HDFS 数据交换实验

程序员文章站 2022-05-02 19:08:15
...

感谢@吴飚飚哥的细心指导,向飚哥精益求精的精神学习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. 必须启动服务才能操作数据库

Mysql-sqoop-HDFS 数据交换实验

数据库端口: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-HDFS 数据交换实验

Mysql-sqoop-HDFS 数据交换实验

九、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数据库