从Host blocked报错学习max_connect_errors

  • 1.案发现场-Host blocked

一个案例场景是大数据抽取job任务连接MySQL实例抽数,任务报错如下图所示:
从Host blocked报错学习max_connect_errors
报错表示,host被锁是由于有大量的连接错误,如果要解锁就执行mysqladmin flush-hosts;
知识点!!!
线索:host被锁,大量连接错误
证人提示线索:确认最近新加proxy vip以后开始出现的报错
solution:mysqladmin flush-hosts?
疑点:

1)什么原因导致被锁?
2)为什么提示执行flush-hosts解锁?

初步判断:

1)提示有大量链接错误,可能是密码错误导致?不过一般密码错误应该报错ACCESS DENIED错误,并且密码错误锁定的是用户是从8.0.19版本以后才支持并且锁定的是用户而非host,所以应该不是密码错误问题导致,并且肯定另有隐情;

ps:下文还有证据
从Host blocked报错学习max_connect_errors

2)提示使用flush-hosts解锁,数据库层需要做什么动作?对于这些控制,MySQL层面多是通过参数的设置来进行管控的,不妨就这个方向来排查一下,说不定有妖气呢;

  • 2.案情推理与依赖关系

基于上个章节的判断和简单推理,来查阅一下官方文档的描述,涉及是MySQL5.6版本;
查阅到第一个信息如下:
从官方提供的内容看出报错是由max_connect_errors这个参数控制导致超过限制以后再来自同一个host的连接就会阻止并抛错,解决方式可以通过flush hosts 或者truncate performance_schema.host_cache 或者 mysqladmin flush-hosts方式来解决;当然如果想要增大容错可以增大max_connect_errors大小;
从Host blocked报错学习max_connect_errors
当然,如上的方式确实都可以解决host locked的问题,但是这是数据库层被动的来解决这个问题,可以说是指标不治本,很有可能在容忍的情况下,应用层面和数据库层面的性能和功能都会受损,这里先不展开;
然后可以关注图中最后一段话:

If you get the Host 'host_name' is blocked error message for a given host, you should first verify that there is nothing wrong with TCP/IP connections from that host. If you are having network problems, it does no good to increase the value of max_connect_errors.

大概意思是如果TCP/IP网络层面出现问题,那么即使调整了max_connect_errors参数并不能起到多大的作用;
看到问题主要相关是max_connect_errors参数问题,那么来看下这个参数的具体资料如下所示:
从Host blocked报错学习max_connect_errors
如图示:max_connect_errors是一个global级别参数,默认值是100,最小可以设置为1,可增加大非常大;
参数作用解释:

After max_connect_errors successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. If a connection from a host is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it.

大概意思是,如果来自主机的连续连接并没有成功连接且请求被中断超过max_connect_errors设置参数限制后,MySQL server会拒绝来自该主机再次发送过来的请求,如果在先前的连接被中断之后,在少于max_connect_errors次尝试的时间内成功建立了来自主机的连接,则该主机的错误计数将清零。然而,一旦host被locked的话,那么真想只有一个,flush host_cache;
(ps:下划线内容后续3.3章节来验证)

For more information about how the host cache works, see Section 5.1.11.2, “DNS Lookups and the Host Cache”.

从Host blocked报错学习max_connect_errors
从上述的文章内容看host cache和DNS的解析有关,host cache主要记录第一次host 连接进来进行DNS解析来记录IP,然后对于之后的cache的ip来进行host的结果查找;
从Host blocked报错学习max_connect_errors
由此可以看出host_cache表是针对DNS的ip-to-host的解析结果的一个记录,这里相对应通过host_cache_size参数来控制环境的解析多少记录情况;
而结合max_connect_errors的一个错误记录情况,就是和host_cache表中的一个字段sum_connect_errors做对应;
host_cache表的sum_connect_errors字段的解释如下官方文档描述:
从Host blocked报错学习max_connect_errors
大致意思就是:仅统计协议握手错误,并且仅统计通过验证的主机(HOST_VALIDATED = YES)。如果给定主机的SUM_CONNECT_ERRORS达到max_connect_errors的值后,该主机的新连接将被阻止。(ps:这里还有一些其他的特性描述不多展开);
这里还有另外一个参数就是skip-name-resolve来禁用DNS,该参数解释如下官方内容;
从Host blocked报错学习max_connect_errors
大概意思是,通过该参数的设置,所有的连接将必须通过IP地址的方式来进行连接,跳过了DNS的解析步骤;
结合如上官方文档描述和解释的情况来总结看这些参数,表,字段之间的关系图谱如下:
从Host blocked报错学习max_connect_errors
所以针对host locked问题的解决方式有如下几种:
1.flush hosts/truncate host_cache/mysqladmin flush-hosts,属于暴力清空host_cache表解决;
2.增大host_cache_size到一个比较大的值,属于增加容忍度,这个可以容忍的数量就是设置参数的大小,无需再验证;
3.设置skip_name_resolve为on,禁用DNS解析,强制ip直接解析,等于禁用host_cache表;
4.设置host_cache_size为0,等于完全抛弃host_cache的DNS解析缓存的作用,每次连接都做DNS解析;
好了,这里针对第二个初步判断的方向提供了可行的证据,接下来根据这些可行方案进行逐一验证;

  • 3.案情推理方案验证

