In some cases while running MySQL on Amazon RDS (or elsewhere), it may be necessary to FLUSH all host records from MySQL. Typically, this is required when the error "Host 'host_name' is blocked" occurs, which will prevent additional connections from that particular host_name.
The host_name blocked error occurs when the number of max_connect_errors specified in MySQL’s configuration is exceeded, meaning a particular host attempted to connect too many times unsuccessfully. This is a security measure in place by MySQL to prevent unwarranted attacks from hosts/users that don’t have proper credentials, but sometimes it can occur by mistake and may need to be resolved by flushing the hosts.
What Does Flushing Hosts Do?
With a MySQL user with proper privileges executes a FLUSH statement, MySQL can clear flush tables, locks, and internal cache systems depending on the passed in options. In the case of FLUSH HOSTS;, MySQL will empty the host cache, which effectively means MySQL’s record of which hosts are currently or have recently connected is reset, allowing for further connections from said hosts.
Executing FLUSH HOST Directly
While it may be that you are completely prevented from connecting to MySQL, in some cases it is possible that the system will “save” a connection slot which is only available to the primary or root account.
For Amazon RDS, this user name is typically found in your management console as the default or “Master”. Attempt to connect to your MySQL server with this primary user name.
If you are able to connect, flushing your hosts is as simple as running the FLUSH HOSTS; MySQL statement:
FLUSH HOSTS;
You should now have cleared your host_name blocked error and be able to connect with your standard MySQL account.
Flushing Hosts Remotely Using MySQLAdmin
In the event that you are unable to connect to MySQL as the master account and are still receiving blocked errors, the next option is to connect to the server remotely and execute the flush command using the mysqladmin tool.
To do this, you’ll need to be connected to a different EC2 instance or server that has access to the RDS server producing the error.
Once connected, execute the following command with the proper options substituted between <>:
$ mysqladmin -h <RDS_ENDPOINT_URL> -P <PORT> -u <USER> -p flush-hosts
If successful, the flush-hosts command will be executed as expected and you’ll now be able to connect to MySQL as normal.
Rebooting the RDS Instance
The final option, if all else fails, is to simply login to the RDS management control panel and manually restart the RDS instance that is giving the error. This will effectively reset the hosts cache for you, though it may not be ideal in production circumstances.