MySQL and Name Resolution

MySQL and DNS (Domain Name Server) don’t really get along well. To minimize this “conflict,” it’s important to understand how MySQL interprets and handles name resolution.

First, it’s important to say that most applications running nowadays are server-based — meaning the client (user) runs the application on an application server, not directly on their desktop.

It’s reasonable to assume application servers have fixed IPs, unlike desktops, which in large networks typically have dynamic IPs assigned by a DHCP server.

When a new session (or thread) is started from a client request (usually an application server), MySQL needs to take several actions; the most relevant here is authentication and user validation.

MySQL then checks if the user exists and is valid. A valid user is simply one that exists in the mysql.user table. And here the DNS problems begin. MySQL will request DNS to return the IP for the hostname, and also the hostname for the connecting IP, using the functions gethostbyaddr() and gethostbyname().

So far, you might ask: “What does this have to do with me, the DBA?” Well, the DBA should care, because here lies a potential big problem.

MySQL insists on knowing where the connection/request is coming from to create a session (thread) for that user. Therefore, it deems it reasonable to get the hostname and IP of the origin computer, and asks the nearest DNS server to resolve those names, providing hostname, IP, and reverse DNS (usually a fully qualified domain name — FQDN). This also serves as an additional security measure.

DNS servers are designed to be fast, but they fail quite often. This is well known. Whether the network is big or small, and regardless of the number of requests on port 53 (DNS port), DNS servers fail. They handle many requests and have no control over guaranteeing results. They fail! We won’t get into the reasons here, since it’s probably not our area, but yes, they do fail — and they can take time to respond.

Typical MySQL server behavior when suffering from DNS issues includes:

a) slow user authentication (you might notice multiple threads with status UNAUTHENTICATED USER visible via SHOW PROCESSLIST or other monitoring tools); or
b) users unable to connect.

When users cannot connect, MySQL tries to avoid querying the DNS server on every new connection by keeping a cache of the last 128 IPs and hostnames, called the HOST CACHE. This HOST CACHE also logs connection failures and failed login attempts (such as invalid passwords). If the number of failed connection attempts exceeds a limit set in the my.cnf configuration file, the IP & hostname get marked as bad (blacklisted). From then on, connections from that source are rejected. To fix this, either restart the MySQL server (bad!) or run the command FLUSH HOSTS to clear the HOST CACHE and start fresh. The downside is MySQL will again need to rely on DNS and… hold on tight!

Bad news: there is no built-in way to view the IPs and hostnames stored in HOST CACHE, nor their blacklist status. A simple command like SHOW HOST CACHE or something like SHOW CACHE HOST would be great. Maybe even a command like “SHOW ME THIS, PLEASE” would be nice — and funny to see the foreigners handle those accents!

For the brave, there is a plugin or patch that adds this functionality, but I don’t recommend it unless you’re willing to give up official MySQL binaries. If interested, here’s the magical plugin-patch link:
http://jcole.us/blog/archives/2006/09/26/followup-on-ips-hostnames-and-mysql/

I played around with it and it worked well, but I still prefer the official binaries from Oracle/Sun/MySQL. It’s worth noting there’s an ongoing request in the MySQL community for a SHOW HOST CACHE command. Hopefully, it will appear someday — perhaps as a table in the MySQL INFORMATION_SCHEMA database, which would make much more sense.

UNAUTHENTICATED USER is a serious problem. In 2011, I experienced painful issues where everything “worked fine yesterday,” but today “nobody can connect to the database.” The cause? The DNS server went crazy — saturated, stopped responding, or simply decided to mess with the database.

Relying on DNS for every connection can be traumatic. Even with HOST CACHE to reduce DNS queries, we have no control or visibility into this black box.

Also remember that most applications still don’t use persistent connections, so each new command may open a new connection, meaning a new DNS query in theory.

Now do the math: forget for a moment the HOST CACHE and THREAD CACHE. How many queries per second (QPS) are executed against all MySQL servers you manage? 5,000 QPS? 10,000 QPS? That’s thousands of DNS requests per second, just from the database, not counting other network applications that also need name resolution.

My point? DNS servers already have a lot of work, and if MySQL floods them with thousands of requests per second, problems are guaranteed.

Summary: How MySQL uses DNS

Client makes new connection request -> Host Cache lookup -> DNS query -> Host Cache update/insert -> User authentication (user + password + host)

Tips: How to ease the MySQL and DNS relationship

• Avoid using DHCP for application servers. IPs may change and conflict with those stored in the HOST CACHE.
• If you can’t connect to MySQL with a user and password that you know are correct, try running FLUSH HOSTS.
• If you see many threads with status UNAUTHENTICATED USER and slow connection times, and you need to run FLUSH HOSTS often, enable the skip-name-resolve option in the MySQL configuration file. This requires restarting the MySQL server (mysqld).

Implications of using the skip-name-resolve option

After adding skip-name-resolve to your MySQL config file (my.cnf or my.ini), you must restart the MySQL service to take effect.

This option disables DNS resolution entirely. The HOST CACHE will no longer map IPs to hostnames. Connections will authenticate faster, but user accounts can no longer be defined as <user>@<hostname>. Instead, all users must be defined as <user>@<ip>.

My recommendation is to use skip-name-resolve. It solves many issues not covered here, reduces connection times, lowers the load on the MySQL server during authentication, and removes a point of failure (DNS).

Schedule a meeting here

Visit our Blog

Learn more about databases

Learn about monitoring with advanced tools

Have questions about our services? Visit our FAQ

Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!

Discover the History of HTI Tecnologia