遇到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
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> show variables like 'connect_timeout';
mysql>setglobal connect_timeout=20;
参考资料
https://www.jianshu.com/p/3a397a358a22
