mysql异常CommunicationsException: Communications link failure的解决方案

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.

原因查找

正常可以连接msql suse 运行以下命令情况如下：

1. 运行命令netstat -talnp验证3306端口是否打开：

test:~ # netstat -talnp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name 
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 4620/mysqld

。。。。。。

2.运行命令 ps aux|grep mysql看mysql服务是否在运行

test:~ # ps aux|grep mysql
root 4254 0.0 0.0 2940 1332 ? S 06:05 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/test.pid
mysql 4620 0.1 0.9 301476 37428 ? Sl 06:05 0:13 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/test.err --pid-file=/usr/local/mysql/data/test.pid --socket=/usr/local/mysql/mysql.sock --port=3306
root 8002 0.0 0.0 2264 680 pts/2 S+ 09:21 0:00 grep mysql

3.查看3306端口运行的进程 lsof -i :3306
test:~ # lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 4620 mysql 12u IPv4 10559 0t0 TCP *:mysql (LISTEN)

问题机器运行上面运行命令

1.netstat -talnp 看不到绑定到3306端口的tcp连接

2.ps aux|grep mysql 显示服务在运行

3. lsof -i :3306查看mysql是否运行在3306端口，未有显示mysql服务没有运行在3306端口

结论：mysql服务虽然在运行，可以通过teminal操作，但没有运行在3306端口，所以通过jdbc连接连接不上。

4.若不是以上问题，则可能是大量数据访问情况下，mysql connection连接有可能失效 或者长时间不访问，connection会失效 

这时需要 修改MySQL的参数. /etc/my.cnf 添加 
[mysqld]
wait_timeout=31536000
interactive_timeout=31536000
重启mysql 
service mysql restart（5.7版本mysql可能是service mysqld restart)

后查看网络资料mysql配置中skip-networking未注释，参数skip-networking起的作用是：mysql不再在TCP / IP端口上进行监听，与mysqld的所有互动都必须通过Unix套接字或命名管道进行。

配置文件注释如下

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!

将参数skip-networking注释重启mysql服务，jdbc程序连接mysql正常。

网络遇见同样异常，可能的其它原因：

1.

tomcat程序连接服务器mysql数据库，报

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driverhas not received any packets from the server.
错误，后来用java程序直接连接数据库，还是一样的错误，数据库连接，用户名密码都没有问题。
在网上查了很多情况，什么数据库用户权限设置，还是数据库连接等待时间(wait_timeout)设置，都没用。
而连接本地的数据库就没问题，本地数据库版本：5.0.37，服务器数据库版本：5.0.19，JDBC驱动版本：
mysql-connector-java-5.1.9-bin.jar。
这个问题困扰了一天，后来想想换换5.0的驱动看看怎么样，结果在网上找了个高版本比如mysql-connector-java-5.1.14-bin.jar的驱动，结果OK了。

2.以下来自stackoverflow

CommunicationsException: Communications link failure

If you get a SQLException: Connection refused or Connection timed out or a MySQL specific CommunicationsException: Communications link failure, then it means that the DB isn't reachable at all. This can have one or more of the following causes:

IP address or hostname in JDBC URL is wrong.
Hostname in JDBC URL is not recognized by local DNS server.
Port number is missing or wrong in JDBC URL.
DB server is down.
DB server doesn't accept TCP/IP connections.
DB server has run out of connections.
Something in between Java and DB is blocking connections, e.g. a firewall or proxy.

To solve the one or the other, follow the following advices:

Verify and test them with ping.
Refresh DNS or use IP address in JDBC URL instead.
Verify it based on my.cnf of MySQL DB.
Start the DB.
Verify if mysqld is started without the --skip-networking option.
Restart the DB and fix your code accordingly that it closes connections in finally.
Disable firewall and/or configure firewall/proxy to allow/forward the port.