This section describes how to install the connection-control
general information about installing plugins, see
Installing and Uninstalling Plugins.
To be usable by the server, the plugin library file must be
located in the MySQL plugin directory (the directory named by
variable). If necessary, configure the plugin directory location
by setting the value of
plugin_dir at server startup.
The plugin library file base name is
connection_control. The file name suffix
differs per platform (for example,
Unix and Unix-like systems,
To load the plugins at server startup, use the
--plugin-load-add option to name
the library file that contains them. With this plugin-loading
method, the option must be given each time the server starts.
For example, put these lines in the server
my.cnf file (adjust the
.so suffix for your platform as necessary):
my.cnf, restart the server
to cause the new settings to take effect.
Alternatively, to register the plugins at runtime, use these
statements (adjust the
.so suffix as
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so'; INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
INSTALL PLUGIN loads the plugin
immediately, and also registers it in the
mysql.plugins system table to cause the
server to load it for each subsequent normal startup.
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'connection%'; +------------------------------------------+---------------+ | PLUGIN_NAME | PLUGIN_STATUS | +------------------------------------------+---------------+ | CONNECTION_CONTROL | ACTIVE | | CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE | +------------------------------------------+---------------+
If a plugin fails to initialize, check the server error log for diagnostic messages.
If the plugins have been previously registered with
INSTALL PLUGIN or are loaded with
--plugin-load-add, you can use
options at server startup to control plugin activation. For
example, to load the plugins at startup and prevent them from
being removed at runtime, use these options:
[mysqld] plugin-load-add=connection_control.so connection-control=FORCE_PLUS_PERMANENT connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
If it is desired to prevent the server from running without a
given connection-control plugin, use an option value of
FORCE_PLUS_PERMANENT to force server startup
to fail if the plugin does not initialize successfully.
It is possible to install one plugin without the other, but
both must be installed for full connection-control capability.
In particular, installing only the
plugin is of little use because without the
CONNECTION_CONTROL plugin to provide the
data that populates the
table, retrievals from the table will always be empty.
To enable you to configure its operation, the
CONNECTION_CONTROL plugin exposes several
connection_control_failed_connections_threshold: The number of consecutive failed connection attempts permitted to clients before the server adds a delay for subsequent connection attempts.
connection_control_min_connection_delay: The amount of delay to add for each consecutive connection failure above the threshold.
connection_control_max_connection_delay: The maximum delay to add.
To entirely disable checking for failed connection attempts,
to zero. If
is nonzero, the amount of delay is zero up through that many
consecutive failed connection attempts. Thereafter, the amount
of delay is the number of failed attempts above the threshold,
milliseconds. For example, with the default
values of 3 and 1000, respectively, there is no delay for the
first three consecutive failed connection attempts by a
client, a delay of 1000 milliseconds for the fourth failed
attempt, 2000 milliseconds for the fifth failed attempt, and
so on, up to the maximum delay permitted by
You can set the
variables at server startup or runtime. Suppose that you want
to permit four consecutive failed connection attempts before
the server starts delaying its responses, and to increase the
delay by 1500 milliseconds for each additional failure after
that. To set the relevant variables at server startup, put
these lines in the server
[mysqld] plugin-load-add=connection_control.so connection_control_failed_connections_threshold=4 connection_control_min_connection_delay=1500
To set and persist the variables at runtime, use these statements:
SET PERSIST connection_control_failed_connections_threshold = 4; SET PERSIST connection_control_min_connection_delay = 1500;
PERSIST sets the value for the running MySQL
instance. It also saves the value, causing it to be used for
subsequent server restarts. To change a value for the running
MySQL instance without saving it for subsequent restarts, use
GLOBAL keyword rather than
SET Syntax for Variable Assignment.
system variables have fixed minimum and maximum values of 1000
and 2147483647, respectively. In addition, the permitted range
of values of each variable also depends on the current value
of the other:
Thus, to make the changes required for some configurations,
you might need to set the variables in a specific order.
Suppose that the current minimum and maximum delays are 1000
and 2000, and that you want to set them to 3000 and 5000. You
cannot first set
to 3000 because that is greater than the current
value of 2000. Instead, set
to 5000, then set
CONNECTION_CONTROL plugin 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.
Failed-connection counting is based on the user/host combination for each connection attempt. Determination of the applicable user name and host name takes proxying into account and occurs as follows:
If the client user proxies another user, the proxying user's information is used. For example, if
firstname.lastname@example.org, connection counting uses the proxying user,
email@example.com, rather than the proxied user,
firstname.lastname@example.org have valid entries in the
mysql.usersystem table and a proxy relationship between them must be defined in the
mysql.proxies_privsystem table (see Section 4.18, “Proxy Users”).
If the client user does not proxy another user, but does match a
mysql.userentry, counting uses the
CURRENT_USER()value corresponding to that entry. For example, if a user
user1connecting from a host
email@example.com, counting uses
firstname.lastname@example.org. If the user matches a
user1@%entry instead, counting uses
For the cases just described, the connection attempt matches
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
If the connection attempt matches no
mysql.user entry, the attempt fails. In
this case, no
value is available and connection-failure counting uses the
user name provided by the client and the client host as
determined by the 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
The 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
Because this type of rejection occurs so early,
CONNECTION_CONTROL does not see it, and
does not count it.
To monitor failed connections, use these information sources:
Connection_control_delay_generatedstatus variable indicates the number of times the server added a delay to its response to a failed connection attempt. This does not count attempts that occur before reaching the threshold defined by the
CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTStable provides information about the current number of consecutive failed connection attempts per client user/host combination. This counts all failed attempts, regardless of whether they were delayed.
Assigning a value to
at runtime resets all accumulated failed-connection counters
to zero, which has these visible effects: