从源码解析ERROR 1129 (HY000):Host is blocked because of many connection errors

原创水平有限,有错请指出

源码版本5.7.14

今天群里一个朋友出现如下错误：
ERROR 1129 (HY000): Host '10.0.0.8' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
为了找到这个问题原因，首先在源码中找到错误码

然后找到抛错地点如下：
位于
sql_connect.cc下的check_connection()函数的

这里如果如果rc == RC_BLOCKED_HOST RC_BLOCKED_HOST是一个宏定义为1
#define RC_BLOCKED_HOST 1
为真则抛错,接下来我们需要看rc是函数ip_to_hostname的返回值
位于hostname.cc 的ip_to_hostname函数中

到这里我们找到了和这个报错相关的一些事实：
1、max_connect_errors和这个报错有关
2、SPECIAL_NO_RESOLVE和这个报错有关
3、SPECIAL_NO_HOST_CACHE和这个报错有关

max_connect_errors参数不用再解释，如果!(specialflag & SPECIAL_NO_RESOLVE)返回为假则不会调用
ip_to_hostname做IP域名反解析,如果!(specialflag & SPECIAL_NO_HOST_CACHE)返回为假则不会调用if
以后报错的内容。换句话说这里的位与后然后取反关系到了这个报错的是否触发，那么我们就用必要看看
specialflag和SPECIAL_NO_RESOLVE以及SPECIAL_NO_HOST_CACHE的关系了。
下面是源码参数
MYSQLD.CC
其实这里是受到OPT_SKIP_HOST_CACHE、OPT_SKIP_RESOLVE的控制就是我们的参数
skip-host-cache
skip-name-resolve
那么我们起码能够通过skip-host-cache和skip-name-resolve来解决问题，但是这也带来一个问题
建立用户的时候将不能使用域名，如果有UNIX网络编程基础的朋友应该知道客户端通过socket连接
服务端得到的是IP地址和客户端的端口如果一旦关闭IP->DNS的反解析，user@domain这种用户将是
不能连接的，只能是user@ip，这点异常重要。本来是可以连接的：

 create user testuuu@'test' identified by '123';
 /etc/hosts设置为：
 192.168.190.60 test
 
root@test1 ~]# mysql -utestuuu -p123 -h292.168.190.93 -P13001
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.14-7-debug-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit

但是设置skip-host-cache、skip-name-resolve后
[root@test1 ~]# mysql -utestuuu -p123 -h292.168.190.93 -P13001
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'testuuu'@'192.168.190.60' (using password: YES)
明显这里没有通过192.168.190.60去做反解析.

其实关于很多解析的报错都是在函数ip_to_hostname函数中，如果skip-name-resolve将不会发生这些
包含如下：
 sql_print_warning("IP address '%s' could not be resolved: %s",ip_key,gai_strerror(err_code));
 sql_print_warning("IP address '%s' has been resolved " "to the host name '%s', which resembles " "IPv4-address itself.",ip_key,hostname_buffer);
 sql_print_warning("Host name '%s' could not be resolved: %s",hostname_buffer,gai_strerror(err_code));
 sql_print_warning("Hostname '%s' does not resolve to '%s'.",hostname_buffer,ip_key);
 如果遇到问题类似问题紧急情况下先设置skip-name-resolve再说。

注意：
1、
在整个解析期间可能还会出现下面的用户
unauthenticated user
也就是TCP/IP 握手已经成功，数据已经开始交互，线程已经建立，但是还没有通过MYSQL 权限认证的用户(不知这句结论正确与否，自我理解)。

