mysql参数之max_connect_errors

mysql参数之max_connect_errors

指定允许连接不成功的最大尝试次数。5.7默认是100；如果到达这个数，那么服务器将不再允许新的连接，即便mysql仍正常对外提供服务。所以可以将这个参数设置为几万。

mysql> show global variables like 'max_connect%';

+--------------------+-------+

| Variable_name      | Value |

+--------------------+-------+

| max_connect_errors | 100   |

| max_connections    | 151   |

+--------------------+-------+

2 rows in set (0.00 sec)

 

模拟超过100次错误连接

root@bd-dev-mingshuo-182:~#declare i=1

root@bd-dev-mingshuo-182:~#while [ $i -le 110 ]

> do

> mysql -h172.31.217.183 -P 3306 -poracle123;

> let i=i+1

> done

 

预计此时mysql应该无法连接

root@bd-dev-mingshuo-183:~#mysql -S /u01/mysql/3306/data/mysql.sock -poracle

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 7817

Server version: 5.7.23-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2018, 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.

 

mysql>

但是成功连接上了。

 

 

mysql> select * from performance_schema.host_cache\G

*************************** 1. row ***************************

IP: 172.31.217.182

HOST: bd-dev-mingshuo-182

HOST_VALIDATED: YES

SUM_CONNECT_ERRORS: 0

COUNT_HOST_BLOCKED_ERRORS: 0

COUNT_NAMEINFO_TRANSIENT_ERRORS: 0

COUNT_NAMEINFO_PERMANENT_ERRORS: 0

COUNT_FORMAT_ERRORS: 0

COUNT_ADDRINFO_TRANSIENT_ERRORS: 0

COUNT_ADDRINFO_PERMANENT_ERRORS: 0

COUNT_FCRDNS_ERRORS: 0

COUNT_HOST_ACL_ERRORS: 0

COUNT_NO_AUTH_PLUGIN_ERRORS: 0

COUNT_AUTH_PLUGIN_ERRORS: 0

COUNT_HANDSHAKE_ERRORS: 0

COUNT_PROXY_USER_ERRORS: 0

COUNT_PROXY_USER_ACL_ERRORS: 0

COUNT_AUTHENTICATION_ERRORS: 110

COUNT_SSL_ERRORS: 0

COUNT_MAX_USER_CONNECTIONS_ERRORS: 0

COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0

COUNT_DEFAULT_DATABASE_ERRORS: 0

COUNT_INIT_CONNECT_ERRORS: 0

COUNT_LOCAL_ERRORS: 0

COUNT_UNKNOWN_ERRORS: 0

FIRST_SEEN: 2018-09-18 11:39:33

LAST_SEEN: 2018-09-21 10:03:37

FIRST_ERROR_SEEN: 2018-09-21 10:03:37

LAST_ERROR_SEEN: 2018-09-21 10:03:37

1 row in set (0.00 sec)

 

 

可以看到SUM_CONNECT_ERRORS值为0.说明max_connect_errors在判定上与我目前认为的不一致。

COUNT_AUTHENTICATION_ERRORS这个值是110。密码错误认证失败次数。

 

这里可以提出一个问题，哪个参数到达max_connect_errors后会拒绝客户端连接呢？

后面实验会找出这个指标。

 

host cache的含义：

mysql服务器内存中有一块区域，里面记录了客户端信息，比如IP地址，主机名和错误信息。host cache的作用：

1.缓存主机名-IP的解析结果。这样可以有效防止每一次客户端连接的DNS解析，对于一个客户端，解析过一次以后，后续连接就不用再去解析了。

2.缓存中记录了连接过程中发生的错误。如果从一个指定客户端突然发起了很多的连接但是没有一次成功，那么服务器会阻止从这个客户端发起的后续连接。失败连接的次数就是max_connect_errors参数指定的。

服务器只在非本地TCP连接下才会使用host cache。本地回环地址连接，socket，命名管道等其他连接方式不会使用该内存区域。

该内存区域可以用performance_schema.host_cache表查看

 

 

该表的列的含义如下：

The host_cache table has these columns:

 

IP

The IP address of the client that connected to the server, expressed as a string.

 

HOST

The resolved DNS host name for that client IP, or NULL if the name is unknown.

 

HOST_VALIDATED

Whether the IP-to-host name-to-IP DNS resolution was performed successfully for the client IP. If HOST_VALIDATED is YES, the HOST column is used as the host name corresponding to the IP so that calls to DNS can be avoided. While HOST_VALIDATED is NO, DNS resolution is attempted again for each connection attempt, until it eventually completes with either a valid result or a permanent error. This information enables the server to avoid caching bad or missing host names during temporary DNS failures, which would affect clients forever.

 

