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;

卸载插件plugin

mysql> UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;

mysql> UNINSTALL PLUGIN CONNECTION_CONTROL;