乐趣区

CentOS7-MySQL排坑历程

PS:如果觉得文章有什么地方写错了,哪里写得不好,或者有什么建议,欢迎指点。
一、报错及起因
今天在 CentOS7 安装了 mysql5.7,然后为了测试数据库环境是否配置成功,便写了个基于 mybatis+Spring 的 java web 程序连接操作 mysql 数据库,于是就一些发生了令人感到很烦的报错和故事:
当涉及到关于数据库的操作如查询、插入等操作时,首先浏览器访问会很慢,进度条一直旋转,然后页面会报 500 错误:org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exception。然而我在 CentOS7 服务端和 Windows 本地的 Navicat 连接 mysql 都没问题。。。
二、排错历程
1. 检查 sql 语句
看着这似乎是 mybatis 引起的错误,所有先检查 mapper 的 xml 中 sql 语句是否有错误,例如参数的格式转化、resultType 为 JavaBean、resultMap 需要定义、JavaBean 和 dao 的引入等。
检查中并没有发现什么问题,而且错误仍然存在。
2. MySql Host is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’ 报错
原因分析:
查看 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 数据库执行命令:flush hosts;
3.com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure 异常
解决了 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>

4. 远程连接 Mysql 太慢问题
尝试解决了一下上面的连接超时问题,但是发现并没有什么用,还是会出现上面的问题。于是便怀疑是不是远程连接 Mysql 太慢导致了连接超时?因为我在 CentOS7 服务端和 Windows 本地的 Navicat 连接 mysql 都没问题。在网上查询了下,发现在 mysql 的配置文件 /etc/my.cnf 中增加如下配置参数:
# 注意该配置是加在 [mysqld] 下面
[mysqld]
skip-name-resolve
然后需要重启 mysql 服务。因为根据说明,如果 mysql 主机查询和解析 DNS 会导致缓慢或是有很多客户端主机时会导致连接很慢。同时,请注意在增加该配置参数后,mysql 的授权表中的 host 字段就不能够使用域名而只能够使用 ip 地址了,因为这是禁止了域名解析的结果。
5. 终极解决:Could not create connection to database server. Attempted reconnect 3 times. Giving up 报错
原因分析:
经过上面的配置后,重新测试程序,就又出现了这个错误。经过查询,发现这是由于 SSL 引起的错误。因为我是在 CentOS7 上新装的 MySQL5.7,默认是没有配置 MySQL SSL 的。
而我以前一直使用的 Ubuntu16.04 上的 mysql 数据库,它默认是配置了 MySQL SSL 的,所以我习惯在连接数据库的 jdbc 的 url 里面加上 &useSSL=false,即使用 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 在短时间内产生太多中断的数据库连接而导致的阻塞。
以后看报错日志,除了看报错的最开始部分,也要看看报错的结尾部分,弄不好会有一些其他的发现的。

欢迎您的点赞、收藏和评论!(完)

退出移动版