MySQL会话控制限制登录次数

MySQL 5.7 以后提供了Connection-Control插件用来控制客户端在登录操作连续失败一定次数后的响应的延迟。
该插件可有效的防止客户端暴力登录的风险(攻击)。该插件包含以下两个组件：

connection_control：控制失败次数以及延迟时间
connection_control_failed_login_attempts：将登录失败的操作记录至information_schema表



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 = 5


执行安装命令，使用root账户登录
mysql> install plugin connection_control soname "connection_control.so";
mysql> install plugin connection_control_failed_login_attempts soname "connection_control.so";


验证插件安装状态
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)


查看用户登录失败次数，当用户登录成功则删除记录
mysql> select * from information_schema.connection_control_failed_login_attempts;


连接控制的使用次数
mysql> show global status like 'connection_control_delay_generated';


超过失败次数如果想要马上登录，需要设置一下即可。
mysql> set global connection_control_failed_connections_threshold=0;


成功后别忘了改回来
mysql> set global connection_control_failed_connections_threshold=5;