Fix Host ‘IP’ is blocked because of many connection errors on MySQL/MariaDB
I am trying to login to my mysql/mysqld server using the following Unix command: $ mysql -u foo -h 172.16.5.100 -p dbnmame But greeted with the following error on screen: ERROR 1129 (HY000): Host ‘172.16.5.100’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’ How do I fix this problem on a Linux or Unix-like system?
The number of connections permitted is controlled by the MySQL/MariaDB max_connections system variable. The default value is 151 to improve performance when MySQL is used with the Web server. You might run into a problem if you are running a high trafficked web site or MariaDB server in clustered mode or using a Galera master to master DB cluster. If you need to support more connections, you should set a more substantial value for this variable.
How to find out current value of max_connections
Type the following mysql command: $ mysql -u root -p Now issue the following sql command: mysql> show variables like "max_connections"; Sample outputs:
| Variable_name | Value |
| max_connections | 151 |
1 row in set (0.00 sec)
How do I increase max_connections value?
Edit my.cnf or mariadb.conf.d/50-server.cnf in /etc/ directory using a text editor such as vi command or nano command: $ sudo vim /etc/mariadb.conf.d/50-server.cnf Add/append/edit the following line under [mysqld] section (say set value to 1000. Max limit is 100000): max_connections = 1000 Save and close the file. Next, restart the mysqld service, run: $ sudo systemctl restart mysql If you are using a CentOS/RHEL/Fedora/Oracle/Scientific Linux, run: $ sudo systemctl restart mysqld If you are using a FreeBSD unix, run the following to restart the system: $ sudo /usr/local/etc/rc.d/mysql-server restart Verify new limits with the following command: $ mysql -u root -p -e 'show variables like "max_connections";'
A note about mysqladmin command
To flush all cached hosts and remove this limit run the following bash command $ mysqladmin flush-hosts OR pass username and prompt for a password: $ mysqladmin -u root -p flush-hosts
A note about “Too many open files” error
You need to changes the number of file descriptors available to mysqld if you get the error. To see current FD limits, run: $ mysql -u root -p -e 'show variables like "open_files_limit";' Sample outputs:
| Variable_name | Value |
| open_files_limit | 500005 |
Again edit my.cnf if limit is too small and getting an error in your log file: $ sudo vi my.cnf OR $ sudo vim /etc/mariadb.conf.d/50-server.cnf Set the value as per requirements in in [mysqld] section open_files_limit = 1024000 Save and close the file. Make sure you restart the mysqld as described above. If you are using GNU/Linux systemd based distro such as RHEL/CentOS 7, create a file named: $ sudo vi /etc/systemd/system/mysqld.service Append the following: [Service] User=mysql Group=mysql LimitNOFILE=1024000 Restart needed services: $ sudo systemctl daemon-reload $ sudo systemctl restart mysqld Older Linux distro must edit the /etc/security/limits.conf file.
The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.