MySQL/MySQL Skip Name resolve

If you can see lines such as

1473502 | unauthenticated user | xxx.xxx.xxx.xxx:56175 | Connect | Reading from net

Chances are that your resolver is not fast enough in responding to MySQL requests, but why does the database send DNS Queries to my (Or my datacenter's / ISP ) resolver.

The answer is simple, assume John is a user on your MySQL who is allowed to connect from his website at john.example.com

In order for MySQL to allow john to connect, it needs to know that John's IP address has a reverse DNS entry john.example.com, without this ability, John's permissions must be stated as john@HISIP rather than john@HISHOSTNAME.

To make things worse, mysql then does the reverse just to make sure that the IP does not have a missleading hostname, making them 2 DNS requests per thread (It is usually not that bad depending on the size of the DNS cache in MySQL).

So, MySQL tells it best.

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. 

So if you are willing to use IP addresses rather than hostnames, or everyone can connect from any host (%) anyway, then you can dissable the lookups MySQL makes before authenticating.

To do that see the instructions below.

Open your my.cnf file (On debian lenny it is at /etc/mysql/my.cnf)

Find the [mysqld] section (Usually close to the top of the file) and add skip-name-resolve to it as seen in the sample from file

A sample from that file is here

---

---

[mysqld]
#
# * Basic Settings
#
ft_min_word_len=2
#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /hd2/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
skip-name-resolve

---

---

Restart MySQL and the problem will disappear.