当前位置： 移动技术网 > IT编程>数据库>Mysql > 记一次CentOS7-MySQL排坑历程

记一次CentOS7-MySQL排坑历程

2019年09月01日  | 移动技术网IT编程  | 我要评论

一、报错及起因

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

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

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数量（治标不治本）：

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

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

  1. 查找 mysqladmin 的路径：whereis mysqladmin
  2. 执行命令，如：/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>

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 上使用 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 在短时间内产生太多中断的数据库连接而导致的阻塞。

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

ps：如果觉得文章有什么地方写错了，哪里写得不好，或者有什么建议，欢迎指点。

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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网