MySQL execution show processlist unauthenticated user workaround

A unibilling machine suddenly had a heavy load in the first few days. The CPU is found to be heavily occupied in top. The AGI program runs very slowly, and a zombie process occurs. In fact, there were only about 50 concurrent calls. Far from the normal level. Restarting the machine problem is not resolved. Very strange.

Given that the most resources are seen in top mysqld, go to the MySQL console and run the show processlist command to discover that the information for a process is special:

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

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

No way. A search on Google found that a number of people have encountered the problem. It is not a bug for MySQL because it can be resolved by changing the configuration file/ETC/MY.CNF.

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

[Mysqld]

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

Note that skip-name-resolve can disable DNS resolution, however, so that the hostname cannot be used in the MySQL authorization table, only IP is used. The previous MySQL user was created using localhost now requires 127.0.0.1来 instead of executing in the GRANT statement to add the user.

Then, restart Mysqld.

Other statements found on the Internet

Found that this is an official system on the special settings, 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 counter-check. MySQL will try to counter-check Ip->dns, because the anti-check parsing too slow, unable to cope with a fast multi-volume query.

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

copy code

MySQL >show full processlist; Found a large number of unauthenticated user connection information 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

Access to online information, the main solutions are as follows:
1. In/etc/my.cnf by adding the Skip-name-resolve parameter, turn off MySQL DNS counter-query, MySQL uses IP authorization

2. In/etc/hosts add IP and hostname correspondence, check/ETC/RESOVLE Primary DNS service.

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

Confirmed that the above configuration of most of the parameters of the production environment is already configured, a large number of unauthenticated user information indicates that MySQL did not give these client connection request confirmation credentials, that is, MySQL can not confirm the connection using the database account information, in Wait_ Within timeout MySQL has been waiting for these connections to complete, and by testing telnet x.x.x.x 3306, a unauthenticated user x.x.x.x:4706 None connect Rea will appear in the MySQL database Ding from net connection, so this phenomenon is not necessarily a database problem, the following are likely to produce this phenomenon

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

2. Application service pressure is too high a thread exception interrupt causes a large number of exception database connections

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

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

Finally through the voting system to join the verification code to prevent the swipe ticket, reduce the possibility of illegal and abnormal connection, restart the PHP service after the CPU load back to normal, MySQL database unauthenticated user connection no longer appear.

Http://www.111cn.net/database/mysql/42102.htm