转载

【MySQL】关于 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 |
+-----+----------------------+--------------------+------+---------+------+-------+------------------+

二分析
类似于校园门卫看到一个陌生的人进入时的提出的著名问题,大家会进入哲学模式 :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】关于 unauthenticated user的哲学思考

目标机器上 MySQL中执行 show processlist 显示为unauthenticated user
【MySQL】关于 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的哲学思考