我们某个应用系统抛了异常,看提示像是因为连接过多导致的IP访问受限,
Caused by:
java.sql.SQLException: null,
message from server: "Host 'x.x.x.x' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
网上搜了下,问题原因就是同一个IP在短时间内产生太多(超过MySQL数据库max_connection_errors的最大值)中断的数据库连接而导致的阻塞,按照他所说的,max_connect_errors是一个MySQL中与安全有关的计数器值,他负责阻止过多尝试失败的客户端以防止暴力破解密码的情况,max_connect_errors的值与性能并无太大关系。这个设计倒是和Oracle中的密码延迟验证功能有些相似,在Oracle中,随着密码输入错误次数,延迟验证时间会逐步增加(可参考《登录缓慢的诡异问题》),同样都是为了防止账号密码被暴力破解。但是Oracle的这个机制可能回导致其他用户受到影响,或者出现严重的library cache lock等问题,而MySQL的机制很彻底,就是让这个IP不能登录,对其他人没影响,不会导致其他的性能问题。
如果某一客户端尝试连接此MySQL服务器,但是失败(如密码错误等等)的次数超过了max_connect_errors参数定义的值,则MySQL会无条件强制阻止此客户端连接。在未到达之前,当这一客户端成功连接一次MySQL服务器后,针对此客户端的max_connect_errors会清零。
达到max_connect_errors值时的解决方案,可以归为几类,
1. 如提示所说,执行mysqladmin flush-hosts解锁,
mysqladmin flush-hosts (远程执行,-h x.x.x.x) -uroot -p
2. 登录数据库,执行flush hosts,
mysql> flush hosts;
Query OK, 0 rows affected (0.00 sec)
3. 粗暴一些,非生产环境,重启MySQL服务。
但是以上三种,治标不治本,如果频繁出现连接错误的情况,还是会锁,此时就可以根据实际需求,增加max_connect_errors参数值。
临时修改方案:
1. 登录数据库,可以看到max_connect_errors当前值100
mysql> show variables like '%max_connect_errors%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
+--------------------+-------+
1 row in set (0.01 sec)
2. 修改该值为1000,
mysql> set global max_connect_errors = 1000;
Query OK, 0 rows affected (0.00 sec)
3. 确认修改完成,
mysql> show variables like '%max_connect_errors%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
+--------------------+-------+
1 row in set (0.00 sec)
终极修改方案,
使用以上操作,当MySQL重启时,修改就会失效,此时就需要改下配置文件my.cnf,增加这行,然后重启服务,
max_connect_errors=1000
改参数就会一直生效了,
mysql> show variables like '%max_connect_errors%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
+--------------------+-------+
1 row in set (0.00 sec)
从这个参数问题,能看出像MySQL这种数据库在设计上其实考虑得还是很充分的,异常访问的控制、恢复机制、熔断机制,这些都值得我借鉴和思考。
做个预告,本周开始的数据技术嘉年华上,我会在周六下午,分会场12,献丑做个分享,主题是《溯本求源,数据库应用设计优化浅谈》,一个老案例,聊聊演进过程,以及教训经验,和我们些许实践,欢迎各位捧场,拍砖就单聊了