这期内容当中小编将会给大家带来有关MySQL中如何使用Connection-Control插件，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

首先查看系统变量plugin_dir，找到插件(Plugins)所在的路径，如下所示

mysql>selectversion()fromdual;+-----------+|version()|+-----------+|8.0.18|+-----------+1rowinset(0.00sec)mysql>showvariableslike'plugin_dir';+---------------+--------------------------+|Variable_name|Value|+---------------+--------------------------+|plugin_dir|/usr/lib64/mysql/plugin/|+---------------+--------------------------+1rowinset(0.01sec)[root@KerryDB~]#cd/usr/lib64/mysql/plugin/[root@KerryDBplugin]#ls-lrttotal76388-rwxr-xr-x.1rootroot106696Sep202019rewrite_example.so-rwxr-xr-x.1rootroot104432Sep202019mysql_no_login.so-rwxr-xr-x.1rootroot111464Sep202019mypluglib.so-rwxr-xr-x.1rootroot106648Sep202019auth_socket.so-rwxr-xr-x.1rootroot163560Sep202019adt_null.so-rwxr-xr-x.1rootroot409032Sep202019validate_password.so-rwxr-xr-x.1rootroot9054776Sep202019libpluginmecab.so-rwxr-xr-x.1rootroot344696Sep202019authentication_ldap_sasl_client.so-rwxr-xr-x.1rootroot1145832Sep202019rewriter.so-rwxr-xr-x.1rootroot625944Sep202019ha_example.so-rwxr-xr-x.1rootroot388992Sep202019semisync_slave.so-rwxr-xr-x.1rootroot32368Sep202019component_log_sink_json.so-rwxr-xr-x.1rootroot235200Sep202019component_audit_api_message_emit.so-rwxr-xr-x.1rootroot494720Sep202019keyring_udf.so-rwxr-xr-x.1rootroot149280Sep202019component_log_sink_syseventlog.so-rwxr-xr-x.1rootroot1446024Sep202019semisync_master.so-rwxr-xr-x.1rootroot2277480Sep202019mysql_clone.so-rwxr-xr-x.1rootroot1231376Sep202019libmemcached.so-rwxr-xr-x.1rootroot454096Sep202019component_mysqlbackup.so-rwxr-xr-x.1rootroot193336Sep202019component_log_filter_dragnet.so-rwxr-xr-x.1rootroot1177352Sep202019ha_mock.so-rwxr-xr-x.1rootroot83936Sep202019locking_service.so-rwxr-xr-x.1rootroot1269784Sep202019connection_control.so-rwxr-xr-x.1rootroot1280936Sep202019innodb_engine.so-rwxr-xr-x.1rootroot442304Sep202019component_validate_password.so-rwxr-xr-x.1rootroot1206024Sep202019version_token.so-rwxr-xr-x.1rootroot2338880Sep202019keyring_file.so-rwxr-xr-x.1rootroot2031912Sep202019ddl_rewriter.so-rwxr-xr-x.1rootroot49246400Sep202019group_replication.sodrwxr-xr-x.2rootroot4096Nov62019debug

安装插件

mysql>INSTALLPLUGINCONNECTION_CONTROLSONAME'connection_control.so';QueryOK,0rowsaffected(0.02sec)mysql>INSTALLPLUGINCONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTSSONAME'connection_control.so';QueryOK,0rowsaffected(0.00sec)

检查是否安装成功

mysql>showplugins;mysql>SELECTPLUGIN_NAME,PLUGIN_LIBRARY,PLUGIN_STATUS,LOAD_OPTION->FROMINFORMATION_SCHEMA.PLUGINS->WHEREPLUGIN_LIBRARY='CONNECTION_CONTROL.SO';+------------------------------------------+-----------------------+---------------+-------------+|PLUGIN_NAME|PLUGIN_LIBRARY|PLUGIN_STATUS|LOAD_OPTION|+------------------------------------------+-----------------------+---------------+-------------+|CONNECTION_CONTROL|connection_control.so|ACTIVE|FORCE||CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS|connection_control.so|ACTIVE|FORCE|+------------------------------------------+-----------------------+---------------+-------------+2rowsinset(0.00sec)mysql>

设置系统变量

mysql>showvariableslike'connection_control%';+-------------------------------------------------+------------+|Variable_name|Value|+-------------------------------------------------+------------+|connection_control_failed_connections_threshold|3||connection_control_max_connection_delay|2147483647||connection_control_min_connection_delay|1000|+-------------------------------------------------+------------+

