MySQL Host ‘host_name’ is blocked – Which Database

MySQL

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)

Best Answer

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).

SUGGESTION #1

Your binary logs are being written to /vu/mysql/dados, your datadir.

If you have a folder on a separate disk, perhaps you should map your binary logs there. That will keep data and logging on separate disks.

[mysqld]
log-bin=/someothermountedfolder/mysql-bin

SUGGESTION #2

You are using MySQL 5.5 but I do not see any options to make InnoDB access multiple cores.

Please add the following to my.cnf to give InnoDB some Enhanced I/O Performance

[mysqld]
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=2000

Give it a Try !!!

MySQL – Connection blocked because of many connection errors – How to find root cause

The advice you've seen about max_connections is a distraction from the issue you're seeing, because it has nothing to do with connection errors.

You can log the failed connections to the mysql error log by issuing this:

SET GLOBAL log_warnings = 2;

You can also make this a permanent setting in my.cnf so that it persists after a restart.

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-warnings

Best Answer

Related Solutions

Mysql – Bin logs with high i/o

SUGGESTION #1

SUGGESTION #2

MySQL – Connection blocked because of many connection errors – How to find root cause

Related Question