Mysql max-connect-errors参数-CSDN博客

本文链接：https://blog.csdn.net/hbhe0316/article/details/122701138

本文介绍了MySQL的max_connect_errors变量，当来自特定主机的连接错误达到该值后，服务器会阻止该主机的进一步连接。可以通过`SHOW VARIABLES LIKE 'max_connect_errors';`查看当前设置，并通过`set global max_connect_errors=新值;`进行全局设置，或在my.cnf配置文件中修改。重启服务后，新的设置生效。

摘要由CSDN通过智能技术生成

官网地址:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_myisam_recover_optionshttps://www.cndba.cn/hbhe0316/article/22618https://www.cndba.cn/hbhe0316/article/22618

https://www.cndba.cn/hbhe0316/article/22618

在max_connect_errors之后，来自主机的连续连接请求被中断而没有成功连接，服务器将阻止该主机进一步连接。如果在前一个连接中断后，主机的连接在小于max_connect_errors尝试次数的情况下成功建立，那么该主机的错误计数将被清除为零。要清除阻塞的主机，请刷新主机缓存;请参见刷新主机缓存。https://www.cndba.cn/hbhe0316/article/22618https://www.cndba.cn/hbhe0316/article/22618

1.查看默认值

https://www.cndba.cn/hbhe0316/article/22618

mysql> SHOW VARIABLES LIKE 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
+--------------------+-------+
1 row in set (0.00 sec)

2.设置globalmax_connect_errorshttps://www.cndba.cn/hbhe0316/article/22618 https://www.cndba.cn/hbhe0316/article/22618 https://www.cndba.cn/hbhe0316/article/22618

mysql> set global max_connect_errors=200;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@mysql57 ~]# /mysql/bin/mysql -uroot -pwwwwww -S /mysql/mysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 3
Server version: 5.7.36-log 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> SHOW VARIABLES LIKE 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 200   |
+--------------------+-------+
1 row in set (0.01 sec)

3.设置/etc/my.cnf文件https://www.cndba.cn/hbhe0316/article/22618

[root@mysql57 ~]# cat /etc/my.cnf | grep -i max-connect-errors
max-connect-errors = 6000
[root@mysql57 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.. SUCCESS! 
mysql> SHOW VARIABLES LIKE 'max_connect_errors';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 6000  |
+--------------------+-------+
1 row in set (0.00 sec)

MYSQL