Shulou(Shulou.com)05/31 Report--

This article mainly introduces that the remote connection mysql in mysql is very slow, and how to deal with the local connection mysql very quickly. What is introduced in this article is very detailed and has certain reference value. Interested friends must finish reading it!

Symptom: remote connection to mysql is slow, but local connection to mysql is fast, and network communication between ping and route is normal.

Solution: the following configuration parameters are added to the configuration file of mysql:

[mysqld]

Skip-name-resolve

Principle:

As part of MySQL tuning, many people recommend turning on skip_name_resolve. This parameter forbids domain name resolution (including hostname, of course). Many children's shoes will wonder what is the principle behind this, and under what circumstances it is more appropriate to open this parameter.

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

1. By caching the mapping between IP and host name the first time the connection is established, subsequent connections to the same host will view the host cache directly without having to parse the DNS again.

2. The host cache also contains the error message of IP login failure. Based on this information, these IP can be restricted accordingly. It will be mentioned in detail later.

The information for host cache 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 connects in, MySQL Server creates a new record in host cache for the IP, including IP, hostname, and client lookup validation flag, corresponding to the IP,HOST and HOST_VALIDATED columns in the host_ cache table. The first time the connection is established, because there is only IP and no hostname, HOST will be set to NULL,HOST_VALIDATED and FALSE will be set.

2. MySQL Server detects the value of HOST_VALIDATED. If it is FALSE, it will attempt to parse DNS. If the parsing is successful, it will update the value of HOST to hostname and set the value of HOST_VALIDATED to TRUE. If the resolution is not successful, determine whether the cause of the failure is permanent or temporary. If it is permanent, the value of HOST is still NULL, and the value of HOST_VALIDATED is set to TRUE. Subsequent connections will no longer be resolved. If the reason is temporary, HOST_VALIDATED will still be FALSE, and subsequent connections will resolve DNS again.

In addition, the flag of successful resolution is not just to get the host name through IP, which is just one of the steps. Another step is to reverse parse the host name to IP to determine whether the IP is the same as the original IP. If it is the same, it is judged that the resolution is successful and the information in the host cache can be updated.

Based on the summary above, let's talk about the advantages and disadvantages of host cache:

Cons: when a new client connects, MySQL Server creates a new record, and if DNS parsing is slow, it will undoubtedly affect performance. If a large number of hosts are allowed to access, it will also affect performance, which is related to host_cache_size, and this parameter was introduced in 5.6.5. Before 5.6.8, the default is 128 Person.After 5.6.8, the default is-1, which is adjusted dynamically based on max_connections values. So if a large number of hosts are allowed to access, based on the LRU algorithm, previously established connections may be squeezed out, and when these hosts re-enter, they will conduct a DNS query again.

Pros: in general, the hostname is the same, while IP is changeable. If a client's IP changes frequently, IP-based authorization can be a tedious process. Because it's hard to know when IP will change. Based on the hostname, only one authorization is required. Moreover, based on the failure information in host cache, brute force cracking attacks can be prevented to some extent.

For preventing brute force cracking attacks from outside, the max_connect_errors parameter is involved. The default 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 a client's connection reaches the max_connect_errors limit, access will be disabled with the following error:

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

Let's simulate it.

First, set the value of max_connect_errors

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

Let's take a look at the information in the host_ cache table. Sum_connect_errors is 2.

If this parameter is set to OFF, the above method will report an error, which can be seen from the error message that it directly converts 127.0.0.1 to localhost.

[root@localhost] # mysql-uroot-h227.0.0.1-p123456-P3306Warning: Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Note: the password defined in 't1roomroomquota%' can be used for login of 't1roomroomroomroom127.0.0.1' if the skip_name_resolve parameter is enabled. If this parameter is not enabled, the password defined in' t1roomroomroomlocalhost` will be converted to'T1 roomroomroomlocalhost` login, and the password defined in 't1roomroomalarm%' is not applicable at this time.

The above is all the contents of this article entitled "remote connection mysql is very slow in mysql, how to deal with Local connection mysql very soon". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.