前言

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


处理java访问mysql连接数太多的错误_java


故障摘要

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>

处理java访问mysql连接数太多的错误_sql_02