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.
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!
this is from MySQL:
“Do not resolve host names when checking client connections. Use only IP addresses.”
It will not do DNS resolve to hostnames, and % wildcard should work
You can do ‘192.168.1.%’, but you CAN’T do ‘%.domain.com’
This command will not use DNS servers.
Regards
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.
Is this a security threat if I were to add this setting in?
Thanks
No it’s not a security threat, just you will not be able to use hostnames in your grant tables.
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.
MySQL server keeps crashed on http://wootmanga.com. Does performing large data is broking the server?
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 »