一現象
在生產環境中我們偶爾會遇到 "unauthenticated user" 這樣的連線,同時伴有資料庫伺服器層面的load,sys cpu較高,或者thread running異常。

+-----+----------------------+--------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+----------------------+--------------------+------+---------+------+-------+------------------+
| 235 | unauthenticated user | 10.10.2.74:53216 | NULL | Connect | NULL | login | NULL |
| 236 | unauthenticated user | 10.120.61.10:51721 | NULL | Connect | NULL | login | NULL |
| 237 | user | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+----------------------+--------------------+------+---------+------+-------+------------------+

二分析
類似於校園門衛看到一個陌生的人進入時的提出的著名問題,大家會進入哲學模式 :TA是誰? 從哪裡來? 要幹什麼？本文從IT技術角度回答這個哲學問題
TA是誰？

官方介紹: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。

意即:MySQL有一個執行緒在處理客戶端的連線,但是該客戶端還沒透過使用者驗證,show processlist時顯示"unauthenticated user".
從哪裡來？
回答這個問題之前，我們先了解client端和MySQL建立TCP連線(socket不在討論範圍之內)的過程，一般客戶端建立與MySQL的連線分4步:
1. 客戶端傳送資料包到MySQL伺服器,準備建立連線。如果MySQL伺服器對應的埠沒有執行的例項會直接返回報錯:
ERROR 2003 (HY000): Can't connect to MySQL server on '[host]' (111)
2. MySQL伺服器向客戶端響應基本資訊資料庫伺服器的ip,port,mysqld version ,the thread id,客戶端的host , port等等,此時連線已經建立但是尚未完成授權，

“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:
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.”

實際連線過程 mysql 分配一個新的執行緒來處理客戶端的連線請求。先檢查客戶端的hostname 是否在快取中，如果不在則對hostname解析解析.先作反向解析客戶端IP --->客戶端的hostname,然後作客戶端的hostname --->客戶端IP的正向解析。如果結果符合，則驗證為合法使用者允許登入，如果不符合則定義為"unauthenticated user"。
3. 客戶端傳送username/password/要訪問的dbname到MySQ資料庫伺服器。如果客戶端由於某些原因在connect_timeout規定的時間內沒有傳送的包或者傳送錯誤的包，資料庫伺服器會斷開該連線。
4. 伺服器驗證並返回驗證結果給客戶端。如果驗證不透過則通常返回:

ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: [YES/NO])

ok,至此，我們可以知道TA來自客戶端和MySQL伺服器建立連線的第二階段過程中。
要做什麼？
顯然準備訪問資料庫啊，獲取資料或者寫入資料。
如何規避這樣的三無人員的出現呢？從產生該問題的原因來分析,主要介紹解決方法如下：
a 在 /etc/my.cnf 的[mysqld]中新增 skip-name-resolve 引數，關閉mysql的dns反查詢，mysql使用IP或者%授權
b 在 /etc/hosts 新增IP與主機名對應關係
192.168.0.1 xxxx
然而在我們生產環境中,已經配置了skip-name-resolve,依然會出現大量unauthenticated user 資訊時表明MySQL伺服器沒有為客戶端連線請求確認憑證，也就是說MySQL無法確認這些連線使用的資料庫賬號資訊，在wait_timeout時間之內MySQL一直等待這些連線完成。
比如我在某機器上執行

目標機器上 MySQL中執行 show processlist 顯示為unauthenticated user

因此這種現象不一定就是資料庫本身的問題 ,下面這些都有可能產生這種現象
1.如果應用安全問題出現大量資料庫探測,出現大量這種未經授權的連線。
2.應用服務壓力過大出現執行緒異常中斷導致出現大量異常資料庫連線。
3.應用服務異常，導致出現大量異常資料庫連線。
4.MySQL客戶端連線版本問題,驗證協議不相容,尤其注意old-password驗證方式。
5.資料庫伺服器的執行緒處於排隊狀態,因此可以加大back_log，增加MySQL處理連線請求的能力。
前三個要從應用伺服器端檢視出現大量 unauthenticated user 的時候系統的負載或者應用程式的狀態，後面兩個要從資料庫伺服器層面來檢查系統的狀態。

三參考
[1] 《Troubleshooting Problems Connecting to MySQL》
[2] 《what is an unauthenticated user》
[3] back_log 官方說明
[4] https://dev.mysql.com/doc/refman/5.6/en/connection-threads.html
[5] https://dev.mysql.com/doc/refman/5.6/en/host-cache.html

如果您覺得從這篇文章受益，可以微信支付 北在南方 一瓶飲料 ^_^

【MySQL】關於 unauthenticated user的哲學思考

相關文章