一个由MySQL安全插件引发的生产题目
一、背景生产环境下遇到一个题目，有数据库节点的连接数略高，触发了连接数告警。登录上检察后，发现实际业务压力不大。检察processlist发现有大量状态为“Waiting in connection_control plugin”的等候连接。
https://img-blog.csdnimg.cn/img_convert/89fed5c2e3816dc161863ac7c3d89281.jpeg
mysql> select ID,HOST,DB,COMMAND,TIME,STATE,INFO from information_schema.PROCESSLIST where STATE='Waiting in connection_control plugin';
https://img-blog.csdnimg.cn/img_convert/3b10e01161718d33e1c84b9895a55632.jpeg
该状态的连接总数达到338个
mysql> select COUNT(*) from information_schema.PROCESSLISTwhere STATE='Waiting in connection_control plugin';
https://img-blog.csdnimg.cn/img_convert/127d7d415d3f8652aed91a43d74b23b6.png
应该是Connection-Control Plugins起作用了，先在测试环境模仿一下。
二、模仿测试
2.1、安装
该 Connection-Control Plugins 插件默认未启用，需要自行安装
mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
Query OK, 0 rows affected (0.40 sec)
mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Query OK, 0 rows affected (0.01 sec)
确认下SQL插件是否安装
mysql> select PLUGIN_NAME, PLUGIN_STATUS from INFORMATION_SCHEMA.PLUGINS where PLUGIN_NAME like 'connection%';
+------------------------------------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+------------------------------------------+---------------+
| CONNECTION_CONTROL | ACTIVE |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE |
+------------------------------------------+---------------+
2 rows in set (0.00 sec)
2.2、参数释疑
mysql> show variables like "connection_control%";
+-------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 1000 |
+-------------------------------------------------+------------+
3 rows in set (0.00 sec)
参数含义：
[*]connection_control_failed_connections_threshold：单个用户登录失败（由于密码错误引起）次数上限，默认3次
[*]connection_control_max_connection_delay：失败上限之后再次实验登录前最大等候时间，单位ms
[*]connection_control_min_connection_delay：失败上限之后再次实验登录前最小等候时间，单位ms
上述3个参数均可以利用 set global 的方式在线修改。
2.3、实验
实验3次错误输入密码后，在第4次登录时会delay 1秒（由connection_control_min_connection_delay指定），同时Connection_control_delay_generated计数+1（若登录密码继续输入错误，则delay秒数与计数器继续增加。直到成功登录为止之后，此时delay清零
检察到登录失败的次数
mysql> show global status like "%conn%control%";
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 1 |
+------------------------------------+-------+
1 row in set (0.00 sec)
开启多个连接，继续试错下去，此时可以看到进程中
mysql> select * from information_schema.PROCESSLIST where USER='root';
+----+-------------+-----------+------+---------+------+--------------------------------------+------+---------+-----------+---------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | TIME_MS | ROWS_SENT | ROWS_EXAMINED |
+----+-------------+-----------+------+---------+------+--------------------------------------+------+---------+-----------+---------------+
| 54 | root | localhost | NULL | Connect | 2 | Waiting in connection_control plugin | NULL | 2485 | 0 | 0 |
| 52 | root | localhost | NULL | Connect | 7 | Waiting in connection_control plugin | NULL | 7038 | 0 | 0 |
| 53 | root | localhost | NULL | Connect | 4 | Waiting in connection_control plugin | NULL | 4591 | 0 | 0 |
+----+-------------+-----------+------+---------+------+--------------------------------------+------+---------+-----------+---------------+
3 rows in set (0.00 sec)
这时，我们就回到了一开始提出的生产环境下遇到的题目。
三、解决生产题目
由于题目连接过多，逐个 kill掉显然不太现实
因此，我接纳拼接SQL 的方式，批量 kill
根据STATE状态为Waiting in connection_control plugin' 从information_schema.PROCESSLIST表中检索出 ID ，再本地文本中拼接出SQL ，再批量 kill
mysql> select ID from information_schema.PROCESSLIST where Command='Connect' and STATE='Waiting in connection_control plugin';
检索效果如下
https://img-blog.csdnimg.cn/img_convert/8f43d1eee14b021ae371b3e923070c5e.jpeg
再在文件编辑器中，利用查找替换功能，
将 “ |” 替换为 ";"
https://img-blog.csdnimg.cn/img_convert/a50e1b69fb105e3da68aa46957aab16b.png
再将 “|” 替换为 “kill”
https://img-blog.csdnimg.cn/img_convert/d0f0bc9637833a9f72ce062e39d153df.jpeg
拼接的SQL 效果如下：
https://img-blog.csdnimg.cn/img_convert/89c2c24dee89477a7da52794a8143942.png
执行SQL 文件， 连接数告警 消失，题目临时解决
四、事后分析
后续排查确认是zabbix agent 的一个监控脚本 ，数据库地址 配置的题目，导致存在这样的现象，Waiting in connection_control plugin
补充：
[*]卸载MySQL安全插件：Connection-Control Plugins
[*]注意检察my.cnf 中 是否也界说了此参数
mysql> UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
mysql> UNINSTALL PLUGIN CONNECTION_CONTROL;
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]