合 MySQL报错Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'和could not be resolved: Name or service not known
👉 本文共约1514个字，系统预计阅读时间或需6分钟。
目录
现象
'10.64.30.4' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
原因
同一个ip在短时间内产生太多（超过mysql数据库max_connect_errors的最大值）中断的数据库连接而导致的阻塞；
max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。
max_connect_errors的值与性能并无太大关系，默认是10。意味着如果某一客户端尝试连接此MySQL服务器，但是失败（如密码错误等等）10次 ，则MySQL会无条件强制阻止此客户端连接。
如果希望重置此计数器的值，则必须重启MySQL服务器或者执行mysql> flush hosts; 命令。当这一客户端成功连接一次MySQL服务器后，针对此客户端的max_connect_errors会清零。
如果max_connect_errors设置过小，则网页可能提示无法连接数据库服务器。
分析
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | mysql> show variables like '%log_error%'; +----------------------------+----------------------------------------+ | Variable_name | Value | +----------------------------+----------------------------------------+ | binlog_error_action | ABORT_SERVER | | log_error | /var/log/mysqld.log | | log_error_services | log_filter_internal; log_sink_internal | | log_error_suppression_list | | | log_error_verbosity | 2 | +----------------------------+----------------------------------------+ 5 rows in set (0.13 sec) mysql> [root@db-09 mysql]# more /var/log/mysqld.log | grep "10.64.30.4" 2023-04-06T05:52:27.039721Z 7005 [Warning] [MY-010055] [Server] IP address '10.64.30.4' could not be resolved: Name or service not known 2024-02-04T05:10:47.539800Z 14 [Warning] [MY-010055] [Server] IP address '10.64.30.4' could not be resolved: Name or service not known 2024-02-04T05:14:57.547741Z 29 [Warning] [MY-010055] [Server] IP address '10.64.30.4' could not be resolved: Name or service not known 2024-02-04T05:15:47.549721Z 43 [Warning] [MY-010055] [Server] IP address '10.64.30.4' could not be resolved: Name or service not known 2024-02-07T05:02:30.234721Z 9650 [Warning] [MY-010055] [Server] IP address '10.64.30.4' could not be resolved: Name or service not known 2024-02-08T02:50:00.884651Z 13 [Warning] [MY-010055] [Server] IP address '10.64.30.4' could not be resolved: Name or service not known 2024-02-08T03:01:40.894519Z 14 [Warning] [MY-010055] [Server] IP address '10.64.30.4' could not be resolved: Name or service not known |
因为mysql默认会反向解析DNS，对于访问者Mysql不会判断是hosts还是ip都会进行dns反向解析，频繁地查询数据库和权限检查，这大大增加了数据库的压力，导致数据库连接缓慢，严重的时候甚至死机，出现“连接数据库时出错”等字样。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
