MySQL连接报错-->Host 'X.X.X.X' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

MySQL连接报错-->Host 'X.X.X.X' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

错误信息

fail: Microsoft.EntityFrameworkCore.Database.Connection[20004]
      An error occurred using the connection to database 'TestDb' on server 'X.X.X.X'.
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'Test.API.Infrastructure.TestContext'.
      MySql.Data.MySqlClient.MySqlException (0x80004005): Host 'X.X.X.X' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
         at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
         at MySql.Data.MySqlClient.NativeDriver.Open()

Host 'X.X.X.X' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

该错误的其它扩展信息

错误说明
意味着某一客户端尝试连接此MySQL服务器,
但是失败次数大于配置允许失败的次数,
则MySQL会无条件强制阻止此客户端连接

功能与作用
一般来说建议数据库服务器不监听来自网络的连接,
仅仅通过sock连接,这样可以防止绝大多数针对mysql的攻击;
如果必须要开启mysql的网络连接,则最好设置此值,以防止穷举密码的攻击手段.

如何进入MySQL

关键命令-->mysql -u root -p

[root@iZ2ze77grk2a8yrd97ft1uZ ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

//经验+1
我刚开始直接使用的是Linux服务器的用户名.{root}和密码.
其实应该使用MySQL数据库的用户名.{MySQL_Uid}和MySQL数据库的密码.

成功进入的一个提示↓

[root@iZ2ze77grk2a8yrd97ft1uZ ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27624
Server version: 5.7.33 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>

解决方案-->1(治标不治本)(重启服务参数会再次重置为默认值)

连接数量-->查看

mysql> show global 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=150;
Query OK, 0 rows affected (0.00 sec)

由于我在测试环境中,所以我采用的是该方法

解决方案-->2(重启服务参数会再次重置为默认值)

修改配置文件,这样每次服务重启,不用每次使用命令再次执行修改参数了

vim /etc/my.cnf

在节点.{mysqld}下修改参数

[mysqld]
max_connect_errors = 1000

解决方案-->3(重置最大错误连接数)

#MySQL版本5.6.5及以前默认值为 10
#MySQL版本5.6.6及以后默认值为 100
flush hosts;   

#执行完flush hosts查询
select *from performance_schema.host_cache limit 1 \G;
*************************** 1. row ***************************
                                        IP: X.X.X.X
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 0
                 COUNT_HOST_BLOCKED_ERRORS: 0
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 1
                       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: 0
                   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

其它

#为了避免数据太多,我们只需要查看第一条数据就行了
select *from performance_schema.host_cache limit 1 \G;


#或者这样写↓
use performance_schema;
select * from host_cache limit 1 \G;



#输出...
*************************** 1. row ***************************
                                        IP: X.X.X.X
                                      HOST: NULL
                            HOST_VALIDATED: YES
                        SUM_CONNECT_ERRORS: 193
                 COUNT_HOST_BLOCKED_ERRORS: 2973
           COUNT_NAMEINFO_TRANSIENT_ERRORS: 0
           COUNT_NAMEINFO_PERMANENT_ERRORS: 1
                       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: 195
                                                           

摘抄文档

• 博客园.MySQL参数max_connect_errors分析释疑
• Imooc.MySQL性能参数详解之Max_connect_errors 使用介绍
• 简书.max_connect_errors

作者：linux快速入门培训
链接：https://www.imooc.com/article/44354
来源：慕课网