MySQL Host 'host_name' is blocked - Which Database?

Question

We are getting the error that a host is blocked on MySQL

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

I understand how to clear this error (Flush Hosts),

However we are a shared hosting company and would like to know to which database the connections are failing in order to rectify the issue and notify the customer. We have many databases and many customer websites.

We are running MySQL 5.5 - so the 'host cache' table does not exist (not sure if that would help anyway)

Answer 1

I am not sure that you can get the database, but you can identify the user with one of the following methods:

• The general log
• The error log (log_warnings = 2)
• An extra patch called user statistics (included by default in Percona and MariaDB)
• An audit plugin like McAfee's

The first three are mentioned on this article on MySQL performance blog.

Sadly, visilibity on the host cache was only added until 5.6 on performance_schema.host_cache.

As a temporary workaround, you would be required to set max_connect_errors to a large value instead of 10, and set MAX_CONNECTIONS_PER_HOUR and MAX_USER_CONNECTIONS per user to an appropriate value (you should be using different users if you are not, and/or different DNS names).