SUM_CONNECT_ERRORS

The number of connection errors that are deemed “blocking” (assessed against the max_connect_errors system variable). Only protocol handshake errors are counted, and only for hosts that passed validation (HOST_VALIDATED = YES).

 

COUNT_HOST_BLOCKED_ERRORS

The number of connections that were blocked because SUM_CONNECT_ERRORS exceeded the value of the max_connect_errors system variable.

 

COUNT_NAMEINFO_TRANSIENT_ERRORS

The number of transient errors during IP-to-host name DNS resolution.

 

COUNT_NAMEINFO_PERMANENT_ERRORS

The number of permanent errors during IP-to-host name DNS resolution.

 

COUNT_FORMAT_ERRORS

The number of host name format errors. MySQL does not perform matching of Host column values in the mysql.user table against host names for which one or more of the initial components of the name are entirely numeric, such as 1.2.example.com. The client IP address is used instead. For the rationale why this type of matching does not occur, see Section 6.2.3, “Specifying Account Names”.

 

COUNT_ADDRINFO_TRANSIENT_ERRORS

The number of transient errors during host name-to-IP reverse DNS resolution.

 

COUNT_ADDRINFO_PERMANENT_ERRORS

The number of permanent errors during host name-to-IP reverse DNS resolution.

 

COUNT_FCRDNS_ERRORS

The number of forward-confirmed reverse DNS errors. These errors occur when IP-to-host name-to-IP DNS resolution produces an IP address that does not match the client originating IP address.

 

COUNT_HOST_ACL_ERRORS

The number of errors that occur because no users are permitted to connect from the client host. In such cases, the server returns ER_HOST_NOT_PRIVILEGED and does not even ask for a user name or password.

 

COUNT_NO_AUTH_PLUGIN_ERRORS

The number of errors due to requests for an unavailable authentication plugin. A plugin can be unavailable if, for example, it was never loaded or a load attempt failed.

 

COUNT_AUTH_PLUGIN_ERRORS

The number of errors reported by authentication plugins.

 

An authentication plugin can report different error codes to indicate the root cause of a failure. Depending on the type of error, one of these columns is incremented: COUNT_AUTHENTICATION_ERRORS, COUNT_AUTH_PLUGIN_ERRORS, COUNT_HANDSHAKE_ERRORS. New return codes are an optional extension to the existing plugin API. Unknown or unexpected plugin errors are counted in the COUNT_AUTH_PLUGIN_ERRORS column.

 

COUNT_HANDSHAKE_ERRORS

The number of errors detected at the wire protocol level.

 

COUNT_PROXY_USER_ERRORS

The number of errors detected when proxy user A is proxied to another user B who does not exist.

 

COUNT_PROXY_USER_ACL_ERRORS

The number of errors detected when proxy user A is proxied to another user B who does exist but for whom A does not have the PROXY privilege.

 

COUNT_AUTHENTICATION_ERRORS

The number of errors caused by failed authentication.

 

COUNT_SSL_ERRORS

The number of errors due to SSL problems.

 

COUNT_MAX_USER_CONNECTIONS_ERRORS

The number of errors caused by exceeding per-user connection quotas. See Section 6.3.5, “Setting Account Resource Limits”.

 

COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS

The number of errors caused by exceeding per-user connections-per-hour quotas. See Section 6.3.5, “Setting Account Resource Limits”.

 

COUNT_DEFAULT_DATABASE_ERRORS

The number of errors related to the default database. For example, the database did not exist or the user had no privileges for accessing it.

 

COUNT_INIT_CONNECT_ERRORS

The number of errors caused by execution failures of statements in the init_connect system variable value.

 

COUNT_LOCAL_ERRORS

The number of errors local to the server implementation and not related to the network, authentication, or authorization. For example, out-of-memory conditions fall into this category.

 

COUNT_UNKNOWN_ERRORS

The number of other, unknown errors not accounted for by other columns in this table. This column is reserved for future use, in case new error conditions must be reported, and if preserving the backward compatibility and table structure of the host_cache table is required.

 

FIRST_SEEN

The timestamp of the first connection attempt seen from the client in the IP column.

 

LAST_SEEN

The timestamp of the last connection attempt seen from the client in the IP column.

 

FIRST_ERROR_SEEN

The timestamp of the first error seen from the client in the IP column.

 