mysql> show processlist;
+----+----------------------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
| Id | User                 | Host                 | db   | Command | Time | State    | Info             | Rows_sent | Rows_examined |
+----+----------------------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
|  5 | unauthenticated user | 192.168.190.60:12770 | NULL | Connect |   35 | login    | NULL             |         0 |             0 |
|  6 | root                 | localhost            | NULL | Query   |    0 | starting | show processlist |         0 |             0 |
+----+----------------------+----------------------+------+---------+------+----------+------------------+-----------+---------------+
如果出现这种用户优先考虑一下是否是DNS解析缓慢问题，如开启了mysql 反解析，没有设置合适的/etc/hosts
2、测试期间，未关闭skip-name-resolve首先使用了user@ip进行了连接，然后删除用户建立一个user@domain的用户
   并且设置了/etc/hosts，这个时候客户端连接不上服务端，一直报密码不对，这个问题flush hosts后解决，这是
   因为最开始使用IP反解析的时候得到的域名和我设置的/etc/hosts的域名不一致，虽然得到了正确的IP，但是在
   host cache中，得到的domain并不一致，也就是说本来是username@domain1被反解析为了username@domain2这种
   情况当然在mysql.user中找不到相应的权限用户记录了，这个时候flush hosts后清空了host cache，得以重新
   生成相应的host cache后正常，这点在前面的代码
   Host_entry *entry= hostname_cache_search(ip_key);//在cache中查找
   也有体现。
3、select * from performance_schema.host_cache 这里记录了IP->DOMAIN的类容
如下：
mysql> select * from performance_schema.host_cache \G;
*************************** 1. row ***************************
                                        IP: 192.168.190.60
                                      HOST: test
                            HOST_VALIDATED: YES
..................
                                FIRST_SEEN: 2017-05-31 17:17:40
                                 LAST_SEEN: 2017-05-31 17:17:40
                          FIRST_ERROR_SEEN: NULL
                           LAST_ERROR_SEEN: NULL
4、反解析的作用
如前面所讲述，反解析IP->DOMAIN的作用就在于，在建立如下用户的时候
create user testuuu@'test' identified by '123';
的时候MYSQL能够通过ip地址判断出他的权限信息，因为在MYSQL.USER中存储的是域名(DOMAIN)，而为了加速反解析的速度，而有了HOST CACHE
那就是
第一次连接
1、拿到客户端IP地址(socket连接客户端IP地址信息)
2、进行DNS反解析 (如/etc/hosts)
3、存储反解析信息到host cache
4、从host cache中拿到这个反解析出来的domain进行权限验证
如果不是第一次连接跳过第二步，也正是因为跳过了第二步，产生了注意2中的问题
flush host就是来清理host cache从而解决这种问题，重新进行DNS反解析                        
其实整个定位问题的过程还是比较简单，但是我至今没有找到entry->m_errors.m_connect什么时候增加，而且源码中
还有很多地方 没有理解由于时间原因我没有去仔细看(因为这要消耗很多很多的时间)，这里只能抛砖了，同时也记录
了我的分析过程。
   
下面是MYSQL官方手册的解释：   
9.12.6.2 DNS Lookup Optimization and the Host Cache   
The server handles entries in the host cache like this:
1. When the first TCP client connection reaches the server from a given IP address, a new entry is
created to record the client IP, host name, and client lookup validation flag. Initially, the host name is
set to NULLand the flag is false. This entry is also used for subsequent client connections from the
same originating IP.
2. If the validation flag for the client IP entry is false, the server attempts an IP-to-host name 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 NULLand the validation flag
is set to true. For transient failures, the host name and validation flag remain unchanged. (Another DNS
resolution attempt occurs the next time a client connects from this IP.)
3. 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 23.9.16.1, “The host_cache Table”.
The server uses the host cache for several purposes:
? By caching the results of IP-to-host name lookups, the server avoids doing a DNS lookup for each client
connection. Instead, for a given host, it needs to perform a lookup only for the first connection from that
host.
? The cache contains information about errors that occur during the connection process. Some errors are
considered “blocking.”If too many of these occur successively from a given host without a successful
connection, the server blocks further connections from that host. The max_connect_errorssystem
variable determines the number of permitted errors before blocking occurs. See Section B.5.2.6, “Host
'host_name' is blocked”.
To unblock blocked hosts, flush the host cache by issuing a FLUSH HOSTSstatement or executing a
mysqladmin flush-hostscommand.

作者微信：