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:
mysql> SELECT user, host FROM mysql.user
-> WHERE host <> 'localhost' AND host RLIKE '[a-z]';
| user | host |
| foo | my.com |
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.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.