How to Run mysqladmin flush-hosts on Amazon RDS
In some cases while running MySQL on Amazon RDS (or elsewhere), it may be necessary to
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 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
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:
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
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.