This is the first official post in my new category: MySQL Tips. Feel free to subscribe to the category-specific RSS feed, if you prefer.
First, for a bit of background information…
In MySQL, access is always granted based on the combination of username and hostname (or IP address, in some cases), and password—for example, email@example.com could be a valid username and hostname combination. As far as I know, this is a historical thing—MySQL authentication is based on username and hostname because it has always been that way.
In order to verify the “network credentials” of the connecting client, MySQL uses a “double reverse-DNS lookup” on the IP of the incoming connection. In short, MySQL first finds out the hostname using the IP address of the incoming connection via a reverse-DNS lookup using the gethostbyaddr() function or similar. It then resolves that hostname to its IP addresses via a forward-DNS lookup using the gethostbyname() function or similar. Finally it verifies that the original connecting IP is one of the IPs returned.
This can be a problem because reverse-DNS and forward-DNS lookups are fairly expensive, and block waiting on a reply from a DNS server. There are timeouts involved, but they are fairly long, I believe 30 seconds. In order to avoid this long overhead, MySQL has a hostname cache internally.
There are a few problems with the current implementation:
- The hostname cache is statically sized at 128 entries1. If the cache gets full, the older entries will be purged. If you have more than 128 distinct client machines connecting to a single MySQL server, the cache becomes completely ineffective.
- If a DNS server cannot be reached to resolve an IP address or hostname, the connection cannot be accepted, and in fact in some circumstances MySQL will cache the failure and refuse further connections from the same IP.
- If the forward-DNS mapping and reverse-DNS mapping don’t match up, MySQL will refuse to accept the connection.
- There is no way to see the contents of the hostname cache2. The only available SQL command dealing with it is FLUSH HOSTS which just clears the entire cache.
- If a particular client machine fails connecting for max_connect_errors, that client (or rather, that IP address) will be refused further connections unless FLUSH HOSTS is run.
- If libwrap (tcp wrappers) support is enabled in MySQL, that can often cause the reverse-DNS lookup to be done while MySQL is in single-threaded accept(), where a delay in resolution will mean blocking all other clients from connecting.
The whole situation means a few bad things for running busy production MySQL servers:
- MySQL’s reliability is only as good as your DNS infrastructure. If DNS is down/flaky, so is MySQL.
- If your network is flaky, it can mean that you quickly reach the default max_connect_errors of 10 and further connections from that client machine will be refused.
I would recommend a few things:
- Never ever allow connections to a MySQL server from the outside world. This opens up a huge potential for DNS-based DoS attacks on MySQL.
- Don’t enable libwrap support in MySQL, as this generally only makes things worse.
- Enable skip_name_resolve in your my.cnf. This will disable all hostname resolutions, period. All GRANTs must be based on IP address.
- Set max_connect_errors to something very high, say 99999999. This will avoid your servers getting sporadically blocked because of network or client flakiness.
Good luck, and I hope the first entry of MySQL Tips is useful!
1, 2 I have a patch for MySQL 4.1, which adds a SHOW HOST CACHE command, and a configurable global variable host_cache_size.
and where’s the link to that patch?
“If the forward-DNS mapping and reverse-DNS mapping donâ€™t match up, MySQL will refuse to accept the connection.”
This isn’t a “problem”, this is a “feature”.
Lest I configure my reverse dns to say that I am “w3.yahoo.com” and start trying to log into a Yahoo MySQL server.
“Set max_connect_errors to something very high, say 99999999. This will avoid your servers getting sporadically blocked because of network or client flakiness.”
Are you high? That also means that an attacker gets the opportunity to make about 100 million brute force attempts to get access, all the while you’ll never know it’s happening.
Agreed on the first part, I probably shouldn’t have put that under “problems”, it’s more like “facts”. It can be a problem though, in that many people don’t realize that’s the case.
As far as the second, if you’re referring to trying to crack the user/password combo, you get as many of those brute force attempts as you want, anyway, because the host blocking is not used for bad password attempts. I maintain my ground on this one. ;)
Just to elaborate on the “facts”: Whenever a user attempts to connect with a mismatch in forward/reverse DNS, that host is blocked indefinitely until FLUSH HOSTS is run. And again, there’s no way to see which hosts are blocked.
Sam’s random musings » On IPs, hostnames, and MySQL
Keep up with this sort of quality and you’re going to be writing the “inconvenient feature fix” dev schedule… :)
OK, Do you think remote MySQL Database connection Idea is Good foe Website performance ?
Question: Is there any scenario in which I would not want to enable skip_name_resolve?