安全插件Connection-Control导致无法登录的问题

Connection-Control 插件是用来控制客户端在登录操作连续失败一定次数后的响应的延迟。可防止客户端暴力破解。

## 问题现象及背景

在业务主机上使用zabbix监控MySQL数据库服务状态；且因等保要求，对数据库开启了安全登录管理控制插件；在运行过程中出现zabbix无法正常获取到数据库状态的情况

经过查询任务线程状态，发现大量`waiting in connection_control plugin`状态的连接等待。

![](/media/202404/2024-04-07_142613_3975440.9801182345287023.png)

## 排查结果

统计状态的连接总数
```
select ID,HOST,DB,COMMAND,TIME,STATE,INFO from \
information_schema.PROCESSLIST where \
STATE='Waiting in connection_control plugin';
```

**检查插件的状态查询**
```SQL
show variables like "%connection_control%";
```
![](/media/202404/2024-04-07_142232_5309630.580454269470782.png)

参数含义说明：
- **connection_control_failed_connections_threshold**：单个用户登录失败（由于密码错误引起）次数上限，默认3次
- **connection_control_max_connection_delay**：失败上限之后再次尝试登录前最大等待时间，单位ms
- **connection_control_min_connection_delay**：失败上限之后再次尝试登录前最小等待时间，单位ms

上述3个参数均可以利用 set global 的方式在线修改。

>1. 尝试多次错误输入密码后，在超过设定值时，每登录一次delay会增加1秒（由connection_control_min_connection_delay指定）
2. 同时Connection_control_delay_generated计数+1（若登录密码继续输入错误，则delay秒数与计数器继续增加。直到成功登录为止之后，此时delay清零

经查是由于等保要求开启账密有效期后密码过期，zabbix反复尝试获取数据时失败导致

## 处理流程

### 1. 更新密码过期
如用户密码过期，需先修改更新用户密码，确保更新时间符合未过期的时间范围
1） 检查全局密码过期默认配置的生命周期
```
SHOW VARIABLES LIKE '%life%';
```
2) 更新用户密码实现同步更新最后一次密码修改时间

```
set password for 'USER'@'HOST' =password('PASSWORD');
```

### 1. 清理当前进程

1) 需要停止当前已存在的服务
2) 删除当前运行中的进程

根据STATE状态为’ Waiting in connection_control plugin’ 从information_schema.PROCESSLIST 表中检索出ID ，再本地文本中拼接出SQL ，再批量kill

方案一：
```
select concat('kill ',ID,';') as killer from information_schema.PROCESSLIST where \
Command='Connect' and STATE='Waiting in connection_control plugin';
```

方案二：
```
pt-kill --victims=all --kill --match-command Connect \
--match-state "Waiting in connection_control plugin" \
--interval=120 u=dba,S=/tmp/mysql.sock --ask-pass --daemonize
```

3) 修改配置，重置delay时间

Connection_control_delay_generated：表示连接控制的使用次数(可用户判断是否存在暴力登录尝试)
重新配置connection_control_failed_connections_threshold变量，该表记录会被删除(重置)
```
set global connection_control_failed_connections_threshold=5;
```