sqoop与mysql的连接_sqoop连接mysql导入数据到hive

hadoop集群环境，hadoop1.0.4

192.168.1.23 hadoopmaster

192.168.1.25 hadoopnodeone

192.168.1.27 hadoopnodetwo

mysql 5.5.29

database:test

hive用户：hive

Sqoop 1.2.0-CDH3B4

一、在mysql中建表并insert测试数据

create table student(sid int,name varchar(20),age

int,load_date datetime, primary key(sid));

insert into student values(1,'tom',10,now());

insert into student values(2,'alice',12,now());

insert into student values(3,'kate',10,now());

insert into student values(4,'mike',12,now());

二、测试sqoop连接mysql查看table-list

sqoop list-tables --connect jdbc:mysql://localhost/mysql

--username root --password 123456

测试成功

三、测试将student表写入hdfs

sqoop import --connect jdbc:mysql://192.168.1.23:3306/test

--username root --password 123456  -m 1 --table

student;

测试报错：

ERROR manager.SqlManager: Error reading database metadata:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:

Communications link failure

The last packet sent successfully to the server was 0

milliseconds ago. The driver has not received any packets from the

server.

将localhost改为127.0.0.1也报一样的错

四、在网上查了说要修改mysql绑定的地址，不能用localhost或者是127.0.0.1，需要用mysql服务器的地址。

mysql服务器停止 hadoop@hadoopmaster:~/mysql$ bin/mysqladmin -u root

-p shutdown

mysql服务器启动，绑定IP shell>bin/mysqld_safe

--user=hadoop --bind-address=192.168.1.23 &

测试sqoop连接mysql查看  table-list sqoop

list-tables --connect jdbc:mysql://192.168.1.23:3306/test

--username root --password 123456 ，测试结果，报错：ERROR

manager.SqlManager: Error reading database metadata:

java.sql.SQLException: Access denied for user 'root'@'hadoopmaster'

(using password:

YES)；网上查是root/123456登陆访问test失败，网上的解决方案有很多种，但需要看mysql的版本，例如： GRANT

ALL PRIVILEGES ON databaseName.* TO '%'@'%';  #允许所有用户查看和修改databaseName数据库模式的内容，否则别的IP连不上本MYSQL GRANT ALL

PRIVILEGES ON databaseName.* TO ''@'%';

#不是很清楚，可能是匿名吧 对于mysql5.5.29不起作用

另外一种方法是修改mysql数据库中的user表

select host

,user,password,max_connections,plugin,authentication_string from

user where user='root';

update user set

Password=PASSWORD('123456') where user='root';

flush

privileges;//必须

再测试show

tablelist：table-list sqoop list-tables --connect

jdbc:mysql://192.168.1.23:3306/test --username root

--password

测试成功

五、再测试将student表写入hdfs

sqoop import --connect jdbc:mysql://192.168.1.23:3306/test

--username root --password 123456  -m 1 --table

student;

测试报错：ERROR manager.SqlManager: Error reading database

metadata: java.sql.SQLException: Access denied for user

'root'@'hadoopnodeone' (using password: YES)

ERROR manager.SqlManager: Error reading database metadata:

java.sql.SQLException: Access denied for user

'root'@'hadoopnodetwo' (using password: YES)

hdfs集群上的其他节点都需要连接mysql导入数据，需要把其他节点的用户访问权限也添加到mysql，用类似以下语句修改mysql数据库的user表，加入hadoopnodeone,hadoopnodetwo，若要导入hive还需要加入hive@hadoopmaster的访问权限，注意修改表后需要运行flush

privileges：

insert into

user(host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv

, Drop_priv , Reload_priv , Shutdown_priv , Process_priv ,

File_priv , Grant_priv , References_priv , Index_priv , Alter_priv

, Show_db_priv , Super_priv , Create_tmp_table_priv ,

Lock_tables_priv , Execute_priv , Repl_slave_priv ,

Repl_client_priv , Create_view_priv , Show_view_priv ,

Create_routine_priv , Alter_routine_priv , Create_user_priv ,

Event_priv , Trigger_priv , Create_tablespace_priv , ssl_type ,

ssl_cipher , x509_issuer , x509_subject , max_questions ,

max_updates , max_connections , max_user_connections , plugin

,authentication_string)

select

'hadoopnodetwo',User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv

, Drop_priv , Reload_priv , Shutdown_priv , Process_priv ,

File_priv , Grant_priv , References_priv , Index_priv , Alter_priv

, Show_db_priv , Super_priv , Create_tmp_table_priv ,

Lock_tables_priv , Execute_priv , Repl_slave_priv ,

Repl_client_priv , Create_view_priv , Show_view_priv ,

Create_routine_priv , Alter_routine_priv , Create_user_priv ,

Event_priv , Trigger_priv , Create_tablespace_priv , ssl_type ,

ssl_cipher , x509_issuer , x509_subject , max_questions ,

max_updates , max_connections , max_user_connections , plugin

,authentication_string from user where user='root' and

host='hadoopmaster';

再测试将student表写入hdfs，

sqoop list-tables --connect jdbc:mysql://localhost/test

--username root --password 123456

测试成功

六测试将mysql数据导入hive

sqoop import --hive-import --connect

jdbc:mysql://192.168.1.23:3306/test --username root --password

123456  -m 1 --table student;

测试成功：

hive> select * from student;

OK

1 tom 10 2013-05-02 15:49:38.0

2 alice 12 2013-05-02 15:50:26.0

3 kate 10 2013-05-02 15:49:38.0

4 mike 12 2013-05-02 15:49:39.0

Time taken: 0.705 seconds