Summary and sharing of skip_name_resolve parameters _mysql

As part of the MySQL tuning, many people recommend opening skip_name_resolve. This parameter prohibits domain name resolution (including, of course, hostname). Many children's shoes will be curious, what is the principle behind this, and when 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 message. is primarily used for non-local TCP connections.

1. By caching the mapping relationship between IP and host name when the connection is first established, subsequent connections to the same host view the host cache directly, without having to perform DNS resolution again.

2. The host cache will also contain error messages for IP logon failures. Based on this information, these IP restrictions can be limited. It will be specifically mentioned later.

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

So how is the mapping relationship between IP and host name established?

1. When a new client is connected, the MySQL server creates a new record for the IP in host cache, including IP, hostname, and client lookup validation flag, corresponding to the IP in the Host_cache table, respectively. Host and host_validated these three columns. The first time a connection is established because there is only IP and no host name, the host setting to Null,host_validated will be set to false.

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

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

Based on the above summary, here are the advantages and disadvantages of host cache:

Disadvantage: When a new client is connected, MySQL server will create a new record, if DNS resolution is slow, it will undoubtedly affect performance. If the host is allowed to access a lot of, 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 max_connections value dynamic adjustment. So if you are allowed to access a lot of hosts, based on the LRU algorithm, the previously established connection may be squeezed out, these hosts back in when the DNS query again.

Advantages: Typically, the hostname is invariant, and the IP is variable. If a client's IP is constantly changing, the ip-based authorization will be a tedious process. Because you can hardly determine when the IP changes. Based on the hostname, only one authorization is required. Moreover, based on the failure information in the host cache, it can prevent the external brute force from attacking the attack to some extent.

With regard to preventing external violence from cracking attacks, involving max_connect_errors parameters, the default is 100, the official explanation is as follows:

Copy Code code as follows:

If more than this many successive connection requests from a host are interrupted the without a successful connection Ver blocks that host from further connections. If a client's connection reaches the limit of max_connect_errors, it is blocked from accessing and prompts the following error:

Copy Code code as follows:

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

Now let's simulate

First, set the value of the Max_connect_errors

Mysql> Show variables like ' max_connect_errors ';
+--------------------+-------+
| Variable_name   | Value |
+--------------------+-------+
| max_connect_errors |
+--------------------+-------+
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   |
+--------------------+-------+
row in Set (0.00 sec)

Simulate interrupted without a successful connection by Telnet.

[root@mysql-slave1 ~]# telnet 192.168.244.145 3306
trying 192.168.244.145 ...
Connected to 192.168.244.145.
Escape character is ' ^] '.
N
5.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 3306
trying 192.168.244.145 ...
Connected to 192.168.244.145.
Escape character is ' ^] '.
N
Y#>PVB (>! Bl}nknjij]smmysql_native_password
^]
! #08S01Got packets out of the orderconnection closed by foreign host.
[Root@mysql-slave1 ~]# mysql-h192.168.244.145-uroot-p123456
warning:using a password on the command line INTERFAC E can be insecure.
ERROR 1129 (HY000): Host ' 192.168.244.144 ' is blocked because of many connection; Unblock with ' mysqladmin flush-hosts '

Even if you later use the correct account and password login, will still be blocked.

Then look at the information in the Host_cache table, 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 |
+-----------------+------+----------------+--------------------+-----------------------------+
row in set ( 0.00 sec)

The block remains 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 prevent DNS resolution, you can set the Skip_name_resolve parameter so that host-based authorization in the Mysql.user table is not available and the error log prompts:

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

Here, access through MYSQL-SLAVE1, Access will be denied

[Root@mysql-slave1 ~]# mysql-h192.168.244.145-uroot-p123
warning:using a password on the command line interface CA n be insecure.
ERROR 1045 (28000): Access denied for user ' root ' @ ' 192.168.244.144 ' (using Password:yes)

Host cache is opened by default, if you want to ban, you can set the Host_cache_size to 0, this parameter is a dynamic parameter, can be modified online.

If you want to completely ban 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 first time the client connection, the client data volume is relatively small, the cost is actually small, completely do not have to start skip_name_resolve parameters, the benefit is that for clients and a variety of IP direct decoupling, only the host name for a one-time authorization.

2. In the case of not open skip_name_resolve, whether it is through the # mysql-p123456 socket connection or # mysql-p123456-h127.0.0.1 walk TCP connection, the display of users are root@localhost. If you want to display root@127.0.0.1, you must open the Skip_name_resolve parameter.

Alternatively, you can see through \s whether the current connection uses sockets or TCP.

This article on the skip_name_resolve parameters of the summary of sharing is a small series to share all the content, hope to give you a reference, but also hope that we support the cloud-dwelling community.