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

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

If you're using MySQL >= 5.6, then you can potentially use the information I did to resolve my situation without increasing max_connect_errors.

Run select * from performance_schema.host_cache; chances are, you'll have records for the host that is experiencing problems. you can use this page to figure out what each column means. In my case, I had EC2 instances communicating across a VPC Peering connection and my databases were failing the hosts with COUNT_NAMEINFO_PERMANENT_ERRORS, so I added skip-name-resolve to my my.cnf, restarted the instance, and everything worked great.

StackExchange Database Administrators Q#40150, answer score: 4