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, jcole@foo.example.bar 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 entries¹. 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 cache². 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:

1. 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.
2. Don’t enable libwrap support in MySQL, as this generally only makes things worse.
3. Enable skip_name_resolve in your my.cnf. This will disable all hostname resolutions, period. All GRANTs must be based on IP address.
4. 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.