How to Unblock With Mysqladmin Flush Hosts in MySQL

1. Reasons for Blocking a Host
2. How to Deal With Blocked Hosts
3. Effects of Flushing the host_cache Table
4. Different Ways to Flush Hosts
5. Conclusion

In this tutorial, we will learn about the reasons for blocking a host. We will also see how to unblock with mysqladmin flush-hosts; in MySQL.

Then, we’ll find the different methods to flush the host_cache for a successful connection. After that, we will also look at the effects of flushing the host_cache table.

Reasons for Blocking a Host

Any of the following reasons can get your host (host_name) blocked.

• A host, host_name is blocked when any of your hosts have changed the IP address.
• Host, host_name is blocked due to many connection errors. It means mysqld got many interrupted connection requests.

How to Deal With Blocked Hosts

The host_cache has the client’s HOST, IP, SUM_CONNECT_ERRORS, and other necessary details.

Host cache is used to track the connection errors. The MySQL server uses host_cache to track the errors during the client connection process.

Suppose you got the following error while making a connection. Then it means, mysqld has received many connection requests interrupted in the middle.

Now, it is not letting you connect again.

Host 'host_name' is blocked because of many connection errors.
Unblock with mysqladmin flush-hosts

The system variable max_connect_errors determines the number of interrupted connect requests.

If the number of interrupted connect requests exceeds the allowed ones, your host is blocked. And can’t send the further connect request until you flush the hosts_cache table.

It is because mysqld assumes that someone is trying to break in or something is going wrong. In this situation, you can not send the connect request until you clear the host-cache table.

You can use mysqladmin flush-hosts; from a command prompt/terminal. You can also use the FLUSH HOSTS; statement in phpMyAdmin.

Effects of Flushing the host_cache Table

You may have one or more effects if you unblock the host via flushing host_cache. Those effects are listed below.

• In-memory host_cache would be cleared.
• Unblocks the host that was previously blocked.
• All rows from host_cache would be removed.

Different Ways to Flush Hosts

These are the different methods that you can use to flush hosts. You have to provide the required privileges for this.

• By providing SUPER privilege, you can change the value of the host_cache_size system variable.
• By giving DROP privilege, truncate the host_cache table. You can do it by using the TRUNCATE TABLE statement.
• Use FLUSH HOSTS; if you are using phpMyAdmin. It needs RELOAD privilege.
• By assigning RELOAD privilege, you can unblock with mysqladmin flush-hosts in MySQL. Execute mysqladmin flush-hosts; statement from command prompt/terminal.
• You can also use mysqladmin -u username -p password flush-hosts; to clear the host_cache table. Replace the username and password with your username and password.

Conclusion

Considering the above sections, we have concluded that the host is blocked if your host has changed the IP. Or you have more connection errors as compared to the allowed number of failed connection requests.

You can unblock the host via flushing your host_cache by using mysqladmin flush-hosts in MySQL and FLUSH HOSTS in phpMyAdmin.