Analysis of connection timeout caused by reverse resolution of Mysql DNS (skip-name-resolve)

After the MySQL database receives a network connection, it first obtains the IP address of the other party, and then performs reverse DNS resolution on the IP address to obtain the host name corresponding to the IP address. Use the host name in the permission system for permission determination. Reverse DNS resolution is time-consuming and may make users feel slow. Sometimes, if the host name resolved in reverse direction does not point to this IP address, the connection will fail.

You can disable reverse DNS resolution for MySQL in the configuration file. You only need to add the following lines to the [mysqld] section of my. cnf:

Skip-name-resolve (same for windows and linux)

When the device is connecting to mysql, it takes about 4 s to wait for the server's banner information, which affects the connection speed of the Mysql service.
Perform verification as follows:

1. Telnet port verification

Telnet the port of the Mysql service through the device and the Virtual Machine (Linux System) separately. The following occurs:

Device (UAG/terminal): After telnet, wait about 10 s for the Mysql server to respond.

[DPtech-Developer-Shell] telnet 10.101.0.206 3308
Trying 10.101.0.206...
Connected to 10.101.0.206.
Escape character is '^]'.
E
5.0.67-community-nt-log? Hc95
Virtual Machine (Ubuntu): After telnet, the Mysql server returns immediately

[Root] ~ # Telnet 10.101.0.20 6 3308
Trying 10.101.0.206...
Connected to 10.101.0.206.
Escape character is '^]'.
E
5.0.67-community-nt-log? D % (; 1 $] +, success! Zdh ''? G) 6r] YConnection closed by foreign host. // the time consumed here is very short.

2. Verify by program

For specific source code, see Attachment: verification program source code
The source code basically sets the Recv timeout. After a socket connection is established, the data is received, and the time is timed and output.

The results on the device and the virtual machine are as follows:
Device:

[DPtech-Developer-Shell]/tcpclient_mips 10.101.0.1 3306
Duration: 19553
Recved 68 bytes
@
5.5.2-m2-community % uD3q 'n)

Virtual Machine:

[Root] tcp_demo #./tcpclient 10.101.0.1 3306.
Duration: 10525
Recved 68 bytes
@
5.5.2 m2-communitd ~ K ~ Y "; B

It can be found that a device consumes about 9 s more than a Linux server, and 10 seconds may be the recv timeout time.

3. Telnet verification with different operating systems

Through the Windows system, the Linux virtual machine, the device, respectively through Telnet connection attempts, through packet capture analysis that only the device time is relatively long, other time is relatively short.
After the socket in the device is created during packet capture, the MYSQL server needs to send multiple NBNS packets before transmitting the banner information, this problem does not occur in the process of Linux Virtual Machine and Windows host.
I found some information about MYSQL NBNS packets:

Questions about the Mysql Forum:

Http://forums.mysql.com/read.php? 11,250982, 250982 # msg-250982

Reply to this question
Http://forums.mysql.com/read.php? 11,250982, 254683 # msg-254683

From the answer, it seems that there are some versions of the problem. The temporary solution is to configure the Mysql server. If Named Pipes is not enabled, that is, the Named pipeline function can solve this problem.

After finding the relevant information, we learned that the remote connection timeout may be due to the fact that Mysql has enabled DNS reverse resolution by default. The server tries to resolve the Host Name of the Connection Client each time it connects, resulting in a long time.

The solution is to configure a skip-name-resolve in the [mysqld] section of the my. ini file on the server to disable reverse DNS resolution enabled by Mysql by default.

Telnet the device to the virtual machine or Windows system again to find that the connection timeout obviously does not exist.

In addition, the same problem exists when you connect the C code you write. After modifying skip-name-resolve, you can actually find that the problem does not exist:

Device:

[DPtech-Developer-Shell]/tcpclient_mips 10.101.0.1 3306
Duration: 10520
Recved 68 bytes
@
5.5.2 m2-community