MySQL execution show processlist unauthenticated user solution

A unibilling machine suddenly loads heavier a few days ago. The CPU is found to be heavily occupied in top. The AGI program runs slowly, and a zombie process occurs. In fact, there were only about 50 concurrent calls. Far from the normal level. The problem of restarting the machine has not been solved. Very strange.

Taking into account that the mysqld occupies most of the resources in top, go into the MySQL console, run the show processlist command, and find that the information of a process is very special:

The code is as follows	Copy Code
User "unauthenticated user" Command "Connect"

is the state ' reading from net ' a hacker attacking my machine from the Internet? Obviously not, because the host column shows "localhost".

No way. A search on Google found that several people had encountered the problem. Nor is it a MySQL bug, as it can be resolved by changing the configuration file/ETC/MY.CNF.

Specifically, add Skip-name-resolve to the mysqld column

[Mysqld]

The code is as follows	Copy Code
Skip-name-resolve

Note that skip-name-resolve can disable DNS resolution, but this does not allow the host name to be used in the MySQL authorization table, only IP. The MySQL user was previously created if you are using localhost now you need to use 127.0 0.1来 instead of executing in the GRANT statement to add the user.

Then, restart the mysqld.

Other statements found on the Internet

Found that this is an official system on a special set, can also call him a MySQL bug, regardless of the way the link is through the hosts or IP mode, he will do the DNS back check. MySQL will try to reverse the ip->dns, because the reverse lookup resolution is too slow, unable to cope with the rapid and multiple queries.

The solution is very simple: When starting MySQL, add--skip-name-resolve option, prohibit domain name resolution, problem solving

code is as follows

copy code

MySQL > Show full processlist; Large number of unauthenticated user connection information found 13012 unauthenticated user x.x.x.x:2501 None Connect Reading from net 10056 unauthenticated user x.x.x.x:2805 None Connect Reading from net 10311 unauthenticated user x.x.x.x:2306 None Connect writing to net 10326  unauthenticated user X . x.x.x:2715 None Connect login 10422  unauthenticated user x.x.x.x:2811 None Connect login  < /p>

Check the information on the Internet, the main introduction of the solution is as follows:
1. In/etc/my.cnf by adding skip-name-resolve parameters, turn off MySQL DNS reverse query, MySQL use IP authorization

2. Add IP to host name in/etc/hosts and check/etc/resovle Primary DNS service.

3. Adjust MySQL max_connections,max_allowed_packet, wait_timeout,interactive_timeout parameters

It is confirmed that the above configuration most of the parameters of the production environment are already configured, there is a large number of unauthenticated user information that MySQL did not give these client connection request confirmation credentials, that is, MySQL can not confirm the use of these connections database account information, in the Wait_ Within timeout MySQL has been waiting for these connections to complete, by testing Telnet x.x.x.x 3306 observation, in the MySQL database will also appear a unauthenticated user x.x.x.x:4706 None connect Rea Ding from net connection, so this behavior is not necessarily a database problem, which is likely to occur

1. If there is a large number of database probes using security issues, there is a large number of such unauthorized connections

2. Application service pressure too large occurrence of thread abnormal interrupt cause a large number of abnormal database connections

3. Application service exception, resulting in a large number of exception database connections

4. MySQL Client Connection version problem, verify the protocol is incompatible, pay special attention to Old-password authentication method

Finally through the voting system to join the verification code to prevent the brush ticket, reduce the possibility of illegal and unusual connection, restart the PHP service after the CPU load back to normal, MySQL database unauthenticated user connection also did not appear.