connection_control_failed_connections_threshold #登陆失败次数限制,默认值为3

connection_control_max_connection_delay #限制重试时间最大值，单位为毫秒( milliseconds)，默认值2147483647

connection_control_min_connection_delay #限制重试时间最小值，单位为毫秒( milliseconds)，默认值为1000毫秒，也就是1秒

注意事项：

1：connection_control_min_connection_delay的值必须小于connection_control_max_connection_delay，connection_control_max_connection_delay不能小于connection_control_min_connection_delay的值。

mysql>setglobalconnection_control_min_connection_delay=60000;QueryOK,0rowsaffected(0.00sec)

注意，命令方式设置全局系统变量在服务器重启后丢失，所以最好的方式在参数文件my.cnf设置全局系统变量

-- 配置文件增加以下配置

[mysqld]plugin-load-add=connection_control.so#不是必须connection-control=FORCE#不是必须connection-control-failed-login-attempts=FORCE#不是必须connection_control_min_connection_delay=60000connection_control_max_connection_delay=1800000connection_control_failed_connections_threshold=3

三次连续输错密码后，就会在第四次输入密码后挂起

[root@lnx02~]#mysql-h10.20.57.24-utest-pEnterpassword:ERROR1045(28000):Accessdeniedforuser'test'@'192.168.27.180'(usingpassword:YES)[root@lnx02~]#mysql-h10.20.57.24-utest-pEnterpassword:ERROR1045(28000):Accessdeniedforuser'test'@'192.168.27.180'(usingpassword:YES)[root@lnx02~]#mysql-h10.20.57.24-utest-pEnterpassword:ERROR1045(28000):Accessdeniedforuser'test'@'192.168.27.180'(usingpassword:YES)[root@lnx02~]#mysql-h10.20.57.24-utest-pEnterpassword:

注意，MySQL服务重启过后，INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS中的数据全部前空。

The CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin must be activated for this table to be available, and the CONNECTION_CONTROL plugin must be activated or the table contents will always be empty. See Section 6.4.2, “The Connection-Control Plugins”.

必须激活CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件才能使用该表CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS，并且要么激活CONNECTION_CONTROL插件，要么该表的内容始终为空。请参见第6.4.2节“连接控制插件”。

The table contains rows only for clients that have had one or more consecutive failed connection attempts without a subsequent successful attempt. When a client connects successfully, its failed-connection count is reset to zero and the server removes any row corresponding to the client.

该表仅包含已进行一次或多次连续失败连接尝试而没有随后成功尝试的客户端的行。当客户端成功连接时，其失败连接计数将重置为零，并且服务器将删除与该客户端对应的任何行。

Assigning a value to the connection_control_failed_connections_threshold system variable at runtime resets all accumulated failed-connection counters to zero, which causes the table to become empty.

在运行时为connection_control_failed_connections_threshold系统变量分配一个值会将所有累积的失败连接计数器重置为零，这将导致表变空。

解除账号延迟响应限制

方法1：重启MySQL实例

方法2：调整系统变量connection_control_failed_connections_threshold的值。

mysql>SELECT*FROM->INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;+-------------------+-----------------+|USERHOST|FAILED_ATTEMPTS|+-------------------+-----------------+|'test'@'192.168%'|5|+-------------------+-----------------+1rowinset(0.00sec)mysql>mysql>setglobalconnection_control_failed_connections_threshold=2;QueryOK,0rowsaffected(0.00sec)mysql>SELECT*FROM->INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;Emptyset(0.00sec)

3：卸载插件plugin

mysql>UNINSTALLPLUGINCONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;mysql>UNINSTALLPLUGINCONNECTION_CONTROL;

注意事项：

关于Connection-Control的实现原理，淘宝数据库内核月报有篇文章分析过代码，介绍过实现原理，Connection-Control插件虽然可以防止恶意暴力破解MySQL账户，但是可能会浪费MySQL的资源;

比如如果短时间内有大量的恶意攻击，该插件虽然可以防止破解MySQL账户，但是会消耗主机资源(每一个连接创建一个线程);

如果这里使用了线程池，虽然可以避免消耗主机资源，但是等线程池中的线程被消耗光，再有新连接来就会拒绝服务。

上述就是小编为大家分享的MySQL中如何使用Connection-Control插件了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。