MySQL [Warning] IP address could not be resolved

Question

I'm running MySQL5.6.3 on a CentOS 6.1 virtual machine running on Windows XP in VirtualBox.

The network card is configured in bridge mode, so my physical & virtual machines share the same ethernet card.

On the virtual machine, everything works fine: internet access, DNS lookups. However, connections to the MySQL daemon take a while, and the logs keep showing this warning:

[Warning] IP address '192.168.1.201' could not be resolved: Temporary failure in name resolution

192.168.1.201 is my host machine on which I'm runnning the MySQL client.

Looks like although DNS lookups work fine, reverse DNS lookups end up in a timeout.

Here is the virtual machine configuration:

# cat /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE="eth0"
HWADDR="08:00:27:4B:3D:7C"
NM_CONTROLLED="yes"
ONBOOT="yes"
NETMASK=255.255.255.0
IPADDR=192.168.1.200
GATEWAY=192.168.1.1
PEERDNS=yes

# cat /etc/resolv.conf
nameserver 192.168.1.1

Is there something wrong in my network configuration?

Solution

IMHO This sounds like you need mysqld to stop using DNS.

Please do the following: Add this to /etc/my.cnf

[mysqld]
skip-host-cache
skip-name-resolve

Them restart mysql. From then on, mysql will no longer resolve addresses via DNS.

Give it a Try !!!

CAVEAT

Please read these options in the MySQL Documentation:

• skip-host-cache
• skip-name-resolve
• DNS Lookup Optimization and the Host Cache

Also, there is one restriction to using this: You cannot use DNS names in the host column of any of the grant tables.

UPDATE 2012-12-17 08:37 EDT

I was recently asked if skip-host-cache and skip-name-resolve could be set without a mysql restart. Let's find out:

mysql> show variables like '%host%';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | ************ |
| report_host   |              |
+---------------+--------------+
2 rows in set (0.00 sec)

mysql> show variables like 'skip_%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| skip_external_locking | ON    |
| skip_name_resolve     | OFF   |
| skip_networking       | OFF   |
| skip_show_database    | OFF   |
+-----------------------+-------+
4 rows in set (0.00 sec)

mysql> set global skip_name_resolve = 1;
ERROR 1238 (HY000): Variable 'skip_name_resolve' is a read only variable
mysql>

As shown, skip-host-cache is not visible in the list of global variables. As for skip_name_resolve, it was visible. However, it cannot changed dynamically because it is a read-only variable.

Therefore, skip-host-cache and skip-name-resolve can only be changed via a mysql restart.

Comments

[Amin Sh]

refer to my another answer for this question: serverfault.com/a/778161/96578

[Uday]

Do we have any problems as effect of disabling DNS look ups ?

[RolandoMySQLDBA]

@Uday the only concern within mysql is this: You cannot use a DNS name effectively in the host column of mysql.user. If you have any, you need to replace them with the public or private (preferable) IP address instead.

[Ran]

@RolandoMySQLDBA is there a way to add skip-host-cache and skip-name-resolve without restarting MySql?

[RolandoMySQLDBA]

@Ran Sorry, the answer is no. I updated my answer to reflect this.

[Malay M]

Can there be any possible problem like connectivity from localhost or any other issue? I'm facing problem for ip 243.221.167.124.adsl-pool.sx.cn

[Pimp Juice IT]

@MalayM Actually, I believe the answer for the localhost is "yes" because I had this problem. I had to update the connection to use 127.0.0.1 rather than local host. I don't remember the specifics though but I decided to leave DNS resolution enabled after have a few issues with disabling it in the environment I tried to disable it.