背景

今天公司服务器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 地址进行比较，以确保它们相同。服务器将有关此操作结果的信息存储在主机缓存中。如果缓存已满，则丢弃最近最少使用的条目。

服务器处理主机缓存中流程如下所示：

1. 当第一个 TCP 客户端连接从给定的 IP 地址到达服务器时，会创建一个新的缓存条目来记录客户端 IP(client IP)、主机名(host name)和客户端查找验证标志(client lookup validation flag)。最初，主机名设置为 NULL并且标志为false。此条目还用于来自同一原始 IP 的后续客户端 TCP 连接。
2. 如果客户端 IP 条目的验证标志为false，则服务器尝试对客户端连接进行DNS 解析。如果成功，则使用解析的主机名更新主机名，并将验证标志设置为 true。如果解决不成功，则采取的措施取决于错误是永久性的还是暂时性的。对于永久性故障，主机名保持不变 NULL，验证标志设置为 true。对于暂时性故障，主机名和验证标志保持不变。（在这种情况下，下次客户端从该 IP 连接时会发生另一次 DNS 解析尝试。）
3. 如果在处理来自给定 IP 地址的传入客户端连接时发生错误，则服务器会更新该 IP 条目中的相应错误计数器。有关记录的错误的描述，请参阅 The host_cache Table。

要解除阻塞的主机，请刷新主机缓存；请参阅 Dealing with Blocked Hosts。

如果发生来自其他主机的活动，即使没有刷新主机缓存，被阻止的主机也可能会变得畅通：

• 如果当连接从不在缓存中的客户端 IP 到达时缓存已满，则服务器丢弃最近最少使用的缓存条目以为新条目腾出空间。
• 如果丢弃的条目是针对被阻止的主机的，则该主机将被解除block。

从上可以看出host cache优缺点：

缺点：当一个新的客户端连接进来后，要建立一条新的记录，如果DNS解析很慢，会影响性能，如果被允许连接的客户端主机过多，也会影响到性能。

优点：通常情况下，主机名是不变的，而IP是变化的，如果一个客户端的IP经常变化，那么基于IP的授权，也能防止外界的暴力破解。

解决方案

通过上面的分析可以得知原因出在MySQL对连接的客户端进行DNS反向解析所致，有以下两种解决方案：

1. 将客户端ip写在MySQL服务器的/etc/hosts文件中；

2. 在MySQL配置文件种加入--skip-name-resolve参数，该参数的作用是禁用DNS解析，这种方案的缺点是开启此参数后，mysql.user表中基于主机名的授权将无法使用，且错误日志会提示：

   [Warning] 'user' entry 'user@hostname' ignored in --skip-name-resolve mode.