环境:MySQL 5.7.17
需要打开performance_schema
从Host blocked报错学习max_connect_errors
确认max_connect_errors参数及host_cache表情况如下初始状态:
从Host blocked报错学习max_connect_errors

  • 3.1 密码错误非原因验证

验证之前先验证一下密码错误不是max_connect_error的原因
在客户端进行密码输入错误三次,第四次输入正确密码登录正常;
从Host blocked报错学习max_connect_errors
检查host_cache表记录情况如下:
从Host blocked报错学习max_connect_errors
这也就证明了,密码输入错误不会影响connect_errors记录次数情况,这里其实是aborted_connections报错问题,而真正影响connect_errors的是aborted_client的问题,有兴趣可以自行学习一波;

  • 3.2 flush hosts暴力清空验证

flush hosts/truncate host_cache/mysqladmin flush-hosts,属于暴力清空host_cache表解决;
ps:这里只采用flush hosts方式验证,三种方式原理一致,不再重复;
手动设置connect_timeout超时时间为2s
从Host blocked报错学习max_connect_errors
用Linux下的netem与tc命令模拟构造出复杂环境下的网络传输延时案例
tc qdisc add dev eth0 root netem delay 3000ms
(ps:该命令设置完以后,server上的命令执行就会变得特别缓慢,延迟时间越长越缓慢,要耐得住性子)
从Host blocked报错学习max_connect_errors
从客户端访问就会出现延时的情况发生;
从Host blocked报错学习max_connect_errors
然后开始模拟mysql连接
如下尝试连接3次client端由于网络延迟的情况导致连接都报错
从Host blocked报错学习max_connect_errors
查看host_cache记录内容会记录sum_connect_errors有3次;
(ps:如果是本地虚拟机测试,网卡状态一定需要设置为nat,并且配置DNS解析服务器,如果设置是host only无法进行测试
参考:https://blog.csdn.net/weixin_41216918/article/details/88188357

然后查看host_cache表记录出现报错三次;
从Host blocked报错学习max_connect_errors
然后删除掉延时情况状态
tc qdisc del dev eth0 root netem delay 3000ms
从Host blocked报错学习max_connect_errors
再次执行连接动作报错如下,复现报错场景:
从Host blocked报错学习max_connect_errors
然后通过flush hosts方式处理,再次尝试连接成功;
从Host blocked报错学习max_connect_errors

从Host blocked报错学习max_connect_errors

  • 3.3 未超最大限制恢复正常验证

同样按照3.2章节延时动作进行延时模拟;
从Host blocked报错学习max_connect_errors
client端进行连接测试报错
从Host blocked报错学习max_connect_errors
查询host_cache表情况
从Host blocked报错学习max_connect_errors
执行正常连接(删除延时并且连接)
从Host blocked报错学习max_connect_errors

从Host blocked报错学习max_connect_errors
再次查看host_cache表内容,sum_connect_errors清空;
从Host blocked报错学习max_connect_errors

  • 3.4 skip_name_resolve方案验证

设置skip_name_resolve为on,禁用DNS解析规则;
从Host blocked报错学习max_connect_errors
模拟网络延迟的情况;
然后client端进行连接报错;
从Host blocked报错学习max_connect_errors
在从server端看host_cache表的情况
从Host blocked报错学习max_connect_errors
证明已经不再记录DNS解析的环境信息内容;
删除网络延时的模拟;
再次连接验证正常连接;
从Host blocked报错学习max_connect_errors
证明通过skip_name_resolve参数可以直接禁用掉整个DNS解析;

  • 3.5 host_cache_size为0方案验证

host_cache_size设置为0
从Host blocked报错学习max_connect_errors
同样的方式模拟网络延迟情况,这里省略步骤;
从Host blocked报错学习max_connect_errors
查看host_cache表的情况内容为空,已经不再记录任何缓存的DNS解析的信息;
从Host blocked报错学习max_connect_errors

  • 4.案情处理方式及对比

数据库层面方案对比
如下对比只是从数据库层面来展示如何解决,治标不治本,仅供参考;
从Host blocked报错学习max_connect_errors

  • 5.结案

数据库层面解决方式
在网络层,网络安全保障前提下,推荐skip_name_resolve方案,禁用DNS解析服务;
应用侧解决方式
针对最初的问题还是要考虑网络层即TCP/IP侧的性能情况,网络包体发送耗时等入手来解决,从线索来看增加proxy vip以后出现的问题,需要考虑从应用侧通过proxy vip以及proxy vip到数据库整个网络链路的网络性能及延迟情况做分析,可以利用一些网络工具比如qperf,iperf等网络性能工具来进行测试,确定是否是proxy 本身的能力和资源不足导致出现丢包等情况发生;
最后,夸一下阿里云RDS,不过也是寄托于整个云平台的网络安全机制情况下,skip_name_resolve参数默认设置为on,即禁用DNS解析缓存,避免了类似问题的风险存在;

上一篇:为什么在Python代码中使用局部变量会更快


下一篇:程序从MYSQL迁移ORACLE注意事项之二