Movatterモバイル変換

MySQL 9.2 Reference Manual

8.4.2.2 Connection Control Component Configuration

The Connection Control component exposes the following system variables:

connection_control_failed_connections_threshold: The number of consecutive failed connection attempts permitted to accounts before the server adds a delay for subsequent connection attempts. To disable failed-connection counting, setconnection_control_failed_connections_threshold to zero.
connection_control_min_connection_delay: The minimum delay in milliseconds for connection failures above the threshold.
connection_control_max_connection_delay: The maximum delay in milliseconds for connection failures above the threshold.

component_connection_control.failed_connections_threshold: This is the number of consecutive failed connection attempts by a given account which are allowed before the server adds a delay for subsequent connection attempts by this user. To disable counting of failed connections, set this variable to zero.
component_connection_control.max_connection_delay: The maximum delay in milliseconds for connection failures above the threshold.
component_connection_control.min_connection_delay: The minimum delay in milliseconds for connection failures above the threshold.

Ifcomponent_connection_control.failed_connections_threshold is greater than zero, counting of failed connections and thus connection control is enabled, and applies as follows for each user account:

The firstcomponent_connection_control.failed_connections_threshold consecutive times that this account fails to connect, no action is taken.
For each subsequent attempt by this user to connect, the server adds an increasing delay, until a successful connection occurs. Initial unadjusted delays begin at 1000 milliseconds (1 second) and increase by 1000 milliseconds per attempt. That is, once a delay has been imposed for a given account, the unadjusted delays for subsequent failed attempts are 1000 milliseconds, 2000 milliseconds, 3000 milliseconds, and so forth.
The actual delay experienced by a client is the unadjusted delay, adjusted to lie within the values of thecomponent_connection_control.min_connection_delay andcomponent_connection_control.max_connection_delay system variables, inclusive.
For example, assuming thatcomponent_connection_control.failed_connections_threshold is the default (3): Ifcomponent_connection_control.min_connection_delay is 3000 andcomponent_connection_control.max_connection_delay is 6000, then the delay for each failed attempt to connect is as shown in the following table:
Attempt # Unadjusted Delay (milliseconds) Actual Delay (milliseconds)
1 0 0
2 0 0
3 0 0
4 1000 3000
5 2000 3000
6 3000 3000
7 4000 4000
8 5000 5000
9 6000 6000
10 7000 6000
11 8000 6000
12 8000 6000
Once delays have been instituted for an account, the first successful connection thereafter by that account also experiences a delay, but the failure count is reset to zero for subsequent connections by this account.

Attempt #	Unadjusted Delay (milliseconds)	Actual Delay (milliseconds)
1	0	0
2	0	0
3	0	0
4	1000	3000
5	2000	3000
6	3000	3000
7	4000	4000
8	5000	5000
9	6000	6000
10	7000	6000
11	8000	6000
12	8000	6000

The Connection Control component also exposes a status variable;Component_connection_control_delay_generated is the number of times the server has added a delay to its response to a failed connection attempt. This does not count attempts that occur before reaching the limit set by thecomponent_connection_control.failed_connections_threshold system variable, since no delay was imposed for these attempts.

This variable provides a simple counter. You can obtain more detailed connection control monitoring information from the Performance Schema theconnection_control_failed_login_attempts table.

Assigning a value tocomponent_connection_control.failed_connections_threshold at runtime resetsComponent_connection_control_delay_generated to zero.

When thecomponent_connection_control component is installed, it checks connection attempts and tracks whether they fail or succeed. For this purpose, a failed connection attempt is one for which the client user and host match a known MySQL account but the provided credentials are incorrect, or do not match any known account.

Proxies. Counting of failed connection attempts is based on the combination of user name and host name (user@host) used for each connection attempt. Determination of the applicable user name and host name takes proxying into account, as follows:

If the client user proxies another user, the account for failed-connection counting is the proxying user, not the proxied user. For example, ifexternal_user@example.com proxiesproxy_user@example.com, connection counting uses the proxying user,external_user@example.com, rather than the proxied user,proxy_user@example.com. Bothexternal_user@example.com andproxy_user@example.com must have valid entries in themysql.user system table and a proxy relationship between them must be defined in themysql.proxies_priv system table (seeSection 8.2.19, “Proxy Users”).
If the client user does not proxy another user, but does match amysql.user entry, counting uses theCURRENT_USER() value corresponding to that entry. For example, if a useruser1 connecting from a hosthost1.example.com matches auser1@host1.example.com entry, counting usesuser1@host1.example.com. If the user matches auser1@%.example.com,user1@%.com, oruser1@% entry instead, counting usesuser1@%.example.com,user1@%.com, oruser1@%, respectively.

For the cases just described, the connection attempt matches somemysql.user entry, and whether the request succeeds or fails depends on whether the client provides the correct authentication credentials. For example, if the client presents an incorrect password, the connection attempt fails.

If the connection attempt matches nomysql.user entry, the attempt fails. In this case, noCURRENT_USER() value is available and connection-failure counting uses the user name provided by the client and the client host as determined by the MySQL server. For example, if a client attempts to connect as useruser2 from hosthost2.example.com, the user name part is available in the client request and the server determines the host information. The user/host combination used for counting isuser2@host2.example.com.

Note

The MySQL server maintains information about which client hosts can possibly connect to the server (essentially the union of host values formysql.user entries). If a client attempts to connect from any other host, the server rejects the attempt at an early stage of connection setup:

ERROR 1130 (HY000): Host 'host_name' is notallowed to connect to this MySQL server

This type of rejection occurs before password authentication is attempted; thus, the Connection Control component does not see it, and it is not included in the count shown byComponent_connection_control_delay_generated or in theperformance_schema.connection_control_failed_login_attempts table.

Failure monitoring. You can use the following information sources to monitor failed connections:

Component_connection_control_delay_generated: This server status variable indicates the number of times the server has added a delay to its response to a failed connection attempt, not counting attempts that occur before reaching the limit determined bycomponent_connection_control.failed_connections_threshold.
connection_control_failed_login_attempts: This Performance Schema table provides the current number of consecutive failed connection attempts per MySQL user account (that is, for each combination of user and host). This count includes all failed attempts, regardless of whether they were delayed.

Assigning a value tocomponent_connection_control.failed_connections_threshold at runtime has the effects listed here:

All accumulated failed-connection counters are reset to zero.
Component_connection_control_delay_generated is reset to zero.
Theperformance_schema.connection_control_failed_login_attempts table becomes empty.

PREV HOME UP NEXT