原创内容，转载请注明出处

博主地址：https://aronligithub.github.io/

前言

在生产环境处理故障的过程出现了java服务连接mysql，由于连接数太多被拒绝连接的故障，那么下面来看看怎么优化一下吧。

故障摘要

java.sql.SQLException: null,  message from server: "Host 'mysql_server_ip' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[mysql-connector-java-5.1.41.jar!/:5.1.41]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897) ~[mysql-connector-java-5.1.41.jar!/:5.1.41]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886) ~[mysql-connector-java-5.1.41.jar!/:5.1.41]
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1040) ~[mysql-connector-java-5.1.41.jar!/:5.1.41]
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2205) ~[mysql-connector-java-5.1.41.jar!/:5.1.41]
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2236) ~[mysql-connector-java-5.1.41.jar!/:5.1.41]
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2035) ~[mysql-connector-java-5.1.41.jar!/:5.1.41]
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:790) ~[mysql-connector-java-5.1.41.jar!/:5.1.41]
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) ~[mysql-connector-java-5.1.41.jar!/:5.1.41]

可以从服务端日志中看出，提示了故障信息：

java.sql.SQLException: null,  message from server: "Host 'mysql_server_ip' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"

那么该如何快速处理并优化一下呢？

• 首先为了快速恢复服务，先对mysql主从两台服务进行重启处理

service mysqld restart

• 然后观察服务端的错误日志，对mysql的连接相关参数进行优化

关于mysql连接数优化的参数

对于java连接mysql，是有mysql连接池的，如果提示该错误，可以往这两个方向对mysql优化一下：
1.配置mysql的最大连接数
2.配置centos7服务器的文件打开数
3.配置mysql连接最长等待超时时间

1.配置mysql的最大连接数

配置mysql最大连接数分为两种方式：
1.第一种修改my.cnf文件，需要mysql服务重启后生效
2.第二种直接修改global variables参数

那么该如何处理呢，如果mysqld服务正在运行？

• 首先我会修改global variables参数

• 然后修改my.cnf文件
  这样处理的好处就是立即可以修改参数，不需要重启mysqld服务。当mysqld服务重启的时候，参数也会自动生效。

使用修改global variables参数的方式，修改mysql最大连接数的相关命令

#查看最大连接数：
show variables like '%max_connections%';
#临时增加最大连接数：
mysql> set global max_connections=10000;

修改my.cnf文件(/etc/my.cnf)的方式，修改mysql的最大连接数

设置最大连接数：max_connections=10000

[root@ibmserver10 ~]# cat /etc/my.cnf# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid## set max_connections max_connections=10000## set tempdirtmpdir=/data/mysql/mysqlData/mysql_tmp## set innodb_buffer_pool_sizeinnodb_buffer_pool_size=2G

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@ibmserver10 ~]#

2.配置mysql连接最长等待超时时间（wait_timeout）

• 这个wait_timeout的作用是，设置非交互连接（就是指那些连接池方式、非客户端方式连接的）的超时时间，默认是28800，就是8小时，超过这个时间，mysql服务器会主动切断那些已经连接的，但是状态是sleep的连接。

• 修改wait_timeout可能出现的程序错误：
  当服务器端去连接mysql的时候，连接池里的连接已经被mysql主动断开，这时取回的连接就是null，再加上程序里对此没有做判断的话，接下来的对数据库的一系列的操作都会出现问题。

那么基本知道了好处以及坏处之后，我们下面来看看怎么配置。

查询mysql的连接等待时间

mysql> 
mysql> show global variables like "wait_timeout";
+---------------+-------+| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |+---------------+-------+1 row in set (0.01 sec)

mysql>

可以看到，现在的mysql等待连接时间是28800秒 = 8小时

那么这里就带来另一个问题了，这个等待时间设置为多久比较好呢？

wait_timeout过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可 能会遭遇到“MySQL has gone away”之类的问题。
这种情况只能靠实际情况配置了。

java服务通常连接mysql的业务操作不会超过几分钟，那么设置个几百秒即可。

set global wait_timeout = 200;

那么就设置200秒即可。

但是有一种情况比较特殊，例如用户在使用导出报表等数据处理功能，有些时间选择导出一个月。这个时候就会可能出现mysql连接丢失的情况。

如果碰到这种情况，最好再调整一下mysql的时间，选择一个适中的。

使用my.cnf文件设置wait_timeout参数

前面讲述的是使用global variables参数的方式修改wait_timeout的参数值。下面示例使用my.cnf文件的配置：
wait_timeout=200

[root@ibmserver10 ~]# cat /etc/my.cnf# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid## set max_connections max_connections=10000## set wait_timeoutwait_timeout=200## set tempdirtmpdir=/data/mysql/mysqlData/mysql_tmp## set innodb_buffer_pool_sizeinnodb_buffer_pool_size=2G

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@ibmserver10 ~]#

查看是否配置生效：

mysql> show global variables like "wait_timeout";
+---------------+-------+| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 200   |+---------------+-------+1 row in set (0.01 sec)

mysql>

可以看出已经配置生效了。

作者：DevOps海洋的渔夫
链接：https://www.jianshu.com/p/be4d3141dfce