问题
ERROR 1129 (HY000): Host '192.168.10.10' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
问题验证
我们尝试连接，用命令行也连接失败。
[clog@MC-FSB ~]$mysql -uroot -h192.168.10.18 -p
Enter password:
ERROR 1129 (HY000): Host '192.168.10.10' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
查看日志
查看mysql数据库的日志
tail -n 200 /var/log/mysqld.log
解决办法
进入数据库进行刷新错误连接，并且查看最大数量为100，并改为1000
修改max_connection_errors的数量为1000（立即生效）
set global max_connect_errors = 1000;在my.cnf中[mysqld]下添加（重启后不失效）
max_connect_errors = 1000[admin@wmsweb log]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1853635
Server version: 5.7.25 MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
mysql> flush hosts;
Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%max_connect_errors%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
+--------------------+-------+
1 row in set (0.01 sec)mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)mysql> show global variables like '%max_connect_errors%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 1000 |
+--------------------+-------+
1 row in set (0.00 sec)mysql> 再次通过客户端连接成功
[clog@MC-FSB ~]$mysql -uroot -h192.168.10.18 -p
Enter password:
ERROR 1129 (HY000): Host '192.168.10.10' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
[clog@MC-FSB ~]$mysql -uroot -h192.168.10.18 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1853636
Server version: 5.7.25 MySQL Community Server (GPL)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
根本解决办法
解决ERROR 1129 (HY000)的方法是执行flush host或者 mysqladmin flush-hosts，其目的是为了清空host cache里的信息，那是不是说不使用host cache就可以了？
host_cache_size的作用
缺点：当有一个新的客户端连接进来时，MySQL Server都要建立一个新的记录，如果DNS解析很慢，无疑会影响性能。如果被允许访问的主机很多，也会影响性能，这个与host_cache_size有关，这个参数是5.6.5引入的。5.6.8之前默认是128,5.6.8之后默认是-1,基于max_connections的值动态调整。所以如果被允许访问的主机很多，基于LRU算法，先前建立的连接可能会被挤掉，这些主机重新进来时，会再次进行DNS查询。
优点：通常情况下，主机名是不变的，而IP是多变的。如果一个客户端的IP经常变化，那基于IP的授权将是一个繁琐的过程。因为你很难确定IP什么时候变化。而基于主机名，只需一次授权。而且，基于host cache中的失败信息，可在一定程度上阻止外界的暴力破解攻击。
关于阻止外界的暴力破解攻击，涉及到max_connect_errors参数，默认为100，官方的解释如下：
If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.
如果某个客户端的连接达到了max_connect_errors的限制，将被禁止访问，并提示以下错误：
Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
查看当前的最大链接错误数.
mysql> show variables like 'max_connect_errors';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
+--------------------+-------+
1 row in set (0.07 sec)
使host cache不生效(禁用 MySQL DNS 查找)的方式有如下两种：
A、设置 host_cache_size 为0
mysql> set global host_cache_size=0;
B、配置skip-name-resolve
编辑mysql配置文件 my.cnf
vi /etc/my.cnf
在 [mysqld] 下面添加 下面这一行
skip-name-resolve
⚠️注意事项
但是需要注意⚠️的是，如果这样设置，你将没办法使用127.0.0.1进行连接数据库。
如果禁用了DNS 则 localhost 则不会解析成回环地址.登录报错.
[root@dbserver ~]# mysql -h127.0.0.1 -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
如果该参数设置为OFF，则上述方式就会报错，通过报错信息可以看出，它直接将127.0.0.1转化为localhost了。
[root@localhost ~]# mysql -uroot -h127.0.0.1 -p123456 -P3306
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
