解决MySQL”unauthenticated user“问题
背景
今天公司服务器MySQL连接异常慢，查看各服务器状态，发现CPU负载和内存使用情况均处于正常状态，再看nginx日志和网站日志，无可疑情况。于是登录MySQL，输入SQL语句查询正常，遂输入show processlist查看MySQL当前的连接是否有慢SQL，结果发现有很多这样的连接：
mysql> show full processlist \G
*************************** 1. row ***************************
Id: 64
User: tiku
Host: *.*.*.42:35630
db: newtiku
Command: Sleep
Time: 1
State:
Info: NULL
*************************** 2. row ***************************
Id: 74
User: tiku
Host: *.*.*.42:35880
db: newtiku
Command: Sleep
Time: 1
State:
Info: NULL
*************************** 3. row ***************************
Id: 1410
User: tiku
Host: *.*.*.42:57996
db: newtiku
Command: Sleep
Time: 392
State:
Info: NULL原因分析
通过查询官方文档The host_cache Table如下：
...
For each applicable new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If so, the server refuses or continues to process the connection request depending on whether or not the host is blocked. If the host is not in the cache, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache. If the cache is full, the least recently used entry is discarded.
...
The server handles entries in the host cache like this:
When the first TCP client connection reaches the server from a given IP address, a new cache entry is created to record the client IP, host name, and client lookup validation flag. Initially, the host name is set to NULL and the flag is false. This entry is also used for subsequent client TCP connections from the same originating IP.
If the validation flag for the client IP entry is false, the server attempts an IP-to-host name-to-IP DNS resolution. If that is successful, the host name is updated with the resolved host name and the validation flag is set to true. If resolution is unsuccessful, the action taken depends on whether the error is permanent or transient. For permanent failures, the host name remains NULL and the validation flag is set to true. For transient failures, the host name and validation flag remain unchanged. (In this case, another DNS resolution attempt occurs the next time a client connects from this IP.)
If an error occurs while processing an incoming client connection from a given IP address, the server updates the corresponding error counters in the entry for that IP. For a description of the errors recorded, see Section 25.12.16.1, “The host_cache Table”.
To unblock blocked hosts, flush the host cache;
...根据文档说明，MySQL对于每个新的客户端连接，都会检查客户端主机名host name是否在主机缓存中host cache。如果在，则MySQL服务器根据主机是否被阻塞、拒绝或者继续处理连接请求；如果客户端主机不在缓存中，服务器会尝试解析主机名。首先，它将 IP 地址解析为主机名，然后将该主机名解析回 IP 地址。然后它将结果与原始 IP 地址进行比较，以确保它们相同。服务器将有关此操作结果的信息存储在主机缓存中。如果缓存已满，则丢弃最近最少使用的条目。
服务器处理主机缓存中流程如下所示：
要解除阻塞的主机，请刷新主机缓存；请参阅 Dealing with Blocked Hosts。
如果发生来自其他主机的活动，即使没有刷新主机缓存，被阻止的主机也可能会变得畅通：
从上可以看出host cache优缺点：
缺点：当一个新的客户端连接进来后，要建立一条新的记录，如果
DNS解析很慢，会影响性能，如果被允许连接的客户端主机过多，也会影响到性能。优点：通常情况下，主机名是不变的，而
IP是变化的，如果一个客户端的IP经常变化，那么基于IP的授权，也能防止外界的暴力破解。解决方案
通过上面的分析可以得知原因出在MySQL对连接的客户端进行DNS反向解析所致，有以下两种解决方案：
