The problem background

Application newspaper MySQL Database cannot connect , After investigation, it is found that the number of connections is full . adopt max_connections Parameter to increase the upper limit of connections to 1000 after , The database began to report a lot unauthenticated user error .

: 343942 : unauthenticated user : 192.168.0.4:49607 : : Connect : : login :
: 343943 : unauthenticated user : 192.168.0.4:49608 : : Connect : : login :
...
...

Problem analysis

I checked it on the Internet , It may be because the database is not configured skip-name-resolve Parameters ,MySQL The host name will be resolved when verifying the application connection , If there are too many connections and they are not released in time , It will lead to MySQL“ Feign death ”, Thus the above situation appears .

MySQL The official response to skip-name-resolve The parameters are described as follows ：

Whether to resolve host names when checking client connections. If this variable is OFF, mysqld resolves host names when checking client connections. If it is ON, mysqld uses only IP numbers; in this case, all Host column values in the grant tables must be IP addresses.

namely , When this parameter is turned off ,MySQL The host name will be resolved when checking the client connection ; When this parameter is turned on ,MySQL Only check IP.

Checked the problem MySQL Profile of the database , There is really no skip-name-resolve This business . You can also execute the following statement to query , See if the value is OFF：

show variables like 'skip_name_resolve';

MySQL When the official application connects to the database DNS The parsing process is described as follows ：

For each applicable new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If so, the server refuses or continues to process the connection request depending on whether or not the host is blocked. If the host is not in the cache, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache. If the cache is full, the least recently used entry is discarded.

namely , When the database verifies a new application connection , Will be will be IP Address resolved to hostname , Then resolve the host name to IP, And original IP compare . If DNS Server problem , It is also possible to report unauthenticated user Error of .

Problem handling

Method 1
stay MySQL Add the following line to the configuration file , Then restart the database .

skip-name-resolve

The advantage is that it can fundamentally solve the problem ; The disadvantage is that when authorizing users in the future host Only use IP Address , Cannot use hostname .

Method 2
Manually configure... For each application connection hosts analysis . such as Linux Operating system , Can be in /etc/hosts Add corresponding host Record . The disadvantage is that it is not applicable to the case where there are too many application connections .

192.168.32.33  appserver01
192.168.32.34  appserver02

References：
【1】https://dev.mysql.com/doc/refman/5.7/en/host-cache.html
【2】https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html