MySQL运维实战（2.1） 登录失败次数太多导致主机被block的问题处理
参数max_connect_errors
MySQL有参数max_connect_errors，当一个主机尝试登录MySQL，失败的次数超过了max_connect_errors，则这个主机将无法登录到mysql。
下面是一个简单的测试：
### max_connect_errors 设置成3 mysql> show variables like '%max_connect_errors%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 100 | +--------------------+-------+ 1 row in set (0.00 sec) mysql> set global max_connect_errors=3; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%max_connect_errors%'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 3 | +--------------------+-------+ ### 模拟登陆失败（访问3306端口） [root@box1 ~]# telnet 172.16.20.51 3306 Trying 172.16.20.51... Connected to 172.16.20.51. Escape character is '^]'. J 5.7.32Qm>IHUJd>++[103(Nmysql_native_password !#08S01Got packets out of orderConnection closed by foreign host. ### 重试3次后，无法登陆数据库 [root@box1 ~]# telnet 172.16.20.51 3306 Trying 172.16.20.51... Connected to 172.16.20.51. Escape character is '^]'. jHost '172.16.20.51' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host. #### 提供正确的密码也无法登录MySQL [root@box1 ~]# mysql -uauser -h172.16.20.51 -pauser mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1129 (HY000): Host '172.16.20.51' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' ### 执行mysqladmin flush-hosts后，可以登陆 [root@box1 ecdsa]# mysqladmin -uroot -h127.0.0.1 -phello flush-hosts
客户端连接错误信息记录在information_schema.host_cache表，mysql服务端会从这个表读取数据，获取主机登陆信息。mysqladmin flush-hosts的作用是清空这个表。
mysql> select * from performance_schema.host_cache\G *************************** 1. row *************************** IP: 172.16.20.51 HOST: box1 HOST_VALIDATED: YES SUM_CONNECT_ERRORS: 3 COUNT_HOST_BLOCKED_ERRORS: 2 COUNT_NAMEINFO_TRANSIENT_ERRORS: 0 COUNT_NAMEINFO_PERMANENT_ERRORS: 0 COUNT_FORMAT_ERRORS: 0 COUNT_ADDRINFO_TRANSIENT_ERRORS: 0 COUNT_ADDRINFO_PERMANENT_ERRORS: 0 COUNT_FCRDNS_ERRORS: 0 COUNT_HOST_ACL_ERRORS: 0 COUNT_NO_AUTH_PLUGIN_ERRORS: 0 COUNT_AUTH_PLUGIN_ERRORS: 0 COUNT_HANDSHAKE_ERRORS: 5 COUNT_PROXY_USER_ERRORS: 0 COUNT_PROXY_USER_ACL_ERRORS: 0 COUNT_AUTHENTICATION_ERRORS: 0 COUNT_SSL_ERRORS: 0 COUNT_MAX_USER_CONNECTIONS_ERRORS: 0 COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0 COUNT_DEFAULT_DATABASE_ERRORS: 0 COUNT_INIT_CONNECT_ERRORS: 0 COUNT_LOCAL_ERRORS: 0 COUNT_UNKNOWN_ERRORS: 0 FIRST_SEEN: 2021-04-06 09:17:05 LAST_SEEN: 2021-04-06 09:18:40 FIRST_ERROR_SEEN: 2021-04-06 09:17:16 LAST_ERROR_SEEN: 2021-04-06 09:18:40 1 row in set (0.00 sec)
总结
当一个主机连续登录mysql失败的次数超过max_connect_errors后，将无法登录数据库。
错误数按主机维度，记录在performance_schema.host_cache表。
可以通过mysqladmin flush-hosts重置错误次数。
如果某个主机在登录失败次数到达max_connect_errors前，有一次成功的登录，则会将该主机的错误计数清0。
运维上，我们可以适当调大max_connect_errors的值。
