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
摘抄文档
- [x] 博客园.MySQL参数max_connect_errors分析释疑
- [ ] Imooc.MySQL性能参数详解之Max_connect_errors 使用介绍
- [x] 简书.max_connect_errors
作者：linux快速入门培训
链接：https://www.imooc.com/article/44354
来源：慕课网