MySQL 9.2 Release Notes
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_thresholdto 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.
If
component_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 first
component_connection_control.failed_connections_thresholdconsecutive 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 the
component_connection_control.min_connection_delayandcomponent_connection_control.max_connection_delaysystem variables, inclusive.For example, assuming that
component_connection_control.failed_connections_thresholdis the default (3): Ifcomponent_connection_control.min_connection_delayis 3000 andcomponent_connection_control.max_connection_delayis 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.
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 the
component_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 the
connection_control_failed_login_attempts
table.
Assigning a value to
component_connection_control.failed_connections_threshold
at runtime resets
Component_connection_control_delay_generated
to zero.
When the component_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
If the client user proxies another user, the account for failed-connection counting is the proxying user, not the proxied user. For example, if
[email protected]proxies[email protected], connection counting uses the proxying user,[email protected], rather than the proxied user,[email protected]. Both[email protected]and[email protected]must have valid entries in themysql.usersystem table and a proxy relationship between them must be defined in themysql.proxies_privsystem table (see Section 8.2.19, “Proxy Users”).If the client user does not proxy another user, but does match a
mysql.userentry, counting uses theCURRENT_USER()value corresponding to that entry. For example, if a useruser1connecting from a hosthost1.example.commatches a[email protected]entry, counting uses[email protected]. 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
some mysql.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 no
mysql.user entry, the attempt fails. In this
case, no CURRENT_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
user user2 from host
host2.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 is
[email protected].
Note
The MySQL server maintains information about which client
hosts can possibly connect to the server (essentially the
union of host values for mysql.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 not
allowed 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 by
Component_connection_control_delay_generated
or in the
performance_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 to
component_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_generatedis reset to zero.The
performance_schema.connection_control_failed_login_attemptstable becomes empty.