背景

今天早上遇到有Hue用戶登錄不上，查看集群狀態(tài)服務(wù)有問題。

查看報(bào)錯(cuò)日志如下，應(yīng)該是對(duì)應(yīng)的用戶報(bào)錯(cuò)鏈接太多了，導(dǎo)致報(bào)錯(cuò)。而我們這邊的用戶都是運(yùn)維通過一個(gè)nginx中轉(zhuǎn)過來的，所以都是同一個(gè)ip地址。導(dǎo)致報(bào)這個(gè)錯(cuò)誤。

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.調(diào)整MAX_CONNECT_ERRORS參數(shù)大小

mysql>setglobal max_connect_errors=1000;

2.清理host_cache

1. MySQL performance_schema下的hosts表和host_cache，host_cache表會(huì)記錄連接客戶端主機(jī)名、IP地址信息和其他連接信息，幫助診斷連接問題;

????????????需要關(guān)注的兩個(gè)列：

????????????SUM_CONNECT_ERRORS:連接錯(cuò)誤數(shù)

????????????COUNT_HANDSHAKE_ERRORS:握手錯(cuò)誤計(jì)數(shù)

????3.調(diào)大connect_timeout參數(shù)

????????????由于網(wǎng)絡(luò)原因或其它一些連接錯(cuò)誤會(huì)導(dǎo)致SUM_CONNECT_ERRORS累加1；

????????????網(wǎng)絡(luò)原因 Lost connection to MySQL server at 'XXX', system error: errno；

????????????網(wǎng)絡(luò)原因可以通過設(shè)置connect_timeout解決

????????????mysql>?show variables like 'connect_timeout';?

??????mysql>setglobal connect_timeout=20;

參考資料

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