MySQL Connection-Control插件安装和使用

​

​ 生产环境中我们通常有这样的需求，当同一个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)
复制