CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS

• 该表提供查询有关客户端连续失败连接尝试次数的信息，该表在MySQL 5.7.17中新增
• 表结构定义

CREATE TEMPORARY TABLE `CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS` (
  `USERHOST` varchar(162) NOT NULL DEFAULT '',
  `FAILED_ATTEMPTS` int(16) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

• 表字段含义

• USERHOST：'user_name'@'host_name'格式记录的连接尝试连接失败的客户端的用户/主机组合(即为帐号名称)
• FAILED_ATTEMPTS：USERHOST列值表示的用户对应的当前连续尝试连接失败的次数。该列值计算的是所有失败的连接尝试，不管它们的连接是否被延迟。服务器是否为失败连接增加延迟，要看系统变量connection_control_failed_connections_threshold如何设置（当connection_control_failed_connections_threshold设置为0时，服务器将关闭连接增加延迟功能，同时也关闭了失败的客户端连接计数，当该变量设置为非0值时，该变量指定的数字表示客户端连续连接失败该数值之后，下一次尝试连接时将增加延迟）

• 表记录内容示例

# 先在一台客户端主机上随意输错帐号或者密码连续4次，你会发现第四次开始连接被延迟了
[root@localhost ~]# time mysql -uadmin -pletsg
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.014s
user    0m0.010s
sys    0m0.003s
[root@localhost ~]# time mysql -uadmin -pletsg
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.013s
user    0m0.010s
sys    0m0.002s
[root@localhost ~]# time mysql -uadmin -pletsg
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m0.012s
user    0m0.008s
sys    0m0.003s
[root@localhost ~]# time mysql -uadmin -pletsg
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

real    0m1.013s
user    0m0.005s
sys    0m0.007s

# 此时，查看表中的记录内容
root@localhost : information_schema 12:47:01> select * from CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+-------------+-----------------+
| USERHOST    | FAILED_ATTEMPTS |
+-------------+-----------------+
| 'admin'@'%' |               4 |
+-------------+-----------------+
1 row in set (0.00 sec)

# 输入正确的帐号和密码
[root@localhost ~]# time mysql -uadmin -pletsg0
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.20-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

admin@localhost : (none) 12:48:04> 

# 再次查看表内容，发现记录被删除了
root@localhost : information_schema 12:47:45> select * from CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
Empty set (0.00 sec)

• PS：

• CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS表需要激活CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件才可见，要激活CONNECTION_CONTROL插件才会记录内容
• 此表记录的为连续失败连接的客户端统计次数，当客户端有一次尝试连接成功时，对应的客户端记录将被删除
• 在server运行期间动态修改connection_control_failed_connections_threshold系统变量，将导致所有客户端的计数被删除
• 该表中记录的是没有匹配到正确的帐号名或者密码的客户端（但在mysql.user表中有正确的host记录的），如果客户端在mysql.user表中没有正确的host记录，则CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS表中不会记录任何信息（因为server在更早的阶段已经拒绝了该客户端的连接，此时该客户端对于该表不可见）
• 该information_schema表需要额外安装插件才能使用，参考链接：https://dev.mysql.com/doc/refman/5.7/en/connection-control-installation.html

---

上一篇：TP_THREAD_STATE表 |下一篇：information_schema详解 附录