Skip navigation links
7.6 Disk Issues »
Section Navigation      [Toggle]

7.5.9. How MySQL Uses DNS

When a new client connects to mysqld, mysqld spawns a new thread to handle the request. This thread first checks whether the host name is in the host name cache. If not, the thread attempts to resolve the host name:

  • The thread takes the IP address and resolves it to a host name (using gethostbyaddr()). It then takes that host name and resolves it back to the IP address (using gethostbyname()) and compares to ensure it is the original IP address.

  • If the operating system supports the thread-safe gethostbyaddr_r() and gethostbyname_r() calls, the thread uses them to perform host name resolution.

  • If the operating system does not support the thread-safe calls, the thread locks a mutex and calls gethostbyaddr() and gethostbyname() instead. In this case, no other thread can resolve host names that are not in the host name cache until the first thread unlocks the mutex.

You can disable DNS host name lookups by starting mysqld with the --skip-name-resolve option. However, in this case, you can use only IP numbers in the MySQL grant tables.

If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookups with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default value: 128) and recompiling mysqld.

You can disable the host name cache by starting the server with the --skip-host-cache option. To clear the host name cache, issue a FLUSH HOSTS statement or execute the mysqladmin flush-hosts command.

To disallow TCP/IP connections entirely, start mysqld with the --skip-networking option.


User Comments

Posted by [name withheld] on August 27 2003 10:16pm[Delete] [Edit]

If your forward DNS lookup and reverse DNS lookup don't match, then using hostnames as opposed to IP addr when granting privileges might cause you to scratch your head.

eg: If your client machine is called 'foo.domain.com' with IP 1.2.3.4, you might set up grants on your remote MySQL server like this:

grant select on dbname.* to username@foo.domain.com identified by 'somepassword'

But when you try to connect from foo.domain.com you find you get the error "Host '1.2.3.4' is not allowed to connect to this MySQL server"

This is because a reverse lookup of 1.2.3.4 may in fact return a fully qualified domain name of 4-3-2-1.isp.otherdomain.com, ie something *other* than foo.domain.com.

So stick to granting access by IP, not hostname, unless your forward and reverse lookups match.

Posted by Jan Steinman on October 4 2004 1:31am[Delete] [Edit]

If you use IP aliasing and Apache virtual hosting via PHP to access MySQL, it might seem to make sense to use the hosts table to match databases to IPs. Unfortunately, it seems to be the case that the query comes from the machine's base address, NOT the IP aliase associated with the virtual host.

For example:
1.2.3.4 -- physical base address of httpd host
1.2.3.5 -- IP alias associated with the same physical machine above

When a PHP script in the website associated with 1.2.3.5 opens a MySQL connection, it will have come from the address 1.2.3.4! This makes host-based accessing fairly useless in this case, unless I'm missing something.

Posted by Duncan Berriman on October 20 2005 10:56am[Delete] [Edit]

If you are experiencing problems due to reverse dns lookup issues define the ip addresses in the hosts file on your database server.

Duncan Berriman

Posted by Thomas Fante on January 3 2006 8:34pm[Delete] [Edit]

On Fedora Core 4 I was having an issue came up after transporting the server to a new location that logins would be very slow, however once the login happened, the server would execute queries at lightning speed. After much toiling for the answer I found that placing "skip-name-resolve" as a line in my.cnf solved the issue. Strangely, this worked even though no user rights in my database were ever assigned with names, only IP addresses.

Posted by Cyril Spiro on February 6 2006 4:51pm[Delete] [Edit]

For those of you who are having trouble with latency in rendering HTTP via DBI to browsers who are within a network when the internet connection to that network is down (ergo DNS server is unavailable), add a user to the mysql.user table with host='192.168.%' (or whichever 1st and 2nd values you use for the internal IP address schema) and then add skip-name-resolve to the [mysqld] section of the /etc/my.cnf file. This way reverse lookups are not performed for browsers within the network which don't need an internet connection. -- Newbies: don't forget the mysqladmin -u root -p flush-privileges at the command prompt and to restart the mysqld service.

Posted by Daniel Brinca on November 9 2006 3:34pm[Delete] [Edit]

If you prefer to use a hostname instead of an ip on your connection string in a script (to be able to change the ip at will), but don't want the overhead of dns lookups, just add it to your /etc/hosts file (in windows: %WINDIR%/system32/drivers/etc/hosts).

For example, add the following to your hosts file (changing the bogus ip to your server's real ip):

123.123.123.123 mysqlserver1

Note: On linux, make sure you have "order: hosts,bind" on your /etc/host.conf file.

On a script, make the mysql connection like so:

<?
  $sid = mysql_connect ("mysqlserver1", "user", "pass");
?>

Note: this sample is in php, but it can be any other programming language (just type "ping mysqlserver1" on a prompt, on your server)

And there you have it! If your server ever gets assigned a different ip, just update the hosts file with the new one (every script will work as-is, even if under different hostnames).

Add your own comment.