LAST_ERROR_SEEN

The timestamp of the last error seen from the client in the IP column.

 

从SUM_CONNECT_ERRORS列的含义来看，只有host_validated列为yes(代表这个解析结果是合理的)，并且TCP/IP握手过程发生错误才会被记录。

telnet是TCP/IP协议的一员，可以用telnet模拟失败连接。

 

 

root@bd-dev-mingshuo-182:~#declare i=1

root@bd-dev-mingshuo-182:~#while [ $i -le 110 ]

> do

> telnet 172.31.217.183 3306

> let i=i+1

> done

这个太慢了，直接将max_connect_errors设置为3

set global max_connect_errors=3;

此时：

mysql> select * from performance_schema.host_cache\G

*************************** 1. row ***************************

IP: 172.31.217.182

HOST: bd-dev-mingshuo-182

HOST_VALIDATED: YES

SUM_CONNECT_ERRORS: 10

COUNT_HOST_BLOCKED_ERRORS: 0

COUNT_NAMEINFO_TRANSIENT_ERRORS: 0

COUNT_NAMEINFO_PERMANENT_ERRORS: 0

COUNT_FORMAT_ERRORS: 0

COUNT_ADDRINFO_TRANSIENT_ERRORS: 0

COUNT_ADDRINFO_PERMANENT_ERRORS: 0

COUNT_FCRDNS_ERRORS: 0

COUNT_HOST_ACL_ERRORS: 0

COUNT_NO_AUTH_PLUGIN_ERRORS: 0

COUNT_AUTH_PLUGIN_ERRORS: 0

COUNT_HANDSHAKE_ERRORS: 10

COUNT_PROXY_USER_ERRORS: 0

COUNT_PROXY_USER_ACL_ERRORS: 0

COUNT_AUTHENTICATION_ERRORS: 110

COUNT_SSL_ERRORS: 0

COUNT_MAX_USER_CONNECTIONS_ERRORS: 0

COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0

COUNT_DEFAULT_DATABASE_ERRORS: 0

COUNT_INIT_CONNECT_ERRORS: 0

COUNT_LOCAL_ERRORS: 0

COUNT_UNKNOWN_ERRORS: 0

FIRST_SEEN: 2018-09-18 11:39:33

LAST_SEEN: 2018-09-21 11:15:14

FIRST_ERROR_SEEN: 2018-09-21 10:03:37

LAST_ERROR_SEEN: 2018-09-21 11:15:22

1 row in set (0.00 sec)

可以看到刚刚模拟的时候已经产生了10次握手错误：

COUNT_HANDSHAKE_ERRORS=10

SUM_CONNECT_ERRORS: 10

110次是刚刚模拟错误密码登录产生的。

COUNT_AUTHENTICATION_ERRORS: 110

 

下面进行三次telnet

root@bd-dev-mingshuo-182:~# telnet 172.31.217.183 3306

Trying 172.31.217.183...

Connected to 172.31.217.183.

Escape character is '^]'.

lHost '172.31.217.182' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'Connection closed by foreign host.

发现此时已经被block了。

 

mysql连接：

root@bd-dev-mingshuo-182:~#mysql -h172.31.217.183 -P 3306 -poracle

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1129 (HY000): Host '172.31.217.182' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

已经无法连接了。说明不管之前数据库参数是如何设置的。重新设置max_connect_errors参数后，之前的登录统计信息不会归零计算，会在之前的基础上进行验证。之前累计握手错误已经10次了，将max_connect_errors设为3，比之前记录到的10次小了，所以直接无法登录了。

 

mysql> select * from performance_schema.host_cache\G

*************************** 1. row ***************************

IP: 172.31.217.182

HOST: bd-dev-mingshuo-182

HOST_VALIDATED: YES

SUM_CONNECT_ERRORS: 10

COUNT_HOST_BLOCKED_ERRORS: 3

COUNT_NAMEINFO_TRANSIENT_ERRORS: 0

COUNT_NAMEINFO_PERMANENT_ERRORS: 0

COUNT_FORMAT_ERRORS: 0

COUNT_ADDRINFO_TRANSIENT_ERRORS: 0

COUNT_ADDRINFO_PERMANENT_ERRORS: 0

COUNT_FCRDNS_ERRORS: 0

COUNT_HOST_ACL_ERRORS: 0

COUNT_NO_AUTH_PLUGIN_ERRORS: 0

COUNT_AUTH_PLUGIN_ERRORS: 0

COUNT_HANDSHAKE_ERRORS: 10

