Summary of skip_name_resolve parameters, skip_name_resolve

Summary of skip_name_resolve parameters, skip_name_resolve

As part of MySQL optimization, many people recommend enabling skip_name_resolve. This parameter prohibits domain name resolution (including the host name, of course ). Many children's shoes may wonder what the principle behind this is and when this parameter is enabled.

 

For the following reasons, the MySQL server maintains host information in the memory, which includes three parts: IP address, host name, and error information. It is mainly used for non-local TCP connections.

1. By ing between the cache IP address and host name when a connection is established for the first time, the host cache will be directly viewed for subsequent connections to the same host, instead of performing DNS resolution again.

2. The host cache also contains an error message about failed IP address logon. You can restrict these IP addresses based on the information. It will be mentioned later.

The host cache information can be viewed in the host_cache table in performance_schema.

 

How is the ing between IP addresses and host names established?

1. when a new client is connected, MySQL Server creates a new record for the IP address in the host cache, including the IP address, host name, and client lookup validation flag, the three columns respectively correspond to the IP address, HOST, and HOST_VALIDATED in the host_cache table. The first connection is established because there is only an IP address and no HOST name. Therefore, HOST is set to NULL and HOST_VALIDATED is set to FALSE.

2. mySQL Server checks the value of HOST_VALIDATED. If it is FALSE, it tries to perform DNS resolution. If the resolution is successful, it updates the HOST value to the HOST Name and sets the value of HOST_VALIDATED to TRUE. If the resolution fails, the cause of the failure is permanent or temporary. If it is permanent, the HOST value is still NULL and the HOST_VALIDATED value is set to TRUE, subsequent connections will not be resolved. If this is temporary, HOST_VALIDATED will remain FALSE, and subsequent connections will be resolved again.

 

In addition, the successfully resolved host name is obtained not only by IP address, but also by reverse resolution of the resolved host name to IP address, check whether the IP address is the same as the original IP address. If the IP address is the same, it is determined that the resolution is successful before the host cache information can be updated.

 

Based on the above summary, the following describes the advantages and disadvantages of host cache:

Disadvantage: when a new client is connected, MySQL Server must create a new record. If DNS resolution is slow, it will undoubtedly affect the performance. If many hosts are allowed to access, performance will also be affected. This is related to host_cache_size. this parameter is introduced in 5.6.5. Before 5.6.8, the default value is 128, and after 5.6.8, the default value is-1. The value is dynamically adjusted based on max_connections. Therefore, if many hosts are allowed to access, the previously established connections may be squeezed out based on the LRU algorithm. When these hosts come in again, DNS queries will be performed again.

Advantage: Generally, the host name remains unchanged, while the IP address changes. If the IP address of a client changes frequently, IP-based authorization is a tedious process. It is difficult to determine when the IP address changes. Based on the host name, only one authorization is required. Moreover, based on the failure information in the host cache, it can prevent external brute-force cracking attacks to a certain extent.

 

The max_connect_errors parameter is used to prevent external brute-force cracking attacks. The default value is 100. The official explanation is as follows:

If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections.

If the connection of a client reaches the limit of max_connect_errors, access is forbidden and the following error is prompted:

Host 'host_name' is blocked because of many connection errors.Unblock with 'mysqladmin flush-hosts'

 

The following is a simulation.

First, set the value of max_connect_errors.

mysql> show variables like 'max_connect_errors';+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | 100   |+--------------------+-------+1 row in set (0.00 sec)mysql> set global max_connect_errors=2;Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'max_connect_errors';+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| max_connect_errors | 2     |+--------------------+-------+1 row in set (0.00 sec)

Use telnet to simulate interrupted without a successful connection.

[root@mysql-slave1 ~]# telnet 192.168.244.145 3306Trying 192.168.244.145...Connected to 192.168.244.145.Escape character is '^]'.N5.6.26-log          K]qA1nYT!w|+ZhxF1c#|kmysql_native_password^]!#08S01Got packets out of orderConnection closed by foreign host.[root@mysql-slave1 ~]# telnet 192.168.244.145 3306Trying 192.168.244.145...Connected to 192.168.244.145.Escape character is '^]'.NY#>PVB(>!Bl}NKnjIj]sMmysql_native_password^]!#08S01Got packets out of orderConnection closed by foreign host.[root@mysql-slave1 ~]# mysql -h192.168.244.145 -uroot -p123456Warning: Using a password on the command line interface can be insecure.ERROR 1129 (HY000): Host '192.168.244.144' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Even if you use the correct account and password to log on, it will still be blocked.

Let's take a look at the information in the host_cache table. The value of sum_connect_errors is 2.

mysql> select ip,host,host_validated,sum_connect_errors,count_authentication_errors from performance_schema.host_cache;+-----------------+------+----------------+--------------------+-----------------------------+| ip              | host | host_validated | sum_connect_errors | count_authentication_errors |+-----------------+------+----------------+--------------------+-----------------------------+| 192.168.244.144 | NULL | YES            |                  2 |                           0 |+-----------------+------+----------------+--------------------+-----------------------------+1 row in set (0.00 sec)

 

The blocking will take effect until you take the following actions:

1. mysql> flush hosts;

2. # mysqladmin flush-hosts

3. truncate table performance_schema.host_cache;

4. You can also wait for the record to be squeezed out from the host cache.

 

If you want to disable DNS resolution, you can set the skip_name_resolve parameter. In this way, the host name-based authorization in the mysql. user table will not be available and the error log will prompt:

[Warning] 'user' entry 'root@mysql-slave1' ignored in --skip-name-resolve mode.

Here, access through mysql-slave1 will be denied

[root@mysql-slave1 ~]# mysql -h192.168.244.145 -uroot -p123Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'192.168.244.144' (using password: YES)

 

Host cache is enabled by default. To disable it, set host_cache_size to 0. This parameter is a dynamic parameter and can be modified online.

 

If you want to completely disable TCP/IP connections, you can set the skip-networking parameter when starting MySQL.

 

Summary:

1. in principle, DNS resolution is generally only for the first connection from the client. When the client data volume is small, the overhead is actually not large, and you do not have to start the skip_name_resolve parameter. The benefit is that, directly decouples the client from the changeable IP address. You only need to authorize the host name once.

2. If skip_name_resolve is not enabled, whether it is through a socket connection through # mysql-p123456 or a TCP connection through # mysql-p123456-h127.0.0.1, the displayed users are all root @ localhost. To display the root@127.0.0.1, The skip_name_resolve parameter must be enabled.

In addition, you can view whether the current connection uses socket or TCP through \ s.

 

Refer:

1. http://www.tuicool.com/articles/7R7BRb

2. http://dev.mysql.com/doc/refman/5.7/en/host-cache.html

3. http://dev.mysql.com/doc/refman/5.7/en/blocked-host.html