一、遇到的问题(关键错误日志):
18/08/30 19:38:42 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
18/08/30 19:38:43 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
18/08/30 19:38:43 INFO tool.CodeGenTool: Beginning code generation
18/08/30 19:38:43 ERROR manager.SqlManager: Error executing statement: 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.
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.
..........
Caused by: java.net.ConnectException: Connection refused (Connection refused)
...........
从报错可以看出来:1、连接数据库失败。2、发给数据库服务的包没有回复。3、访问被拒绝。
二、解决方案:
1.网上说是数据库服务等待连接超时的问题:
mysql﹥ show global variables like 'wait_timeout'; //默认是28800秒,也就是8小时,超过8小时就连不上了。
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| wait_timeout | 28800 |
+---------------+---------+
修改ubuntu mysql 用户配置文件,添加一行:
vim /etc/mysql/my.cnf
添加以下内容:
[mysqld]
wait_timeout = 1814400 #改成21天,反正8小时是太小了
重启Mysql服务,mysql﹥ show global variables like 'wait_timeout'; 发现修改成功:
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| wait_timeout | 1814400 |
+---------------+---------+
然而。。。。并没有什么卵用。。。还是连不上,可能我不是这种情况。
2. 从stackoverflow的一个帖子看到可能和Mysql默认使用的3306端口有关:
看看这个端口状态:
$ netstat -an | grep "3306"
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
好像Mysql服务只监听本地(127.0.0.1)的端口?在/etc/mysql/下:
$ sudo grep -irn '127.0.0.1' ./
./mysql.conf.d/mysqld.cnf:43:bind-address = 127.0.0.1
vim ./mysql.conf.d/mysqld.cnf +43, 看看是啥:
lc-messages-dir = /usr/share/mysql
39 skip-external-locking
40 #
41 # Instead of skip-networking the default is now to listen only on
42 # localhost which is more compatible and is not less secure.
43 bind-address = 127.0.0.1
44 #
45 # * Fine Tuning
46 #
Instead of skip-networking the default is now to listen only on localhost which is more compatible and is not less secure.
FUCK!!!这就是问题所在了,翻译过来就是 Mysql默认工作在本地监听模式,只监听本地主机!!!!
对网络上的其他机器发过来的包是不做回复的!!!!
这也解释了报错日志里的:The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
把"bind-address = 127.0.0.1"删掉或者注释掉,重启Mysql服务。
再次运行sqoop命令从Hive导出数据到Mysql,成功~~~