简介：
Connection-Control是Oracle 官方提供的连接限制插件，它的作用是在客户端多次连接失败后，开始增加对客户端的响应延迟。
该插件的校验发生在mysql.user中的连接权限之后。也就是先使用user@ip来进行连接权限校验，然后才去判断用户名密码是否正确。
1.插件安装：
connecion_control.so为官方自带插件，在$mysql_home/lib/plugin下可见。
[root@localhost plugin]# pwd/app/mysql/lib/plugin[root@localhost plugin]# ll connection_control.so-rwxr-xr-x. 1 mysql mysql 669244 Jun 8 2018 connection_control.so
永久生效：
在 my.cnf 添加
plugin-load-add=connection_control.so
重启数据库。
临时生效：
[root@localhost ~]#mysql -uroot -p123456mysql>INSTALL PLUGIN CONNECTION_CONTROLSONAME 'connection_control.so';mysql>INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTSSONAME 'connection_control.so';
2.查看插件当前状态
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)
3.查看参数
mysql> show variables like '%connection_control%';+-------------------------------------------------+------------+| Variable_name | Value |+-------------------------------------------------+------------+| connection_control_failed_connections_threshold | 3 || connection_control_max_connection_delay | 2147483647 || connection_control_min_connection_delay | 1000 |+-------------------------------------------------+------------+3 rows in set (0.00 sec)
其中
connection_control_failed_connections_threshold 用来控制正常的连接次数，当该参数修改为0时，连接错误时不再做延迟限制。connection_control_min_connection_delay用来控制当正常的连接次数用完时需要等待的时间，默认1s，当再次连接错误时，延迟时间为
(当前失败总次数 - connection_control_failed_connections_threshold)* 1s，依次递增，注意这里是每次递增1s而不是connection_control_min_connection_delay的秒数connection_control_max_connection_delay连接延迟时间的最大值，当达到最大值后，延迟时间不再增加。
4.查看用户登录错误次数
这个表记录了用户登录错误的次数，不管是否被delay了
mysql> select *from information_schema.Connection_control_failed_login_attempts;+----------------------------------+-----------------+| USERHOST | FAILED_ATTEMPTS |+----------------------------------+-----------------+| 'root'@'%' | 1 || 'test'@'localhost' | 13 || 'test@192.168.1.5' | 1 |+----------------------------------+-----------------+3 rows in set (0.00 sec)
这个状态表示用户被delay的总次数
mysql> show status like 'Connection_control_delay_generated';+------------------------------------+-------+| Variable_name | Value |+------------------------------------+-------+| Connection_control_delay_generated | 17 |+------------------------------------+-------+1 row in set (0.04 sec)
当connection_control_failed_connections_threshold被修改时，connection_control_delay_generated值和connection_control_failed_login_attempts表被重置为0，如下所示
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)mysql> show status like 'Connection_control_delay_generated';+------------------------------------+-------+| Variable_name | Value |+------------------------------------+-------+| Connection_control_delay_generated | 0 |+------------------------------------+-------+1 row in set (0.01 sec)
当客户端被delay了之后，即使下次输入的是正确的用户名密码，也需要等待delay的时间。
我们来具体看个例子：
[root@localhost ~]# time mysql -uroot -p222222mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)real 0m0.003suser 0m0.001ssys 0m0.002s[root@localhost ~]# time mysql -uroot -p222222mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)real 0m0.004suser 0m0.000ssys 0m0.004s[root@localhost ~]# time mysql -uroot -p222222mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)real 0m0.004suser 0m0.002ssys 0m0.001s[root@localhost ~]# time mysql -uroot -p222222mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)real 0m3.006suser 0m0.002ssys 0m0.002s[root@localhost ~]# time mysql -uroot -p222222mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)real 0m3.005suser 0m0.000ssys 0m0.003s[root@localhost ~]# time mysql -uroot -p222222mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)real 0m3.006suser 0m0.002ssys 0m0.000s[root@localhost ~]# time mysql -uroot -p222222mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)real 0m4.004suser 0m0.002ssys 0m0.001s[root@localhost ~]# time mysql -uroot -p222222mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)real 0m5.006suser 0m0.001ssys 0m0.003s[root@localhost ~]# time mysql -uroot -p123456 -e "quit"mysql: [Warning] Using a password on the command line interface can be insecure.real 0m6.007suser 0m0.001ssys 0m0.002s[root@localhost ~]# time mysql -uroot -p123456 -e "quit"mysql: [Warning] Using a password on the command line interface can be insecure.real 0m0.005suser 0m0.004ssys 0m0.000s
从以上过程可以看到，当错误次数达到connection_control_failed_connections_threshold值时，下一次就开始响应延迟，且为最小延迟时间connection_control_min_connection_delay。
延迟时间具体逻辑为：
当前连接错误次数<=connection_control_failed_connections_threshold：
延迟时间为：connection_control_min_connection_delay
当前连接错误次数>connection_control_failed_connections_threshold:
延迟时间为：connection_control_min_connection_delay+（当前连接错误次数-connection_control_failed_connections_threshold）*1s
被延迟后若输入正确的用户名密码，仍旧会被延迟，但该次登入后，Connection_control_failed_login_attempts变量和Connection_control_failed_login_attempts表均被清空，且之后该客户端的连接尝试将重新开始计算。
mysql> show status like 'Connection_control_failed_login_attempts';Empty set (0.01 sec)mysql> select *from information_schema.Connection_control_failed_login_attempts;Empty set (0.00 sec)
5.connection_control插件强制生效
如果只在my.cnf中添加
plugin_load="connection_control.so"
那么登入系统后，该插件是可以直接卸载掉的：
mysql> uninstall plugin connection_control;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> uninstall plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;Query OK, 0 rows affected (0.00 sec)mysql> select plugin_name,plugin_status ,plugin_type from information_schema.plugins where plugin_name like 'connection_control%';Empty set (0.00 sec)
如上所示，插件已经被卸载了，除非重启，或者重新在线安装。
解决办法：在my.cnf添加两行
connection-control=FORCE_PLUS_PERMANENTconnection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
这两个参数可以在mysql启动加载connection_control插件后，防止手动卸载connection_control插件，以下为演示过程：
mysql> uninstall plugin CONNECTION_CONTROL;ERROR 1702 (HY000): Plugin 'CONNECTION_CONTROL' is force_plus_permanent and can not be unloadedmysql> uninstall plugin CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;ERROR 1702 (HY000): Plugin 'CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS' is force_plus_permanent and can not be unloaded
如上所示，插件已经不能卸载了，再来看看当前connection_control的状态：
mysql> select plugin_name,plugin_status ,plugin_type from information_schema.plugins where plugin_name like 'connection_control%';+------------------------------------------+---------------+--------------------+| plugin_name | plugin_status | plugin_type |+------------------------------------------+---------------+--------------------+| CONNECTION_CONTROL | ACTIVE | AUDIT || CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE | INFORMATION SCHEMA |+------------------------------------------+---------------+--------------------+2 rows in set (0.00 sec)
状态正常，说明我们修改后的参数起作用了。
