Mysql 大量 unauthenticated user

MySQL的文檔：http://dev.mysql.com/doc/refman/5.0/en/dns.html

MySQL處理使用者串連時進行如下過程來驗證使用者的合法性：

 

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. 

 

 

即：先作反向解析IP>Hostname,然後作Hostname>IP的正向解析。如果結果符合，則驗證為合法使用者允許登入，如果不符合則定義為“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.

 

 

 

所以建議建立使用者權限的時候，不要使用hostname作為限制而是直接用IP；更乾脆的話就主機不配置DNS Server。
MySQL的文檔：http://dev.mysql.com/doc/refman/5.0/en/dns.html

手冊中的解釋是：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。意即：有一個線程在處理用戶端的串連，但是該用戶端還沒通過使用者驗證。

解決辦法有：

1. 在 hosts 中添加用戶端ip，如
   192.168.0.1  yejr
2. MySQL啟動參數增加一個skip-name-resolve，即不啟用DNS反響解析
3. 伺服器的線程還處於排隊狀態，因此可以加大 back_log

back_log在手冊中是這樣描述的：

The number of outstanding connection requests MySQL can have. This comes into play when the main MySQL thread gets very many 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. The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily stops answering new requests. You need to increase this only if you expect 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 Unix listen() system call should have more details. Check your OS documentation for the maximum value for this variable. back_log cannot be set higher than your operating system limit.

意思是:back_log意味著mysql串連隊列中允許存放的最大串連請求數。

　　這種情況發生在mysql的主線程在短時間內有大量串連請求。mysql的主線程需要花費一點時間（雖然很短）來檢查串連並且建立一個新的線程。back_log意味著mysql串連隊列中允許存放的最大串連請求數。如果你想在短時間內有大量串連的話，那就增大這個值吧！

　　換句話說，back_log就是傳入tcp/ip串連監聽隊列的大小。你的作業系統有它自己的對這個值的限制。Unix的listen()系統調用的協助頁上有詳細的介紹。檢查你的系統文檔看看對這個變數的限制。back_log的值不能超過作業系統中對tcp/ip監聽數的限制。