max_connect_error的说明

最新推荐文章于 2023-08-09 08:42:07 发布

weixin_33859665

最新推荐文章于 2023-08-09 08:42:07 发布

阅读量423

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/Kenyon/blog/305297

版权

为什么80%的码农都做不了架构师？>>>

一台mysql的服务器做迁移，迁移完检查的时候发现报数据库错误，从日志里看到的如下：

ERROR 1129 (HY000): Host '10.1.11.72' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
ERROR 1129 (HY000): Host '10.1.11.72' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
ERROR 1129 (HY000): Host '10.1.11.72' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
ERROR 1129 (HY000): Host '10.1.11.72' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

现象是只有当前机器连数据库服务器会发生这种问题，其他机器连接都是OK的(用户密码和连接的库不一样，服务器IP是一样的) 这个错误是说当前机器连接失败超过服务器限制的max_connect_error的数量，然后在缓存里直接限制这台机子的暴力访问，在一定程度上可以缓解一些对数据库的莫名攻击。

重现：
当mysql的max_connect_error的连接数是默认的10个时，这个问题很容易重现。比如访问远程数据库的用户不对时. 远程71的用户名是u_ken，这里故意写成u_kenf。如果用户名对，密码不对，则不会出现这个问题，还是存在暴力破解用户密码的情况的。

[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1045 (28000): Access denied for user 'u_kenf'@'10.1.11.72' (using password: YES)
[root@test log]# 
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1129 (HY000): Host '10.1.11.72' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1129 (HY000): Host '10.1.11.72' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
[root@test log]#  mysql -h10.1.11.71 -uu_kenf -p123456 kenyon
ERROR 1129 (HY000): Host '10.1.11.72' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

解决：
在远程数据库服务器上把缓存清掉

[root@db1 mysql]# mysqladmin flush-hosts
[root@db1 mysql]# 
--或者
mysql> flush hosts;
Query OK, 0 rows affected (0.00 sec)
--或者治标不治本的调大max_connect_error参数值

转载于:https://my.oschina.net/Kenyon/blog/305297