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

MySQL

I am developing a web application and am running into the dreaded host xxx blocked because of many connection errors message. There have been numerous questions on SO pertaining to this error and common wisdom is to flush hosts to clear the error and then bump up MySQL's max_connections setting in order to avoid this issue in the future. A summary of the issue from the MySQL manual is as follows:

The number of interrupted connect requests allowed is determined by
the value of the max_connect_errors system variable. After
max_connect_errors failed requests, mysqld assumes that something is
wrong (for example, that someone is trying to break in), and blocks
the host from further connections until you execute a mysqladmin
flush-hosts command or issue a FLUSH HOSTS statement. See Section
5.1.3, “Server System Variables”.

I'd like to go a step further and determine the root cause of the issue for my application. I am relatively new to MySQL and would like to know what is the best way to trap such errors – does MySQL provide any logs or information as to which connections or queries failed or are the any tracing tools I can use to ascertain this information?

Thank you

JP

Best Answer

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

Related Question