【MySQL】关于 unauthenticated user的哲学思考

安宇雨 - 随手采集
2019-09-26 16:42:19
随手采集
0000-未整理-等待研究

一、现象

在生产环境中我们偶尔会遇到show processlist；显示host为 unauthenticated user 这样的连接，同时伴有数据库服务器层面的load，sys cpu较高，或者thread running异常。

二、分析

类似于校园门卫看到一个陌生的人进入学校，对TA提出的哲学式问题：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步:

客户端发送数据包到MySQL服务器，准备建立连接。如果MySQL服务器对应的端口没有运行,则客户端会直接收到报错信息：
ERROR 2003 (HY000): Can't connect to MySQL server on '[host]' (111)
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"。
客户端发送username/password/要访问的dbname到MySQ数据库服务器。如果客户端由于某些原因在connect_timeout规定的时间内没有发送包或者发送错误的包，数据库服务器会断开该连接。
服务器验证并返回验证结果给客户端。如果验证不通过则通常返回：
ERROR 1045 (28000): Access denied for user 'user'@'host' (using password: [YES/NO])

ok，至此，我们可以知道TA来自客户端和MySQL服务器建立连接的第二阶段过程中。

要做什么？

显然此类连接是要访问数据库，准备获取数据或者写入数据。但是我们如何规避这样的三无人员的出现呢？从该问题产生的原因来分析,主要的解决方法如下：

在 /etc/my.cnf 的[mysqld]中添加
skip-name-resolve
参数，关闭mysql的dns反查询，mysql使用IP或者%授权。
在 /etc/hosts 添加IP与主机名对应关系
192.168.0.1 xxxx

值得注意的是在我们生产环境中已经为MySQL配置了skip-name-resolve，依然会出现大量unauthenticated user 的连接时，表明MySQL服务器没有为客户端连接请求确认凭证，也就是说MySQL无法确认这些连接使用的数据库账号信息，在wait_timeout时间之内MySQL一直等待这些连接完成。比如我在某机器上执行