生产环境中我们通常有这样的需求，当同一个MySQL用户连续因密码错误等问题连接失败时，锁定该用户或阻止一段时间该用户的登录，以缓解对MySQL用户的暴力破解风险。MySQL 5.7.17版本开始提供 connection-control plugins,用于用户连接控制包括CONNECTION_CONTROL 和 CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS两个插件；该插件使管理员能设置当连续连接失败超过指定次数后，服务端根据配置值增加对连接的相应延迟。使用该插件能缓解MySQL用户的暴力破解；
CONNECTION_CONTROL插件检查连接行为并根据需要增加服务器相应的延迟，该插件提供系统配置参数和基本状态变量；
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS 插件实现了一个INFORMATION_SCHEMA table，并提供失败的连接的更为详细的信息；
安装
首先确认MySQL插件目录，plugin library文件需位于该路径下。在linux系统下plugin library文件名为connection_control.so
mysql> show global variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.01 sec)
mysql> system ls -ltr /usr/local/mysql/lib/plugin/ |grep connection_control.so
-rwxr-xr-x 1 7161 31415 1342192 Jun 17 2020 connection_control.so
为了在MySQL进程启动时加载插件，需要使用–plugin-load-add选项，如在配置文件中追加
[mysqld]
plugin-load-add=connection_control.so
也可使用以下命令在运行时加载插件。install plugin命令会立即加载插件，并同时注册到系统表 mysql.plugins中。这样服务器后续正常启动时会自动加载，不再需要使用–plugin-load-add选项。
mysql> install plugin connection_control soname 'connection_control.so';
Query OK, 0 rows affected (0.01 sec)
mysql> install plugin connection_control_failed_login_attempts soname 'connection_control.so';
Query OK, 0 rows affected (0.00 sec)
验证connection_control插件情况
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)
系统参数和状态变量
-
connection_control_failed_connections_threshold
连接失败阈值，当连续连接失败超过该阈值是服务端会增加连接响应延迟。为0时禁用连接失败计数，服务端永不会增加延迟。该值为非零值N时，连续连接失败的N+1次尝试将会增加响应延迟。默认为3，取值范围0–2147483647。
-
connection_control_min_connection_delay
服务端增加响应延迟的最少时间，单位为毫秒。默认值1000，取值范围 1000–2147483647。
-
connection_control_max_connection_delay
服务端增加响应延迟的最大时间，单位为毫秒。默认值1000，取值范围 1000–2147483647。
-
状态变量 Connection_control_delay_generated
服务端增加响应延迟的次数，这是一个全局累计值，当修改connection_control_failed_connections_threshold值后该状态值会重置为零；
mysql> show status like 'connection_control_delay_generated'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 13 | +------------------------------------+-------+ 1 row in set (0.00 sec) mysql> set global connection_control_failed_connections_threshold=4; Query OK, 0 rows affected (0.00 sec) mysql> show status like 'connection_control_delay_generated'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | Connection_control_delay_generated | 0 | +------------------------------------+-------+ 1 row in set (0.00 sec)更详细的信息请查询information_schema.connection_control_failed_login_attempts表，表中记录了各用户失败连接的次数，正常连接后记录会被清理；当修改connection_control_failed_connections_threshold值后该表会清空
mysql> select * from information_schema.connection_control_failed_login_attempts; +----------------------+-----------------+ | USERHOST | FAILED_ATTEMPTS | +----------------------+-----------------+ | 'test1'@'localhost' | 1 | | 'test'@'%' | 2 | | 'root'@'localhost' | 2 | | 'teste1'@'localhost' | 6 | +----------------------+-----------------+ 4 rows in set (0.00 sec)
配置使用
当connection_control_min_connection_delay配置为一个非零值N时，用户连续连接失败小于等于N次，延迟为零； 此后服务端会增加服务响应的延迟，直到连接成功。延迟时间从connection_control_min_connection_delay开始每次失败递增1秒，直到connection_control_max_connection_delay；如connection_control_min_connection_delay 等于 connection_control_max_connection_delay 则每次延迟时间不变。
注意，连续失败后第一次成功的链接依然会有响应延迟，但会重置该用户的失败连接次数；
举个例子，设置策略当用户连续连接失败6次后延迟30s，配置如下
#设置参数
mysql> set global connection_control_failed_connections_threshold=6;
Query OK, 0 rows affected (0.00 sec)
mysql> set global connection_control_max_connection_delay=30000;
Query OK, 0 rows affected (0.00 sec)
mysql> set global connection_control_min_connection_delay=30000;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'connection_control_%';
+-------------------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------------------+-------+
| connection_control_failed_connections_threshold | 6 |
| connection_control_max_connection_delay | 30000 |
| connection_control_min_connection_delay | 30000 |
+-------------------------------------------------+-------+
3 rows in set (0.00 sec)
#错误连接，可以看到前6次响应时间无延迟
[root@node1 ~]# time mysql -utest -pIncorrect_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
real 0m0.010s
user 0m0.008s
sys 0m0.001s
[root@node1 ~]# time mysql -utest -pIncorrect_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
real 0m0.010s
user 0m0.006s
sys 0m0.003s
[root@node1 ~]# time mysql -utest -pIncorrect_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
real 0m0.009s
user 0m0.005s
sys 0m0.003s
[root@node1 ~]# time mysql -utest -pIncorrect_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
real 0m0.010s
user 0m0.006s
sys 0m0.003s
[root@node1 ~]# time mysql -utest -pIncorrect_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
real 0m0.010s
user 0m0.008s
sys 0m0.001s
[root@node1 ~]# time mysql -utest -pIncorrect_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
real 0m0.010s
user 0m0.007s
sys 0m0.002s
#第七次开始错误连接会增加响应时间30s
[root@node1 ~]# time mysql -utest -pIncorrect_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
real 0m30.012s
user 0m0.006s
sys 0m0.005s
[root@node1 ~]# time mysql -utest -pIncorrect_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
real 0m30.011s
user 0m0.007s
sys 0m0.003s
#查看监控数据，该用户连续8次登录失败
mysql> select * from information_schema.connection_control_failed_login_attempts;
+------------+-----------------+
| USERHOST | FAILED_ATTEMPTS |
+------------+-----------------+
| 'test'@'%' | 8 |
+------------+-----------------+
1 row in set (0.00 sec)
#第9次正确连接同样有延迟，但正确登录后会清空该用户的错误连接次数，后续连接无延迟
[root@node1 ~]# time mysql -utest -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 87
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \q
Bye
real 0m43.287s
user 0m0.022s
sys 0m0.006s
[root@node1 ~]# time mysql -utest -pIncorrect_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: YES)
real 0m0.010s
user 0m0.008s
sys 0m0.002s
mysql> select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.00 sec)
