MySQL DNS Details

MySQL manual section 7.5.6. How MySQL Uses DNS can be summarized in one sentence: A new thread looks for hostname in cache, if not present it tries to resolve it. Simple enough, however in cases where MySQL is primarily accessed over a network (such as a dedicated backend server), DNS resolution can become the cause of a very elusive type of performance degradation, sometimes to the point of complete MySQL failure.

How MySQL Really Uses DNS

If you're not a programmer, the difference between gethostbyaddr() and gethostbyname() as mentioned in the MySQL manual may not tell you what you should know. gethostbyaddr() performs a reverse DNS lookup; it tries to resolve an IP address to a hostname. gethostbyname() performs a forward DNS lookup; it tries to resolve a hostname to an IP address. When a network connection is made to MySQL it only knows the remote end's IP address. Since grant tables can specify privileges by hostname, MySQL has to resolve every IP address to its hostname. For added security MySQL then also resolves that hostname to an IP address to make sure this IP address matches the IP address of the remote end. In short, for every TCP/IP connection, MySQL does a reverse and forward lookup. For added performance, successful lookups are cached and not tried again until either MySQL is restarted or issued a FLUSH HOSTS; command.

DNS topics in the MySQL manual are largely based on working setups—were DNS resolves quickly and correctly. However, we know DNS isn't always quick or correct. In less common cases DNS doesn't even respond. The following looks at how MySQL handles these conditions, as well as a potential bug involving DNS which stops MySQL from authenticating network connections.

This document isn't geared toward MySQL source code hackers, but in case you do want to see the respective source code it can be found in sql/hostname.cc starting at line 196 through the end of the function, which is ip_to_hostname(), for version 4.0.22 on a Linux system. Numbers in brackets like [200] scattered throughout the document are source code line references.