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  scattered
throughout the document are source code line references.
When a network connection is made to MySQL, all MySQL knows is the remote end (peer) IP. First MySQL
resolves the peer IP to a hostname by calling the standard C library gethostbyaddr() . Naturally,
there are two possible return values: Either the lookup succeeds and returns a hostname, or it fails.
If the reverse lookup succeeds MySQL goes onto the next step. If the reverse lookup fails, the failure
is cached (remembered) , and MySQL will not try to lookup this IP address again until either MySQL
is restarted or issued a FLUSH HOSTS; command. Failure can mean, basically, one of two things: Either
DNS doesn't respond at all, or it responds with NXDomain (non-existent domain). In the first case MySQL
waits at least 10 seconds for a response. Since MySQL uses standard C library resolver functions
(gethostbyaddr() and gethostbyname()) the operation and limitations of DNS lookups is a factor of the C
library, not MySQL. Therefore we have to look at how these resolver functions work.
Standard C library gethostbyaddr() uses /etc/resolv.conf. (For our purpose here we forget about /etc/hosts
which might also be used.) It queries each listed nameserver twice in order, waiting 5 seconds for each one.
For example, if /etc/resolv.conf lists 2 nameservers, the first is queried, then the second, then the first
again, then the second again. Therefore the maximum wait time for gethostbyaddr() is 10 seconds times the
number of nameservers.
For MySQL this means, if no nameserver in /etc/resolv.conf responds, it takes a minimum of 10 seconds to
fail, but only for the first time for the same IP address because this failure will be cached. While MySQL
is waiting for DNS, the status of this connection in the process list is shown like:
| Id | User | Host | db | Command | Time | State | Info |
| 4 | unauthenticated user | 192.168.0.3:1112 | NULL | Connect | NULL | login | NULL |
Where 192.168.0.3 is the peer IP address MySQL is trying to resolve. Usually this won't ever be a problem
because what server would have non-responsive DNS?
The other most common kind of failure is a return value of NXDomain, which means the DNS server
has no matching resource record, which would be a PTR record at this point. Each nameserver is queried twice
in order, waiting 5 seconds for each one, and fails once any nameserver returns NXDomain.
If the reverse lookup succeeds by returning a hostname, MySQL then does a forward lookup on this hostname.
(Technically, MySQL doesn't allow an empty hostname . If the reverse lookup returns an empty hostname
this is considered a failure.)
For added security, MySQL does a forward lookup on the hostname it obtained from the reverse lookup.
MySQL is checking that the IP address this hostname resolves to matches the peer IP address .
MySQL resolves the IP address to a hostname by calling the standard C library gethostbyname() .
If the lookup succeeds its always cached; if the lookup fails for any reason it is never cached.
The forward lookup can fail for the same reasons the reverse look can fail: Non-responsive DNS or
an NXDomain response. The standard C library gethostbyname() works a little differently than gethostbyaddr().
Standard C library gethostbyname() uses /etc/resolv.conf, queries each listed nameserver twice in
order, and waits 5 seconds for each one just like gethostbyaddr() except it goes one step further if
the lookup fails. If gethostbyname() receives no response from any nameserver, it tries all over again but
with a modified hostname: It takes the domain of the server's hostname and appends it to the hostname
in question. For example, if your server's hostname is my.server.com, and the hostname in question is
someone.else.com, gethostbyname() will try to resolve someone.else.com.server.com. The maximum wait time
for gethostbyname() is 20 seconds times the number of nameservers.
If gethostbyname() receives an NXDomain response from a nameserver, it always tries again with the
modified hostname (e.g. someone.else.com.server.com.). Interestingly enough, it may or may not retry
with all nameservers. If the first nameserver returns NXDomain, gethostbyname() retries only that nameserver
with the modified hostname. If the first nameserver doesn't respond, and the second one does, gethostbyname()
retries the first nameserver, then retries the second nameserver.
In either case, should gethostbyname() fail the failure is never cached by MySQL . Only successful forward
lookups are cached. While MySQL is waiting for forward resolution the status of this connection in the
process list is shown the same as above.
After reverse and forward lookups complete successfully MySQL makes two additional checks. First it checks
the hostname obtained from the reverse lookup does not start with a series of numbers followed by a dot .
"MySQL disallows matching on hostnames that start with digits and a dot. Thus, if you have a host named something
like 1.2.foo.com, its name never matches the Host column of the grant tables" (5.5.5.
Finally, MySQL checks that the IP address obtained from the forward lookup matches the peer IP address .
If the two match, the function (ip_to_hostname()) returns the hostname . If the two do not match this
failure is cached . (And if you're curious, ip_to_hostname() returns to sql/sql_parse.cc line 525.)
A Bug In MySQL DNS?
I originally became intersted in how MySQL uses DNS because I literally watched a MySQL server for over 20
hours trying to figure out why it would stop authenticating network connections before I realized it was
a problem with DNS, easily fixed by using the --skip-name-resolve startup option. My original post to several
I have a very weird problem with MySQL 4.0.22 where it stops authenticating remote connections.
Eventually MySQL reaches max_connections and it has to be restarted. Sometimes it restarts nicely,
sometimes it wont and it has to be killed. Here's what I've done to isolate the problem to MySQL:
1. Local connections always work so MySQL isn't frozen
2. Any remote server is effected, so it's not MySQL blocking one particular IP
3. Any remote script is effected, so it's not a particular PHP or Perl issue
4. No query is frozen. I enabled general logging and every time every connection ID quits nicely, then there is a large gap of no queries, no connections.
5. Any db or table is effected
6. The network is ok because I never lose SSH or ping connectivity
7. In the priv table it doesn't matter if it's allowed by IP or domain--either fail
8. Full process list shows *nothing* but:
User: unauthenticated user
9. Any MySQL user is effect. I created a new user: It won't authenticate either
10. MySQL is the only thing running on this server--No firewall, no floodguard, no IDS, etc.
11. Server load, queries/second are all very low and neither shows any relation to the issue
Later I Googled the problem more and found I was not alone, and neither was this a new phenomenon:
Yet despite my sincere efforts to break MySQL and reproduce this potential bug I cannot. Since --skip-name-resolve
fixes the issue, we know the issue probably lies somewhere between sql/sql_parse.cc line 522 and line 534,
the conditional block of code which is not executed when --skip-name-resolve is set. For now, this potential
bug remains easily avoidable and easily worked around.