Sudden MySQL error: Unable to connect to any of the MySQL Hosts

Question

I am using MySQL Community Server 5.5.18 on Windows 7. My application was running absolutely fine without any troubles but since morning it is giving strange messages while opening reports. Reports are on Crystal Reports but the error has nothing to do with the Crystal Reports. This message started appearing suddenly. This application is running very well from last two years.

Error image is below:

Please let me know how to fix this issue.

Answer 1

ISSUE #1 : Connection Errors

Once mysql has racked up enough connection errors, the host blocks all IPs until you do one of two things:

• $ service mysql restart
• mysql> FLUSH HOSTS;

According to MySQL Documentation on FLUSH HOSTS;

HOSTS

empties the host cache tables. You should flush the host tables if some of your hosts change IP address or if you get the error message Host 'host_name' is blocked. When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host tables enables further connection attempts from the host. See Section C.5.2.6, “Host 'host_name' is blocked”. You can start mysqld with --max_connect_errors=999999999 to avoid this error message.

Try bumping up max_connect_errors (default is 10) to some obnoxiously high number, like 999999999 (about 1 billion), in /etc/my.cnf as follows:

[mysqld]
max-connect-errors=999999999

You will then just restar mysql. To put this new setting in place without restarting, just run this command in the mysql client:

mysql> SET GLOBAL max_connect_errors = 999999999;

ISSUE #2 : DNS

If you are connecting to mysql using DNS, this could be one possible source of issues if:

• DNS servers go down
• DNS servers cannot be reached

To alleviate DNS issues, you need to add the following to /etc/my.cnf (mysql restart required):

[mysqld]
skip-host-cache
skip-name-resolve

You also need to look at the host column of mysql.user. If any of the users have a DNS name, replace it with one of the following choices:

• Public IP
• Private IP
• LoadBalacner-managed VIP
• Netblocks (such as 10.1.2.%) of the first three choices

Afterwards, run mysql> FLUSH PRIVILEGES;