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 2 3 4 5 6 7 | 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.
Baron,
You could explain why DOS attacks are happening 🙂
Indeed –skip-name-resolve should be the option you turn on for almost any production installation. If you do it early before having to deal with hostname based grants it is very easy.
I also would note effects of host name resolve magnifies because of various bugs with host-cache in different MySQL versions which could cause more resolves when needed as well as the fact this cache size neither can be configured nor seen so typically you have no transparency about how frequently you get cache hits vs misses.
Having some reasonable defaults on resolve timeout would also make sense.
Another related issue I should mention – I’ve seen some operating systems building MySQL with tcp wrappers – in this case reverse lookup happens even if you have –skip-name-resolve enabled.
Peter, right: DoS attacks can happen if the attacker makes sure their IP addresses don’t reverse lookup to anything. Then you just try to connect to the server a bunch of times 🙂
Another thing I could have mentioned — things can potentially break if you connect from name1.com and reverse DNS says that your IP address actually maps to name2.com.
Really, there are so many ways it can go wrong… I think the general advice is “try to build for zero dependence on DNS”.
I find hostnames very useful for some setups. They are often easier to digest at a glance than IPs, especially if you have complicated setups or have 3rd parties that need to post to a table (very common with some payment processing vendors).
How well does the mysql hostname cache work? I thought mysql cached hostnames internally?
If mysql is using gethostbyname_r(), you should be able to use /etc/hosts and modify /etc/host.conf to assure /etc/hosts is read first. This is how I setup our boxes in a cluster a manage that involves 1 master server and 4 slaves. There are another 15 boxes in the cluster. We’ve some scripts to keep /etc/hosts files updated on all servers. This makes it much easier to manage than keeping track of IP addresses.
So if you store IP addresses in the grants, it doesn’t matter whether domain resolution is on or off, right?
I’ve always strictly used IP Addresses in the grants because I can wildcard them to some degree. This can be particularly nice when you have a scale out model where you might not have a definitive number of slaves. As long as they are all on a particular subnet, you can set your host e.g. (‘192.168.20.%’). Then only machines from that block of IPs can connect as that user. If I add a new slave, I don’t have to worry about the grants.
As far as IP Addresses being easy to digest – it’s generally a one-time, set it and forget it sort of thing. You have a network guy or you are the network guy, and he gives you a diagram or tells you what the subnets are. Machines outside our firewall generally do NOT have access to our database servers. If there’s a third party that’s going to post to our servers, there’s an application layer on top where we can do user and data validation, or if it’s a really trusted third party, then we’ll do a VPN tunnel (in which case they map to an internal IP) or similar.
I don’t know, seems like the red flag in this article isn’t “resolving hostnames” so much as “externally accessible”.
Gregory, the emergency customer I helped yesterday would disagree about the red flag =D
Gregory,
If there are not –skip-name-resolve MySQL will always resolve host name even if your grant tables only happen to contain IP addresses.
Jeff, “How well does the mysql hostname cache work? I thought mysql cached hostnames internally?” It does, and it’s actually kind of buggy itself, plus it’s hard to manage.
And in other news, I just got another emergency call that turned out to be DNS issues.
Larger installations often end up needing cryptic hostnames á la db3.cluster2.datacenter3.example.com to make sense anyways. With some planning and a cunning subnetting scheme, I think IP addresses often end up more readable than their DNS counterpart regardless.
Is there a way to verify that skip_name_resolve is set? I have it set in the my.cnf file but I’d like to verify this from within MySQL itself.
Interesting: there is no variable for this in SHOW VARIABLES. You should submit a bug report.
mysql> show variables like ‘skip%’;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| skip_name_resolve | OFF |
Well, at least that confirms that I’m not completely crazy. 🙂 I did check variable and global variables, but to no avail… I’ll head over and submit a report right away!
Submitted. Bug #37168
Hi Baron, All,
I wrote about these issues in some detail about two years ago:
http://jcole.us/blog/archives/2006/04/26/on-ips-hostnames-and-mysql/
Regards,
Jeremy
>Is there a way to verify that skip_name_resolve is set?
One way is to grant access to a user using a domain name, restart mysql and look in the error log
ex:
080819 14:29:06 [Warning] ‘user’ entry ‘vbskin@cpvps%’ ignored in –skip-name-resolve mode.
Hello,
Is it a good idea to disable the host-cache if you do not use DNS resolving?
The server should be a little response faster (or not?).
The host-cache is also used for black listing. So black listing would be disabled.
Regards,
Holger
I haven’t measured, but I doubt it makes a measurable difference.
A comment on this old post which I just came across today. The query:
SELECT user, host FROM mysql.user
-> WHERE host ‘localhost’ AND host RLIKE ‘[a-z]’;
should be updated by using ‘127.0.0.1’ explicitely instead of ‘localhost’ on newer versions of MySQL. On my 5.6.19 install, it seems that MySQL is trying to resolve localhost, which it can no longer do when skip-name-resolve is ON, thus denying access to MySQL.
@Thierry M. Thank you for the tip!
There are two sides to this story, though. When this article was written a couple years ago, IP addresses may well have been more readable, but with IPv6, the premise is that IP addresses can be cryptic because everybody would rely on DNS. Another issue is that using IP addresses all but prevents using SSL/TLS encryption. Also, there is a security value in host-based authentication; it makes hacked credentials useless from other hosts.
dead link – MySQL does both a forward and reverse DNS lookup on authentication attempts by default, read the MySQL manual page.
OK, I’m using a high Resource Website. Do you think remote MySQL Database connection Idea is Good foe Website performance ?