处理java访问mysql连接数太多的错误
2018-08-20 本文已影响49人
Devops海洋的渔夫
原创内容，转载请注明出处
博主地址：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 = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
## set max_connections
max_connections=10000
## set tempdir
tmpdir=/data/mysql/mysqlData/mysql_tmp
## set innodb_buffer_pool_size
innodb_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 = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
## set max_connections
max_connections=10000
## set wait_timeout
wait_timeout=200
## set tempdir
tmpdir=/data/mysql/mysqlData/mysql_tmp
## set innodb_buffer_pool_size
innodb_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>
可以看出已经配置生效了。
如果你想要看我写的总体系列文章目录介绍，可以点击kuberntes以及运维开发文章目录介绍
