1、插件安装

1.1、配置文件添加配置

  1. vi /etc/my.cnf
  3. [mysqld]
  4. plugin-load-add                                 = connection_control.so
  5. connection-control                              = FORCE
  6. connection-control-failed-login-attempts        = FORCE
  7. connection_control_min_connection_delay         = 1000
  8. connection_control_max_connection_delay         = 86400
  9. connection_control_failed_connections_threshold = 3

1.2执行安装命令

//root账户登录
mysql -u root -p

mysql> install plugin connection_control soname "connection_control.so";
mysql> install plugin connection_control_failed_login_attempts soname "connection_control.so";

1.3查看插件安装状态

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)

2、修改配置文件插件配置

vi /etc/my.cnf

// 修改以下配置，并重启MySQL
// 阻塞一分钟
connection_control_min_connection_delay         = 60000
connection_control_max_connection_delay         = 86400
// 可错误五次
connection_control_failed_connections_threshold = 5

2.1查看修改配置

mysql -u root -p;

mysql> show variables like 'connection_control%';

+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 5          |
| connection_control_max_connection_delay         | 86400      |
| connection_control_min_connection_delay         | 60000      |
+-------------------------------------------------+------------+
3 rows in set (0.00 sec)

2.2 测试

五次错误登录后会阻塞一分钟，一分钟后科再次登录。

3、查看登录次数

mysql -u root -p;

mysql> use information_schema;

// 查看用户登录失败次数，当用户登录成功则删除记录
mysql> select * from connection_control_failed_login_attempts;
// 连接控制的使用次数
mysql> show global status like 'connection_control_delay_generated';