ERROR 1129 (HY000): Host 172.16.5.100 is blocked because of many connection errors

时间:2019-11-20 08:52:02  来源:igfitidea点击:

连接mysql服务器时,报错:

ERROR 1129 (HY000): Host 172.16.5.100 is blocked because of many connection errors; unblock with mysqladmin flush-hosts

原因

MySQL/MariaDB允许的连接数由max_connections参数控制,如果连接数超出了这个值,则会报错。

查看max_connections的当前值

执行以下mysql命令:

$ mysql -u root -p

执行sql语句:

mysql> show variables like "max_connections";

输出示例:

+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| max_connections | 151    |
+-----------------+--------+
1 row in set (0.00 sec)

如何增加max_connections值?

修改mysql配置文件 /etc/my.cnf
或者mariadb的配置文件 /etc/mariadb.conf.d/50-server.cnf:

$ sudo vim /etc/mariadb.conf.d/50-server.cnf

[mysqld]中添加下面内容:

max_connections = 1000

重启mysqld服务,使设置生效:

$ sudo systemctl restart mysqld

如果是在CentOS/RHEL/Fedora/Oracle/Scientific Linux中,重启mysql服务:

$ sudo systemctl restart mysqld

在FreeBSD Unix,使用下面命令进行重启:

$ sudo /usr/local/etc/rc.d/mysql-server restart

查看新的配置值:

$ mysql -u root -p -e 'show variables like "max_connections";'

关于太多打开文件的错误说明

如果报错:Too many open files。

那么类似的,需要修改open_files_limit的值。

查看当前允许打开的文件数:

$ mysql -u root -p -e 'show variables like "open_files_limit";'

输出示例:

Enter password: 
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| open_files_limit | 500005 |
+------------------+--------+