本文转载自微信公众号《DBA的杂念》，作者潇湘隐士。转载本文请联系DBA杂念公众号。在Oracle和SQLServer数据库中，可以设置一些复杂的账号和密码策略，比如超过N次登录失败就锁定账号，那么MySQL中有没有这样的功能呢？答案是MySQL也有类似的功能。只是在MySQL中，登录失败超过阈值后，会延迟响应时间，而不是锁定账户。MySQL5.6.35之后，提供了Connection-Control插件，用于客户端连续登录失败一定次数后进行控制。响应延迟。本插件可有效防止客户端暴力登录的风险（攻击）。该插件包含以下两个组件CONNECTION_CONTROL：用于控制登录失败次数和延迟响应时间CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS：该表将失败的登录操作记录到INFORMATION_SCHEMA库中。让我们介绍一下Connection-ControlPlugins。下面实验的版本是MySQL8.0.18。首先查看系统变量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-xr-x.16root11mysql_no_login.so-rwxr-xr-xr-xr-xr-x.1rootroot19rwxr-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';+-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|+----------------------------------------+--------------------------+----------------+------------+2行插图(0.00sec)mysql>setsystemvariablesmysql>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#登录失败限制，默认值为3connection_control_max_connection_delay#限制最大重试时间，单位为毫秒（毫秒），默认值为2147483647connection_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#Notrequiredconnection-control=FORCE#Notrequiredconnection-control-failed-login-attempts=FORCE#Notrequiredconnection_control_min_connection_delay=60000connection_control_max_connection_delayres=1800000connection_control_failed-login-attempts=FORCE#connection_control_min_connection_delay=60000connection_control_max_connection_delayres=1800000connection_control_failed-login-attempts=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):拒绝用户'test'@'192.168.27.180'(usingpassword:YES)[root@lnx02~]#mysql-h10.20.57.24-utest-pEnterpassword:注意，MySQL服务重启后，INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS中的数据之前都是空的。必须激活CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件才能使该表可用，并且必须激活CONNECTION_CONTROL插件，否则表内容将始终为空。请参阅第6.4.2节，“连接控制插件”。必须激活CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件才能使用表CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS，并且激活CONNECTION_CONTROL插件或表的内容始终为空。请参阅第6.4.2节，“连接控制插件”。该表仅包含具有一次或多次连续失败的连接尝试而没有后续成功尝试的客户端的行。当客户端成功连接时，其失败连接计数将重置为零，并且服务器会删除与客户端对应的任何行。该表仅包含已进行一次或多次连续失败的连接尝试而没有后续成功尝试的客户端的行。当客户端成功连接时，其失败连接计数将重置为零，并且服务器会删除与该客户端对应的所有行。在运行时为connection_control_failed_connections_threshold系统变量赋值会将所有累积的失败连接计数器重置为零，这会导致表变为空，这将导致表格变空。方法一：重启MySQL实例方法二：调整系统变量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：卸载插件pluginmysql>UNINSTALLPLUGINCONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;mysql>UNINSTALLPLUGINCONNECTION_CONTROL;注意事项：关于Connection-Control淘宝数据库内核实现原理月刊有一篇分析代码，介绍实现原理。Connection-Control插件虽然可以防止恶意暴力破解MySQL账号，??但可能会浪费MySQL资源；例如，如果有大量的恶意攻击，虽然这个插件可以防止破解MySQL账户，但是会消耗主机资源（每个连接创建一个线程）；如果这里使用线程池，虽然可以避免消耗主机资源，但是等到线程池中的线程被消耗完，如果有新的连接，就会拒绝服务。参考：https://dev.mysql.com/doc/refman/5.7/en/connection-control-installation.htmlhttps://dev.mysql.com/doc/refman/5.7/en/connection-control-failed-登录尝试表.htmlhttps://dev.mysql.com/doc/refman/5.7/en/connection-control-variables.html
