29

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?

BenMorel
  • 4,215
  • 10
  • 53
  • 81

1 Answers1

55

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:

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.

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • Do we have any problems as effect of disabling DNS look ups ? – Uday Jun 19 '12 at 17:49
  • @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. – RolandoMySQLDBA Jun 19 '12 at 18:01
  • @RolandoMySQLDBA is there a way to add `skip-host-cache` and `skip-name-resolve` without restarting MySql? – Ran Dec 17 '12 at 04:45
  • @Ran Sorry, the answer is no. I updated my answer to reflect this. – RolandoMySQLDBA Dec 17 '12 at 13:37
  • 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` – Malay M May 22 '17 at 10:22
  • @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. – Pimp Juice IT Oct 05 '18 at 15:55