今天数据库出现异常,大量unauthenticated user的链接,经过一番排查,加上谷歌出来的一些资料,感觉下面这个大神写的这篇文章还不错,就转载给大家。
一 现象
在生产环境中我们偶尔会遇到 "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 |
+-----+----------------------+--------------------+------+---------+------+-------+------------------+
官方介绍: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。
“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.”
ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: [YES/NO])