skip-name-resolve: how to disable MySQL DNS lookups

MySQL Reverse DNS lookups are often the cause of long delay and slow MySQL performance when running certain SQL queries.

Sometimes it takes hours or days to detect that the problem in your slow SQL connection times is indeed caused by the skip-name-resolve variable located inside your MySQL configuration file.

This tutorial applies to the following scenarios:

• Disabling skip-name-resolve on cPanel servers
• Disabling skip-name-resolve on MySQL 5.x versions
• Turn off skip-name-resolve on any Linux distribution

skip-name-resolve and DNS lookups

DNS lookups for MYSQL hosts are almost all the time unnecessary, as they only add additional rountrips for the query to be finished.

The funny thing is most of the developers that work with MySQL based apps still don’t know what this is.

In clear words, when you use ‘localhost’ or ‘server.hostname.com’ in your database server variable, you are telling MySQL that it should resolve that hostname using a DNS lookup.

MySQL grant command allows you not only to grant permissions for hostnames, but also IP addresses, that’s why using 127.0.0.1 (local server), or the remote server IP is not a bad idea at the end.

When you are having a few users doing a DNS resolve operation it may pass under the radar, but when you have a few hundred or thousands connections, this becomes a real problem.

Do you have MySQL reverse DNS turned off? If not, continue reading to disable skip-name-resolve

skip-name-resolve: how can I disable this?

The fix for this problem is really easy in fact, as this is a server-side MySQL configuration, it will affect all your databases.

Before changing this, make sure all your users have privileges to connect using IP addresses, instead of hostnames.

Add this line to your MySQL configuration:

nano -w /etc/my.cnf

Locate the mysqld block, and add these two lines:

# Skip reverse DNS lookup 
skip-name-resolve

Save the file and restart MySQL or MariaDB

service mysql restart

That’s it, you have officially disabled skip-name-resolve, and all your MySQL connections should be way faster now.

What changes after I set skip-name-resolve in my.cnf?

skip-name-resolve directive lets MySQL avoid DNS lookup response when checking for clients connections to the MySQL server. This way, your MYSQL server will only use IP addresses, instead of hostnames.

If you choose to use skip-name-resolve, make sure your MYSQL connection privileges are set to allow IPs, and not hosts.

On most systems, for the local MySQL Server you will need to use host=127.0.0.1 for ipv4 and  host=::1 for ipv6 networks, instead of the classic “host=localhost”.

Summary

That’s all, after you set skip-name-resolve on my.cnf you will notice your MySQL queries are really fast, usually less than 0.5 seconds if your application is developed following good programming practices.

Remember to always include this simple configuration inside your my.cnf file to turn off MySQL reverse DNS lookups.

Further reading:

• MySQL DNS docs