遇到mysqladmin flush-hosts报错解决思路

背景

今天早上遇到有Hue用户登录不上，查看集群状态服务有问题。

查看报错日志如下，应该是对应的用户报错链接太多了，导致报错。而我们这边的用户都是运维通过一个nginx中转过来的，所以都是同一个ip地址。导致报这个错误。

Processing exception: (1129, "Host 'cdh011' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"): Traceback (most recent call last):

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/core/handlers/base.py", line 112, in get_response

response = wrapped_callback(request, *callback_args, **callback_kwargs)

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/transaction.py", line 370, in inner

with self:

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/transaction.py", line 237, in __enter__

if not connection.get_autocommit():

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/__init__.py", line 331, in get_autocommit

self.ensure_connection()

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/__init__.py", line 127, in ensure_connection

self.connect()

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/utils.py", line 99, in __exit__

six.reraise(dj_exc_type, dj_exc_value, traceback)

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/__init__.py", line 127, in ensure_connection

self.connect()

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/__init__.py", line 115, in connect

self.connection = self.get_new_connection(conn_params)

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/Django-1.6.10-py2.7.egg/django/db/backends/mysql/base.py", line 435, in get_new_connection

conn = Database.connect(**conn_params)

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/MySQL_python-1.2.5-py2.7-linux-x86_64.egg/MySQLdb/__init__.py", line 81, in Connect

return Connection(*args, **kwargs)

File "/opt/cloudera/parcels/CDH-5.15.0-1.cdh5.15.0.p0.21/lib/hue/build/env/lib/python2.7/site-packages/MySQL_python-1.2.5-py2.7-linux-x86_64.egg/MySQLdb/connections.py", line 193, in __init__

super(Connection, self).__init__(*args, **kwargs2)

OperationalError: (1129, "Host 'cdh011' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'")

解决方案

1. 调整MAX_CONNECT_ERRORS参数大小

mysql>setglobal max_connect_errors=1000;

2. 清理host_cache

1. MySQL performance_schema下的hosts表和host_cache，host_cache表会记录连接客户端主机名、IP地址信息和其他连接信息，帮助诊断连接问题;

需要关注的两个列：

SUM_CONNECT_ERRORS:连接错误数

COUNT_HANDSHAKE_ERRORS:握手错误计数

3. 调大connect_timeout参数

由于网络原因或其它一些连接错误会导致SUM_CONNECT_ERRORS累加1；

网络原因 Lost connection to MySQL server at 'XXX', system error: errno；

网络原因可以通过设置connect_timeout解决

mysql> mysql> show variables like 'connect_timeout';
mysql>setglobal connect_timeout=20;

参考资料

https://www.jianshu.com/p/3a397a358a22