A MySQL problem or something else?

[Chris]

Sometimes problems manifest inside of MySQL but the MySQL server is really not the problem. A good example is how MySQL uses reverse DNS lookups for authentication.

The Problem

You are alerted to a problem with a MySQL server either by Nagios or whatever… You log into the server and everything seems to be working correctly until you run “show full processlist”. The majority of your connections are in an “Unauthenticated” state and the rest are in some other state, probably “Sleeping”. You speak with the lead developer and they state that there have not been any code changes for 1 month. It’s an odd problem, when you see it; however, the solution is not that obscure when you think about how MySQL uses DNS.

The Solution

When you restart MySQL with “skip-name-resolve” enabled you might think that all of your problems are gone based on the issue describe above. Well, just make sure that all of the hosts in the mysql.user table are IPs or IP ranges and not FQDN.

Not only will enabling "skip-name-resolve” save time and money if DNS goes down, it is also good for performance. Here are some quick tests to illustrate the performance gains:

-- Performance Benefit

Without “skip-name-resolve” enabled

shell> date; mysql -u root -h 192.168.0.1 ; date
Mon Oct 5 15:20:40 PDT 2009
ERROR 1130 (00000): Host '192.168.0.2' is not allowed to connect to this MySQL server
Mon Oct 5 15:20:43 PDT 2009

Note the time change is 3 seconds

With “skip-name-resolve” enabled:

shell> date; mysql -u root -h 192.168.0.1 ; date
Mon Oct 5 15:22:01 PDT 2009
ERROR 1130 (00000): Host '192.168.0.2' is not allowed to connect to this MySQL server
Mon Oct 5 15:22:01 PDT 2009

Note that the server comes back immediately with the same error

Best Practice
I always make sure the “skip-name-resolve” is in any MySQL configuration file (my.cnf) in all of my installations.

[Chris]

Totally agree!!!!!

[Mark R]

I'm not sure why skip-name-resolve is not the default. It makes sense.