Mysql DNS reverse parsing causes the connection timeout process to analyze of skip name resolve

  • 2020-05-15 02:21:39
  • OfStack

After the MySQL database receives a network connection, it first gets the IP address of the other party, and then reverse-DNS the IP address to get the host name corresponding to the IP address. Use the host name to determine the permissions in the permissions system. Reverse DNS parsing is time consuming and can feel slow to the user. Even in some cases, the reverse-resolved hostname does not point to the IP address, and the connection is not successful.

You can disable MySQL from doing reverse DNS parsing in the configuration file by adding the following line to the [mysqld] section of my.cnf:

skip-name-resolve (same as windows and linux)

When the device connects to mysql, the banner information waiting for the server needs about 4s, which affects the connection speed of Mysql service.
Verification is conducted in the following ways:

1. Telnet port verification

Through the ports of the Telnet Mysql service respectively by the device and the virtual machine (Linux system), the following phenomena will occur:

Device (UAG/SCANNER) : after telnet, waiting for Mysql's server-side response will probably take about 10s.

[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) : immediately after telnet, the Mysql server returns

[root]~# 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? D % (; 1 $] +, ¢! Zdh ` '? // the time is very short here

2. Verify through the program

See the attachment for the specific source code: validation program source code
The source code basically sets the Recv timeout, after the socket connection is established, accepts the data, counts it and outputs it.

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

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

The virtual machine:

[root]tcp_demo# ./tcpclient 10.101.0.1 3306
Time spent: 10525
Recved 68 bytes
@
5.5.2-m2-communitd~k~Y";B

As you can see, it takes approximately 9s longer on the device than on the Linux server, and 10 seconds of this may be the timeout time for recv itself.

3. Verify Telnet through different operating systems

Through the Windows system and the Linux virtual machine and equipment, connection attempts were made through Telnet respectively. According to the packet capture analysis, only the equipment took a long time, while the rest took a short time.
When catching the packet, it was found that after the establishment of socket in the device, the MYSQL server had to send NBNS messages many times before transmitting the banner information. However, neither the Linux virtual machine nor the host of Windows system had this problem in the process.
I have found some information about the problem of MYSQL NBNS message:

Questions from Mysql forum:

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

The answer to the question
http://forums.mysql.com/read.php?11,250982,254683#msg-254683

According to the reply, it seems to be some version of the problem. The temporary solution is to configure the Mysql server without enabling Named Pipes, which is named pipes.

After looking up relevant information, we learned that the remote connection timeout may be due to the fact that Mysql turns on reverse parsing of DNS by default. The server tries to resolve the host name of the connecting client every time it connects, resulting in a relatively long time.

The solution is to configure an skip-name-resolve in the my.ini file on the server side under the section [mysqld] to turn off the reverse parsing of DNS that Mysql turns on by default.

Once again, Telnet is performed through the device and virtual machine or the Windows system, and the connection timeout is apparently no longer present.

In addition, the same problem also exists when connecting through the code C written by myself. After modifying skip-name-resolve, it can be found that this problem no longer exists:

Equipment:

[DPtech-Developer-Shell]/tcpclient_mips 10.101.0.1 3306
Time spent :10520
Recved 68 bytes
@
5.5.2-m2-community[Z44E > G)
The virtual machine:
[root]tcp_demo# ./tcpclient 10.101.0.1 3306
Time spent :10521
Recved 68 bytes
@
5.5.2-m2-community7evE5wyx

Through virtual machine Telnet connection 1 other ip 10.101.0.206 found speed is more slow, the consumption of time and fairly in equipment, basically can be caused by a virtual machine host and the host before don't need to do a reverse DNS, or shall be the system itself to know virtual machine IP address (NAT mode) the corresponding host name, so don't need to do a DNS reverse DNS, thus resulting in the special case in a virtual machine.
Finally, it comes to the conclusion that maybe this problem actually has nothing to do with the device or virtual machine, Linux system or Windows system, but is mainly caused by the reverse DNS parsing of the server. It cannot be resolved from the client side, which means our device cannot handle this situation.


Related articles: