MySQL「未驗證用戶」大量堆積：DNS 反向解析導致阻塞的診斷與解決

現象：show processlist 顯示大量「unauthenticated user」

Id    User                Host                 db   Command   Time   State   Info
3436942 unauthenticated user 192.168.0.4:49607      Connect   0      login   NULL
3436943 unauthenticated user 192.168.0.4:49608      Connect   0      login   NULL
...

連線處於 login 狀態長時間不進入認證階段，佔用連線槽位，導致 max_connections 耗盡，正常業務無法連線。

根因：DNS 反向解析卡住

MySQL 啟動時預設啟用 主機名解析：每個新連線，Server 會對 Client IP 執行 反向 DNS 查詢（PTR 記錄） 取得主機名，用於：

• mysql.user 表 Host 欄位匹配（支援主機名通配符）
• show processlist 顯示 Host 欄位
• 寫入一般查詢日誌/慢查詢日誌的主機名

若 DNS 伺服器無回應、超時、或 PTR 記錄不存在，Server 會阻塞等待（預設無超時上限），連線堆積在 login 狀態。

驗證方法

# 在 MySQL Server 上測試反向解析
dig -x 192.168.0.4 +time=2 +tries=1
# 或
nslookup 192.168.0.4
# 若超時或無 PTR 記錄，即為元兇

三種解決方案（按推薦順序）

方案一：/etc/hosts 靜態映射（最穩健、不改架構）

在 MySQL Server 的 /etc/hosts 加入 Client IP 對應主機名：

192.168.0.4   app-server-01
192.168.0.5   app-server-02
# 批次腳本：
for ip in 192.168.0.{4..50}; do echo "$ip  host-$(echo $ip | cut -d. -f4)" >> /etc/hosts; done

優點： 即時生效、無需重啟 MySQL、不影響權限表 Host 欄位（仍可用 % 或 IP）、無安全風險。
缺點： Client IP 變更需同步維護 hosts。

方案二：停用 DNS 反向解析 skip-name-resolve

修改 my.cnf：

[mysqld]
skip-name-resolve

重啟 MySQL 生效。
副作用（必讀）：

• mysql.user 表 Host 欄位不再支援主機名，只能用 IP、%、localhost（Unix Socket）。
• GRANT ... TO 'user'@'hostname' 將報錯/不生效。
• show processlist Host 欄位僅顯示 IP。
• 若現有權限表含主機名（如 'user'@'app-server'），啟用後該用戶將無法登入，需預先改為 IP 或 %。

適用場景： 內網環境、權限表已全為 IP/%、追求極致連線效能。

方案三：調整系統 DNS 超時（治標）

修改 /etc/resolv.conf：

options timeout:1 attempts:1 rotate

或啟用 nscd（Name Service Cache Daemon）快取 DNS 結果：

systemctl enable nscd --now

優點： 不改 MySQL 配置、保留主機名支援。
缺點： 仍依賴 DNS 伺服器可用性、快取可能殘留舊記錄。

進階：連線階段超時參數（5.7+）

[mysqld]
connect_timeout = 10          # 等待握手封包秒數（預設 10）
# 8.0 新增：
authentication_ldap_sasl_server_host = ...
# 無直接「DNS 解析超時」參數，故根治仍靠上述三方案

監控與告警建議

# 監控 unauthenticated user 數量
mysql -e "SHOW PROCESSLIST" | grep -c "unauthenticated user"
# 閾值 > 50 觸發告警，排查 DNS/網路/應用連線洩漏

總結

大量 unauthenticated user 90% 由 DNS 反向解析阻塞引起。 推薦優先採用 /etc/hosts 靜態映射，零風險、即時生效、保留主機名權限。若環境純 IP 授權，可開啟 skip-name-resolve 徹底根治並提升連線效能。務必建立監控，防患未然。