记一次CentOS7MySQL排坑历程

jiezi

5 年前

今天在 CentOS7 中安装了 mysql5.7，然后为了测试数据库环境是否配置成功，便写了个基于 mybatis+Spring 的 java web 程序连接操作 mysql 数据库，于是就一些发生了令人感到很烦的报错和故事：

当程序涉及到关于数据库的操作如查询、插入等操作时，首先浏览器访问会很慢，进度条一直旋转，然后页面会报 500 错误：org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exception。然而我在 CentOS7 服务端和 Windows 本地的 Navicat 连接 mysql 都没问题。。。

看着这似乎是 mybatis 引起的错误，所以先检查 mapper 的 xml 中 sql 语句是否有错误，例如参数的格式转化、resultType 为 JavaBean、resultMap 需要定义、JavaBean 和 dao 的引入等。

检查中并没有发现什么问题，而且错误仍然存在。

查看 tomcat 的日志文件，发现在报错开始部分出现了这个错误。经过查询，发现这个错误的原因是：同一个 ip 在短时间内产生太多（超过 mysql 数据库 max_connection_errors 的最大值）中断的数据库连接而导致的阻塞。

进入 CentOS7 服务器：

方法一：提高允许的 max_connection_errors 数量（治标不治本）：

进入 Mysql 数据库查看 max_connection_errors：show variables like '%max_connection_errors%';
修改 max_connection_errors 的数量为 1000：set global max_connect_errors = 1000;
查看是否修改成功：show variables like '%max_connection_errors%';

方法二：使用 mysqladmin flush-hosts 命令清理一下 hosts 文件：

查找 mysqladmin 的路径：whereis mysqladmin
执行命令，如：/usr/local/mysql5.5.35/bin/mysqladmin -uroot -pyourpwd flush-hosts

注：方法二清理 hosts 文件，也可以直接进入 mysql 数据库执行命令：mysql> flush hosts;

解决了 hosts 的问题后，在 tomcat 的日志文件，发现在报错开始部分又出现了这个错误：

 com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 32 milliseconds ago.  The last packet sent successfully to the server was 32 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
    ......

表示程序与 MySQL 通讯失败了，即连接失败了。The last packet successfully received from the server was 32 milliseconds ago 表示 mysql 重连，连接丢失。此为数据库连接空闲回收问题，程序打开数据库连接后，等待做数据库操作时，发现连接被 MySQL 关闭掉了。

认为可能是连接等待超时问题。在 mysql 数据库的配置中，其连接的等待时间（wait_timeout）缺省值为 8 小时。在 mysql 中可以查看：

mysql﹥ 
mysql﹥ show global variables like 'wait_timeout'; 
+---------------+---------+ 
| Variable_name | Value | 
+---------------+---------+ 
| wait_timeout | 28800 | 
+---------------+---------+ 
1 row in set (0.00 sec)

28800 seconds，也就是 8 小时。如果在 wait_timeout 秒期间内，数据库连接 (java.sql.Connection) 一直处于等待状态，mysql 就将该连接关闭。这时，Java 应用的连接池仍然合法地持有该连接的引用。当用该连接来进行数据库操作时，就碰到上述错误。

MySQL 连接一次连接需求会经过 6 次「握手」方可成功，任何一次「握手」失败都可能导致连接失败。前三次握手可以简单理解为 TCP 建立连接所必须的三次握手，MySQL 无法控制，更多的受制于 tcp 协议的不同实现，后面三次握手过程超时与 connect_timeout 有关。

改变数据库参数是最简单的处理方式（修改 /etc/my.cnf 中的 wait_timeout 值），但是需要重启数据库，影响较大。在不修改数据库参数的前提下，可以做已下处理：

如果使用的是 jdbc，在 jdbc url 上添加 autoReconnect=true，如：dataSource.url=jdbc:mysql://132.231.xx.xxx:3306/shop?useUnicode=true&characterEncoding=utf8&useSSL=true&autoReconnect=true

如果是在 Spring 中使用 DBCP 连接池，在定义 datasource 增加属性 validationQuery 和 testOnBorrow，如：

<bean id="vrsRankDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${dataSource.driver}"/>
    <property name="url" value="${dataSource.url}"/>
    <property name="username" value="${dataSource.user}"/>
    <property name="password" value="${dataSource.password}"/>
    <property name="validationQuery" value="SELECT 1"/>
    <property name="testOnBorrow" value="true"/>
</bean>

如果是在 Spring 中使用 c3p0 连接池，则在定义 datasource 的时候，添加属性 testConnectionOnCheckin 和 testConnectionOnCheckout，如：

