A large number of unauthenticated users in MySQL

MySQL document: http://dev.mysql.com/doc/refman/5.0/en/dns.html

MySQL verifies the legality of a user by performing the following steps during user connection processing:

 

 1 When a new client connects to mysqld, mysqld spawns a new thread to handle the request. This thread first checks whether the host name is in the host name cache. If not, the thread attempts to resolve the host name: 
2 
3 The thread takes the IP address and resolves it to a host name (using gethostbyaddr()). It then takes that host name and resolves it back to the IP address (using gethostbyname()) and compares to ensure it is the original IP address. 

 

 

That is, first reverse resolution of IP> hostname, and then forward resolution of hostname> ip. If the results match, the authentication is valid. If the results do not match, the authentication is defined as "unauthenticated user ".

 

 1 eg: If your client machine is called 'foo.domain.com' with IP 1.2.3.4, you might set up grants on your remote MySQL server like this:
2 
3 grant select on dbname.* to [email]username@foo.domain.com[/email] identified by 'somepassword'
4 
5 But when you try to connect from foo.domain.com you find you get the error "Host '1.2.3.4' is not allowed to connect to this MySQL server"
6 
7 This is because a reverse lookup of 1.2.3.4 may in fact return a fully qualified domain name of 4-3-2-1.isp.otherdomain.com, ie something *other* than foo.domain.com.
8 
9 So stick to granting access by IP, not hostname, unless your forward and reverse lookups match.

 

 

 

Therefore, when creating user permissions, we recommend that you do not use the hostname as the limit but use the IP address directly. Simply put, the host does not configure the DNS server.
MySQL document: http://dev.mysql.com/doc/refman/5.0/en/dns.html

The manual explains unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done. Meaning: A thread is processing the client connection, but the client has not passed user verification.

Solutions:

1. Add the Client IP address in hosts, as shown in figure
   192.168.0.1 yejr
2. Add a skip-name-resolve parameter to the MySQL startup parameter, that is, do not enable DNS response resolution.
3. The server thread is still in the queue state, so you can increase the back_log

Back_log is described as follows in the manual:

The number of outstanding connection requests MySQL can have. this comes into play when the main MySQL thread gets very resume connection requests in a very short time. it then takes some time (although very little) for the main thread to check the connection and start a new thread. theback_logValue indicates how does requests can be stacked during this short time before MySQL momentarily stops answering new requests. you need to increase this only if you have CT a large number of connections in a short period of time.

In other words, this value is the size of the listen queue for incoming TCP/IP connections. your operating system has its own limit on the size of this queue. The manual page for the UNIXlisten()System Call shocould have more details. Check your OS documentation for the maximum value for this variable.back_logCannot be set higher than your operating system limit.

This indicates the maximum number of connection requests that can be stored in the MySQL connection queue.

This occurs when the MySQL main thread has a large number of connection requests in a short time. The main thread of MySQL takes a little time (although very short) to check the connection and create a new thread. Back_log indicates the maximum number of connection requests that can be stored in the MySQL connection queue. If you want to have a large number of connections in a short time, increase the value!

In other words, back_log is the size of the incoming TCP/IP connection listening queue. Your operating system has its own limits on this value. The UNIX listen () System Call help page provides a detailed introduction. Check your system documentation to see the limitations on this variable. The value of back_log cannot exceed the number of TCP/IP listeners in the operating system.