COUNT_PROXY_USER_ERRORS: 0

COUNT_PROXY_USER_ACL_ERRORS: 0

COUNT_AUTHENTICATION_ERRORS: 110

COUNT_SSL_ERRORS: 0

COUNT_MAX_USER_CONNECTIONS_ERRORS: 0

COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0

COUNT_DEFAULT_DATABASE_ERRORS: 0

COUNT_INIT_CONNECT_ERRORS: 0

COUNT_LOCAL_ERRORS: 0

COUNT_UNKNOWN_ERRORS: 0

FIRST_SEEN: 2018-09-18 11:39:33

LAST_SEEN: 2018-09-21 11:21:10

FIRST_ERROR_SEEN: 2018-09-21 10:03:37

LAST_ERROR_SEEN: 2018-09-21 11:21:10

1 row in set (0.00 sec)

COUNT_HOST_BLOCKED_ERRORS值为3，代表超过max_connect_errors之后已经阻止了3次客户端连接了。

 

现在将max_connect_errors改为12

set global max_connect_errors=12;

 

182主机又可以登录了。

root@bd-dev-mingshuo-182:~#mysql -h172.31.217.183 -P 3306 -poracle

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 7941

Server version: 5.7.23-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2018, 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.

 

mysql>

 

 

现在继续模拟握手失败

mysql> select * from performance_schema.host_cache\G

*************************** 1. row ***************************

IP: 172.31.217.182

HOST: bd-dev-mingshuo-182

HOST_VALIDATED: YES

SUM_CONNECT_ERRORS: 1

COUNT_HOST_BLOCKED_ERRORS: 3

COUNT_NAMEINFO_TRANSIENT_ERRORS: 0

COUNT_NAMEINFO_PERMANENT_ERRORS: 0

COUNT_FORMAT_ERRORS: 0

COUNT_ADDRINFO_TRANSIENT_ERRORS: 0

COUNT_ADDRINFO_PERMANENT_ERRORS: 0

COUNT_FCRDNS_ERRORS: 0

COUNT_HOST_ACL_ERRORS: 0

COUNT_NO_AUTH_PLUGIN_ERRORS: 0

COUNT_AUTH_PLUGIN_ERRORS: 0

COUNT_HANDSHAKE_ERRORS: 11

COUNT_PROXY_USER_ERRORS: 0

COUNT_PROXY_USER_ACL_ERRORS: 0

COUNT_AUTHENTICATION_ERRORS: 110

COUNT_SSL_ERRORS: 0

COUNT_MAX_USER_CONNECTIONS_ERRORS: 0

COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0

COUNT_DEFAULT_DATABASE_ERRORS: 0

COUNT_INIT_CONNECT_ERRORS: 0

COUNT_LOCAL_ERRORS: 0

COUNT_UNKNOWN_ERRORS: 0

FIRST_SEEN: 2018-09-18 11:39:33

LAST_SEEN: 2018-09-21 11:27:48

FIRST_ERROR_SEEN: 2018-09-21 10:03:37

LAST_ERROR_SEEN: 2018-09-21 11:27:58

1 row in set (0.00 sec)

可以看到SUM_CONNECT_ERRORS的值是1

也就说，在刚刚的成功登录之后，SUM_CONNECT_ERRORS才会被归零，重新计算连接失败数。

而COUNT_HANDSHAKE_ERRORS还在继续累计。

上述过程证明了SUM_CONNECT_ERRORS才是max_connect_errors的最大临界值。

 

模拟出无法登录

set global max_connect_errors=2;

telnet 172.31.217.183 3306

telnet 172.31.217.183 3306

root@bd-dev-mingshuo-182:~#mysql -h172.31.217.183 -P 3306 -poracle

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1129 (HY000): Host '172.31.217.182' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

 

解决方案

1.调大max_connect_errors参数

2.mysqladmin flush-hosts

这种方法不好的地方在于，会flush host cache内存区域。类似oracle的flush shared_pool这种命令。这会影响其他的客户端程序。所有客户端连接时不得不重新去解析。

3.将host_cache_size设为0.本质上就是禁掉host cache内存区域的使用。

4.设置参数--skip-name-resolve关闭DNS解析。不过这样客户端将不能再用机器名连接，只能用IP地址了。

5.设置参数--skip-networking关闭TCP/IP连接方式。前面说过host cache区域使用条件之一就是用TCP/IP的连接方式，这是从源头上就cut掉问题。

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2214728/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31480688/viewspace-2214728/