MySQL安装connection_control限制登录次数插件
Connection-Control插件用来控制客户端在登录操作连续失败一定次数后的响应的延迟。可防止客户端暴力破解。
一、查询插件是否安装
mysql> show plugins;
| CONNECTION_CONTROL | ACTIVE | AUDIT | connection_control.so | GPL |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE | INFORMATION SCHEMA | connection_control.so | GPL |
+------------------------------------------+----------+--------------------+-----------------------+---------+
50 rows in set (0.01 sec)
如果有connection_control则已安装，没有则继续下一步。
二、安装插件
在mysql5.7后mysql/data/lib/plugin目录默认增加了connection_control.so插件,安装即可：
1. install plugin connection_control soname "connection_control.so"; #登录错误次数限制插件
2. install plugin connection_control_failed_login_attempts soname 'connection_control.so'; #为了把错误次数记录到表中
三、设置插件
查询一下安装状态show variables like "%connection_control%";
mysql> show variables like '%connection_control%';
+-------------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 4 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 1000 |
+-------------------------------------------------+------------+
3 rows in set (0.01 sec)
解释：
connection_control_max_connection_delay :超过最大失败次数之后阻塞登录最大时间（毫秒）
connection_control_failed_connections_threshold ：连续失败最大次数3次,0表示不开启
connection_control_min_connection_delay :超过最大失败次数之后阻塞登录最小时间（毫秒）
修改配置命令：set global connection_control_failed_connections_threshold=5
四、修改my.cnf配置文件
[mysqld]
plugin-load-add = connection_control.so
connection-control = FORCE
connection-control-failed-login-attempts = FORCE
connection_control_min_connection_delay = 1000
connection_control_max_connection_delay = 86400
connection_control_failed_connections_threshold = 3
五、查询插件状态
show status like "%connection_control%";
mysql> show status like "%connection_control%";
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 14 |
+------------------------------------+-------+
1 row in set (0.01 sec)
Connection_control_delay_generated：表示连接控制的使用次数(可用户判断是否存在暴力登录尝试)
重新配置connection_control_failed_connections_threshold变量，该表记录会被删除(重置)
SET GLOBAL connection_control_failed_connections_threshold = 3;
六、查询各账号登录失败次数
1. use information_schema;
2. select * from connection_control_failed_login_attempts;
mysql> select * from information_schema.connection_control_failed_login_attempts;
+--------------------+-----------------+
| USERHOST | FAILED_ATTEMPTS |
+--------------------+-----------------+
| 'root'@'localhost' | 12 |
+--------------------+-----------------+
1 row in set (0.00 sec)
如果使用不存在的用户登录，则该表记录用户名为空，但会记录具体登录的IP
测试环节：
让我们通过尝试使用MySQL Shell和无效密码连接到MySQL Server来测试我们的设置。我们将连续尝试10次，并且只显示命令花费的实时时间（连接尝试）：
[root@node250 mysqlshell]#TIMEFORMAT=%R
[root@node250 mysqlshell]# for i in `seq 1 10`; do time mysql -uroot -p'1233' -hlocalhost | grep real; done
0.052
0.037
0.038
1.040
2.061
3.055
4.059
很明显，在第4次尝试后，响应时间不断增加。
我们可以验证状态变量Connection_control_delay_generated，该变量包含服务器对失败连接尝试的响应添加延迟的次数：
mysql> show global status like 'connection_control_%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 7 |
+------------------------------------+-------+
1 row in set (0.00 sec)
这一次connection_control_failed_login_tempts表不为空:
mysql> select * from information_schema.connection_control_failed_login_attempts;
+--------------------+-----------------+
| USERHOST | FAILED_ATTEMPTS |
+--------------------+-----------------+
| 'root'@'localhost' | 7 |
+--------------------+-----------------+
1 row in set (0.00 sec)
要重置这些计数器，只需再次为变量connection_control_failed_connections_threshold赋值：
mysql> SET GLOBAL connection_control_failed_connections_threshold = 4;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.connection_control_failed_login_attempts;
Empty set (0.00 sec)