<bean name="cacheCloudDB" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="${dataSource.driver}"/>
    <property name="jdbcUrl" value="${dataSource.url}"/>
    <property name="user" value="${dataSource.user}"/>
    <property name="password" value="${dataSource.password}"/>
    <property name="initialPoolSize" value="10"/>
    <property name="maxPoolSize" value="10"/>
    <property name="testConnectionOnCheckin" value="false"/>
    <property name="testConnectionOnCheckout" value="true"/>
    <property name="preferredTestQuery" value="SELECT 1"/>
</bean>

尝试解决了一下上面的连接超时问题，但是发现并没有什么用，还是会出现上面的问题。于是便怀疑是不是远程连接 Mysql 太慢导致了连接超时？因为我在 CentOS7 服务端和 Windows 本地的 Navicat 连接 mysql 都没问题。在网上查询了下，发现在 mysql 的配置文件 /etc/my.cnf 中增加如下配置参数：

# 注意该配置是加在 [mysqld] 下面
[mysqld]
skip-name-resolve

然后需要重启 mysql 服务。因为根据说明，如果 mysql 主机查询和解析 DNS 会导致缓慢或是有很多客户端主机时会导致连接很慢。同时，请注意在增加该配置参数后，mysql 的授权表中的 host 字段就不能够使用域名而只能够使用 ip 地址了，因为这是禁止了域名解析的结果。

经过上面的配置后，重新测试程序，就又出现了这个错误。经过查询，发现这是由于 SSL 引起的错误。因为我是在 CentOS7 上使用 yum 命令新装的 MySQL5.7，默认是没有配置 MySQL SSL 的。

而我以前一直使用的 Ubuntu16.04 上的 mysql 数据库，它默认是配置了 MySQL SSL 的，所以我习惯在连接数据库的 jdbc 的 url 里面加上 &useSSL=true，即使用 SSL 加密。导致我在连接当前 mysql 的时候，一直连接不上。查看 tomcat 日志的报错末尾，会有如下报错：

Caused by: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
    at sun.security.ssl.Alerts.getSSLException(Alerts.java:192)
    at sun.security.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1946)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:316)
    at sun.security.ssl.Handshaker.fatalSE(Handshaker.java:310)
    at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1639)
    at sun.security.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:223)
    at sun.security.ssl.Handshaker.processLoop(Handshaker.java:1037)
    at sun.security.ssl.Handshaker.process_record(Handshaker.java:965)
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:1064)
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1367)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1395)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1379)
    at com.mysql.jdbc.ExportControlled.transformSocketToSSLSocket(ExportControlled.java:186)
    ... 24 more
Caused by: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
    at com.mysql.jdbc.ExportControlled$X509TrustManagerWrapper.checkServerTrusted(ExportControlled.java:302)
    at sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:1091)
    at sun.security.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1621)
    ... 32 more
Caused by: java.security.cert.CertPathValidatorException: Path does not chain with any of the trust anchors
    at sun.security.provider.certpath.PKIXCertPathValidator.validate(PKIXCertPathValidator.java:154)
    at sun.security.provider.certpath.PKIXCertPathValidator.engineValidate(PKIXCertPathValidator.java:80)
    at java.security.cert.CertPathValidator.validate(CertPathValidator.java:292)
    at com.mysql.jdbc.ExportControlled$X509TrustManagerWrapper.checkServerTrusted(ExportControlled.java:295)
    ... 34 more

遂恍然大悟。。。

在配置 JDBC URL 时使用 &useSSL=false，即不使用 SSL 加密，配置后连接正常：

dataSource.url = jdbc:mysql://132.231.xx.xxx:3306/shop?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true

如果安全性较高的数据建议在 MySQL 端还是把 SSL 配上。

由于 MySQL 的 SSL 问题引起了一连串的问题。由于 SSL 加密的问题，导致程序向 mysql 的连接超时，然后一直向 mysql 发送连接，导致了同一个 ip 在短时间内产生太多中断的数据库连接而导致的阻塞。

以后看报错日志，除了看报错的最开始部分，也要看看报错的结尾部分，弄不好会有一些其他的发现的。

欢迎您的点赞、收藏和评论！

记一次CentOS7MySQL排坑历程

一、报错及起因

二、排错历程

1. 检查 sql 语句

2. MySql Host is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’ 报错

原因分析：

解决方法：

3.com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure 异常

原因分析：

解决方法：

4. 远程连接 Mysql 太慢问题

5. 终极解决：Could not create connection to database server. Attempted reconnect 3 times. Giving up 报错

原因分析：

解决方法：

三、总结