Slow connection due to reverse query of MySQL DNS

Scenario
A module on machine A connects to MySQL on machine B and works normally in the lab network environment. Similarly, A and B are two machines, the network environment is switched to A small LAN environment isolated from the outside world. The connection between modules on A and MySQL on B is very slow.

Environment
SUSE 11x64, MySQL 5.1

Analysis
The module on A does not respond for A long time after it is started and cannot provide services. Because the module code compilation does not have sufficient debugging information, it does not know where the program is blocked. Therefore, use gdb for debugging. After the program is interrupted, execute bt to view the call stack. The program calls mysql_real_connect to establish a connection with MySQL and calls read in mysql_real_connect to receive data returned by the MySQL server, the program is stuck in the read call.

Use telnet on A to connect to port 3306 of MySQL on B and establish A normal network connection. However, when A accesses MySQL on B using A MySQL client, it takes A long time to establish A MySQL connection (including establishing A network connection and user authentication). During this period, netstat is used to view network connections, confirm that the MySQL client on A has established A network connection with the MySQL server on B. Therefore, the problem lies in the subsequent protocol interaction between the client and the MySQL server.

After carefully comparing the two network environments, the only difference is that the problematic network environment cannot access the Internet, the other environments are the same, and even the IP addresses of machines are the same. At this time, I remembered that a similar problem occurred when I used Oracle in the LAN. At that time, I just needed to clear the DNS configuration (/etc/resolv. conf) of the Oracle machine. So out of luck, I cleared the DNS configuration of machine B and restarted the module on machine A. Everything went fine.

Continue to analyze the problem and find that the previously configured DNS is an Internet address, which can be accessed in the lab network environment and cannot be accessed in the subsequent small LAN environment. It is assumed that MySQL uses DNS when a user establishes a connection. Because the configured DNS cannot be accessed, MySQL will wait until the network times out.

I found related information online and found that MySQL does perform DNS lookup on the Client IP address during user login. To avoid this lookup process, you can make the following configuration in the MySQL configuration file my. cnf:
12 [mysqld]
Skip-name-resolve

If the login is slow due to DNS lookup, use show processlist on the MySQL server to see a connection similar to the following:
123 | 592 | unauthenticated user | 192.168.3.20: 35320 | NULL | Connect | login | NULL |
| 593 | unauthenticated user | 192.168.3.20: 35321 | NULL | Connect | login | NULL |
| 594 | unauthenticated user | 192.168.3.20: 35322 | NULL | Connect | login | NULL |

Solution
1. Use the skip-name-resolve command;
2. Configure an accessible DNS address or directly clear the DNS configuration.

Reference
1. MySQL has a large number of UNAUTHENTICATED users.
2. MySQL DNS query is disabled.