Sqoop导出Hive数据到MySQL，报错：jdbc4.CommunicationsException: Communications link failure

一、遇到的问题(关键错误日志)：

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，成功~~~