MySQL数据库被锁定

问题

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)