skip-name-resolve to speed up MySQL and avoid problems


MySQL Reverse DNS took us all day to debug! and right before reinstalling MySQL on another server, a guy who had this problem told us the solution: skip-name-resolve


skip-name-resolve Problem

After searching the net I’ve found that by default MySQL grants authentication on users based on IP address or hostnames. When there is a connection from a particular host, MySQL does a reverse DNS lookup and compares the hostname and IP address.

In our case there was some problem with the DNS server and the high traffic website with more then 70,000 visitors a day was down. We started looking into the memcache server, after that the nginx and the 2 Apache servers and at the end we have located the problem in the MySQL database.

Because the website was down for a long time we have decided to attach the database on one of the other servers and later find out what was the problem. But as the backup of the database was finishing a guy walks in and with a little smile on his face asked us: Do you have MySQL reverse DNS turned off? And there was the solution!


skip-name-resolve Solution

Here is the simple fix to this problem; just add the following line into your MySQL configuration file and restart the server.

[mysqld]
# Skip reverse DNS lookup of clients
skip-name-resolve

This saved us a lot of time and after this I’ve reconfigured all my MySQL servers with skip-name-resolve parameter.

For more informations see the MySQL reference manual for more details.



Subscribe
Notify of
guest
8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
MySQL in PDX
MySQL in PDX
11 years ago

Thanks for your posting on this topic.

The MySQL docs say:

http://dev.mysql.com/doc/refman/5.5/en/server-options.html

“If you use this option, all Host column values in the grant tables must be IP addresses or localhost.”

Does this mean that if I use skip-name-resolve on my MySQL server, then I can no longer use a wildcard like ‘%’ to allow connections from any host? Instead, I now have to explicily list EVERY possible IP that I want to allow connections from?

Or will ‘%’ wildcards still work if I enable skip-name-resolve ?

Thanks again!

daedulus_hamlet
daedulus_hamlet
5 years ago
Reply to  MySQL in PDX

Wildcard will work. You won’t be able to restrict permission to certain IP addresses unless you have setup static IP since IP keeps changing for machines.

Lanbo
10 years ago

Is this a security threat if I were to add this setting in?

Thanks

Indospace.io
4 years ago
Reply to  Lanbo

It’s actually more of a security fix. now there is only one way to get in, instead of yourdomain.io and /127.0.0.1, you will just use your server ip>/127.0.0.1.

Make sure to disable remote access in your my.cnf file, if it is not required. Actually, it should never be required, you should ssh into the server, then access the DB.

The best way to disable external connections to your MySQL is setting bind-address=127.0.0.1 in my.cnf configuration file. If you still need to block MySQL external access using firewall, you drop or reject all packets sending to 3306 port.

Rosario Jones
10 years ago

MySQL server keeps crashed on http://wootmanga.com. Does performing large data is broking the server?

Tom Dings
Tom Dings
6 years ago

I am aware of this setting but is it not just a work-around for ignoring a well configured mysql setup ? Using currently MariaDb Galera clusters without this option and I never had any issues. For sure I do not know all the features which are available but this is a very ‘normal’ setting I think. If it is performing better it is great but as far as I read the manuals for setting up Galera it is mentioned all the time to put ips / hostnames in the hosts file of each cluster node. So working DNS or not… Read more »

Advertisement