You are here

Is DNS the Achilles heel in your MySQL installation?

MySQL Performance Blog - Sun, 01/06/2008 - 12:12am

Do you have skip_name_resolve set in your /etc/my.cnf? If not, consider it. DNS works fine, until it doesn't. Don't let it catch you off guard.

Do you really need to restrict MySQL users based on hostnames? If you don't, you should probably disable this feature of MySQL's authentication system. You never know when your hosting provider's DNS (or your own for that matter) will go into the toilet. And when that happens, MySQL mysteriously stops letting users log in, and all kinds of chaos ensues. Worse, it can be kind of hard to know that this is the problem, and diagnosing adds to your downtime.

Here's another scenario: DNS doesn't really fail. It just gets a little bit slow. Subtle enough that you don't really notice it, but enough to cause connection problems every now and then.

I've seen both scenarios recently when working with clients. Oh, and did I mention that not enabling skip_name_resolve actually leaves you open to DoS attacks, if your servers are externally accessible?

To disable two DNS lookups per authentication attempt, you just need to set skip_name_resolve in your my.cnf file and restart MySQL. But before you do that, run the following command:

  1. mysql> SELECT user, host FROM mysql.user
  2.     -> WHERE host <> 'localhost' AND host RLIKE '[a-z]';
  3. +------+--------+
  4. | user | host   |
  5. +------+--------+
  6. | foo  | |
  7. +------+--------+

Any users you see here need to be converted to use IP addresses, IP address wildcards, or 'localhost' or they won't be able to log in after you disable DNS resolution.

For more information on how and why MySQL does both a forward and reverse DNS lookup on authentication attempts by default, read the MySQL manual page.

Entry posted by Baron Schwartz | One comment

Add to: delicious | digg | reddit | netscape | Google Bookmarks