A summary of the skip_name_resolve parameters

As part of MySQL tuning, many people recommend opening skip_name_resolve. This parameter is not allowed to resolve the domain name (including, of course, the host name). Many children's shoes will be curious, the rationale behind this is what, under what circumstances to open this parameter is more appropriate.

For the following reasons, the MySQL server maintains a host message in memory, including three parts: IP, hostname, and error information. Used primarily for non-local TCP connections.

1. By caching the mappings between the IP and host name when the connection is first established, subsequent connections to the same host will view the host cache directly without DNS resolution.

2. The host cache also contains error messages for IP logon failures. Depending on this information, the IP can be restricted accordingly. It will be mentioned in detail later.

The host cache information can be viewed through the Host_cache table in Performance_schema.

So, how is the mapping of IP and host name established?

1. When a new client connection comes in, MySQL server establishes a new record for the IP in the host cache, including IP, hostname and client lookup validation flag, corresponding to the IP in the Host_cache table, respectively. Host and host_validated are the three columns. The first connection is established because only IP, no host name, so host will be set to null,host_validated set to false.

2. MySQL server detects the value of host_validated, if false, it tries to resolve the DNS, and if the resolution succeeds, it updates the value of host to the hostname and sets the host_validated value to true. If there is no successful resolution, the reason for the failure is permanent or temporary, if it is permanent, then the value of host is still null, and the value of host_validated is set to true, subsequent connections are no longer resolved, and if the cause is temporary, host_ Validated is still false, and subsequent connections will be DNS resolved again.

Another, the resolution of the success of the flag is not just through the IP, get to the hostname, this is just one step, there is a step through the parsed host name to reverse the resolution to the IP, to determine whether the IP is the same as the original IP, if the same, only to determine the success of the resolution to update the host cache information.

Based on the above summary, here are the pros and cons of host cache:

Cons: When a new client connection comes in, MySQL server creates a new record, and if DNS resolution is slow, it will undoubtedly affect performance. If the host is allowed to access a lot, it will also affect performance, this is related to Host_cache_size, this parameter is 5.6.5 introduced. 5.6.8 before default is 128,5.6.8 after default is-1, based on the value of max_connections dynamic adjustment. So if the host is allowed to access many, based on the LRU algorithm, the previously established connection may be squeezed out, these hosts will re-enter the DNS query again.

Pros: Typically, the hostname is constant, and the IP is changeable. If a client's IP is constantly changing, then IP-based authorization will be a tedious process. Because it's hard to know when the IP will change. Based on the host name, only one authorization is required. Moreover, based on the failure information in the host cache, the external brute force attack can be prevented to a certain extent.

Regarding the blocking of external brute force attacks, involving the max_connect_errors parameter, the default is 100, the official explanation is as follows:

 More than the many successive connection requests from a host is interrupted without a successful connection, t The He server blocks this host from further connections.

If a client's connection reaches the max_connect_errors limit, access is blocked and the following error is indicated:

' host_name '  'mysqladmin flush-hosts'

Here's a mock-up.

First, set the value of the Max_connect_errors

Mysql>Show variables like 'max_connect_errors';+--------------------+-------+|Variable_name|Value|+--------------------+-------+|Max_connect_errors|  -   |+--------------------+-------+1Rowinch Set(0.00sec) MySQL> SetGlobal Max_connect_errors=2; Query OK,0Rows Affected (0.00sec) MySQL>Show variables like 'max_connect_errors';+--------------------+-------+|Variable_name|Value|+--------------------+-------+|Max_connect_errors| 2     |+--------------------+-------+1Rowinch Set(0.00Sec

Simulates interrupted without a successful connection via Telnet.

[[Email protected] ~]# Telnet192.168.244.145 3306Trying192.168.244.145... Connected to192.168.244.145. Escape character is'^]'. N5.6. --Log K]qa1nyt!W|+zhxf1c#|Kmysql_native_password^]!#08S01Got packets out of orderconnection closed by foreign host. [[Email protected]-slave1 ~]# Telnet192.168.244.145 3306Trying192.168.244.145... Connected to192.168.244.145. Escape character is'^]'. ny#>PVB (>!Bl} Nknjij]smmysql_native_password^]!#08S01Got packets out of orderconnection closed by foreign host. [[Email protected]-slave1 ~]# mysql-h192.168.244.145-uroot-p123456warning:using a password on the command line interface can is insecure. ERROR1129(HY000): Host'192.168.244.144'is blocked because of many connection errors; Unblock with'mysqladmin flush-hosts'

Even if you later log in with the correct account and password, you will still be blocked.

Then look at the information in the Host_cache table, Sum_connect_errors is 2.

Mysql> SelectIp,host,host_validated,sum_connect_errors,count_authentication_errors fromPerformance_schema.host_cache;+-----------------+------+----------------+--------------------+-----------------------------+|Ip|Host|host_validated|Sum_connect_errors|Count_authentication_errors|+-----------------+------+----------------+--------------------+-----------------------------+| 192.168.244.144 | NULL |YES|                  2 |                           0 |+-----------------+------+----------------+--------------------+-----------------------------+1Rowinch Set(0.00Sec

The block will remain in effect until the following actions are taken:

1. mysql> Flush hosts;

2. # mysqladmin Flush-hosts

3. truncate TABLE Performance_schema.host_cache;

4. Or wait for the record to be squeezed out of the host cache.

If you want to disable DNS resolution, you can set the Skip_name_resolve parameter so that the hostname-based authorization in the Mysql.user table will not be available, and the error log will prompt:

[Warning] ' User ' ' [email protected] ' inch -- skip-name-resolve mode.

Here, access via MYSQL-SLAVE1 will deny access to

[[email protected] ~]# MySQL-h192.168.244.145 -Uroot-p123warning:using a password onThe command line interface can insecure. ERROR1045(28000): Access denied for User 'Root'@'192.168.244.144'(using Password:yes)

The host cache is turned on by default, and if you want to disable it, you can set Host_cache_size to 0, which is a dynamic parameter that can be modified online.

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

Summarize:

1. From the principle, the DNS resolution is generally only for the client's first connection, the client data volume is small, the cost is not very small, do not have to start the Skip_name_resolve parameter, the benefit is that for the client and the changeable IP directly decoupling, only need to authorize the host name.

2. When the skip_name_resolve is not turned on, whether it is through the # mysql-p123456 socket connection or # mysql-p123456-h127.0.0.1 Go TCP connection, the display of the user is [email Protected]. If you want to display [email protected], you must turn on the Skip_name_resolve parameter.

Alternatively, you can view the current connection using a socket or TCP via \s.

A summary of the skip_name_resolve parameters