6.4.2.1 Connection-Control Plugin Installation连接控制插件安装

This section describes how to install the connection-control plugins, CONNECTION_CONTROL and CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS. 本节介绍如何安装连接控制插件CONNECTION_CONTROL和CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS。For general information about installing plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.有关安装插件的一般信息，请参阅第5.6.1节，“安装和卸载插件”。

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system variable). 为了让服务器使用，插件库文件必须位于MySQL插件目录（由plugin_dir系统变量命名的目录）中。If necessary, configure the plugin directory location by setting the value of plugin_dir at server startup.如有必要，在服务器启动时通过设置plugin_dir的值来配置插件目录位置。

The plugin library file base name is connection_control. 插件库文件库名为connection_control。The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).文件名后缀因平台而异（例如，用于Unix和类Unix系统的.so，用于Windows的.dll）。

To load the plugins at server startup, use the --plugin-load-add option to name the library file that contains them. 要在服务器启动时加载插件，请使用--plugin-load-add选项命名包含它们的库文件。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, adjusting the .so suffix for your platform as necessary:例如，将这些行放入服务器my.cnf文件中，根据需要调整您的平台的.so后缀：

[mysqld]
plugin-load-add=connection_control.so

After modifying my.cnf, restart the server to cause the new settings to take effect.修改my.cnf后，重新启动服务器以使新设置生效。

Alternatively, to load the plugins at runtime, use these statements, adjusting the .so suffix for your platform as necessary:或者，要在运行时加载插件，请使用以下语句，根据需要调整您的平台的.so后缀：

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 without the need for --plugin-load-add.INSTALL PLUGIN会立即加载插件，并将其注册到mysql.plugins系统表中，以便服务器在每次后续正常启动时加载它，而不需要--plugin-load-add。

To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement (see Section 5.6.2, “Obtaining Server Plugin Information”). For example:要验证插件安装，请检查INFORMATION_SCHEMA.PLUGINS表或使用SHOW PLUGINS语句（请参阅第5.6.2节，“获取服务器插件信息”）。例如：

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 the --connection-control and --connection-control-failed-login-attempts options at server startup to control plugin activation. 如果插件之前已在INSTALL PLUGIN中注册或加载了--plugin-load-add，则可以在服务器启动时使用--connection-control和--connection-control-failed-login-attempts选项来控制插件激活。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 or FORCE_PLUS_PERMANENT to force server startup to fail if the plugin does not initialize successfully.如果希望防止服务器在没有给定连接控制插件的情况下运行，请使用FORCE或FORCE_PLUS_PERMANET选项值，在插件未成功初始化的情况下强一致性务器启动失败。

Note注意

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 CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS plugin is of little use because, without the CONNECTION_CONTROL plugin to provide the data that populates the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table, the table is always empty.特别是，只安装CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件用处不大，因为如果没有CONNECTION_CONTROL插件提供填充CONNECTION_CONTROL_FOAILED_LOGIN_ATTEMPTS表的数据，该表总是空的。

Connection Delay Configuration连接延迟配置

To enable configuring its operation, the CONNECTION_CONTROL plugin exposes these system variables:为了能够配置其操作，CONNECTION_CONTROL插件公开了以下系统变量：

If connection_control_failed_connections_threshold is nonzero, failed-connection counting is enabled and has these properties:如果connection_control_failed_connections_threshold为非零，则启用失败连接计数，并具有以下属性：

  • The delay is zero up through connection_control_failed_connections_threshold consecutive failed connection attempts.通过connection_control_failed_connections_threshold连续失败的连接尝试，延迟为零。

  • Thereafter, the server adds an increasing delay for subsequent consecutive attempts, until a successful connection occurs. The initial unadjusted delays begin at 1000 milliseconds (1 second) and increase by 1000 milliseconds per attempt. 此后，服务器为后续的连续尝试增加延迟，直到成功连接。初始未调整的延迟从1000毫秒（1秒）开始，每次尝试增加1000毫秒。That is, once delay has been activated for an account, the unadjusted delays for subsequent failed attempts are 1000 milliseconds, 2000 milliseconds, 3000 milliseconds, and so forth.也就是说，一旦为帐户激活了延迟，后续失败尝试的未调整延迟为1000毫秒、2000毫秒、3000毫秒等。

  • The actual delay experienced by a client is the unadjusted delay, adjusted to lie within the values of the connection_control_min_connection_delay and connection_control_max_connection_delay system variables, inclusive.客户端实际经历的延迟是未调整的延迟，调整后的延迟在connection_control_min_connection_delay和connection_control_max_connection_delay系统变量的值范围内，包括在内。

  • Once delay has been activated for an account, the first successful connection thereafter by the account also experiences a delay, but failure counting is reset for subsequent connections.一旦为帐户激活了延迟，帐户此后的第一次成功连接也会经历延迟，但后续连接的失败计数会重置。

For example, with the default connection_control_failed_connections_threshold value of 3, there is no delay for the first three consecutive failed connection attempts by an account. 例如，默认connection_control_failed_connections_threshold值为3时，帐户连续三次失败的连接尝试没有延迟。The actual adjusted delays experienced by the account for the fourth and subsequent failed connections depend on the connection_control_min_connection_delay and connection_control_max_connection_delay values:帐户在第四次和后续失败连接中经历的实际调整延迟取决于connection_control_min_connection_delay和connection_control_max_connection-delay值：

  • If connection_control_min_connection_delay and connection_control_max_connection_delay are 1000 and 20000, the adjusted delays are the same as the unadjusted delays, up to a maximum of 20000 milliseconds. 如果connection_control_min_connection_delay和connection_control_max_connection_delay分别为1000和20000，则调整后的延迟与未调整的延迟相同，最大为20000毫秒。The fourth and subsequent failed connections are delayed by 1000 milliseconds, 2000 milliseconds, 3000 milliseconds, and so forth.第四个和后续的失败连接会延迟1000毫秒、2000毫秒、3000毫秒，以此类推。

  • If connection_control_min_connection_delay and connection_control_max_connection_delay are 1500 and 20000, the adjusted delays for the fourth and subsequent failed connections are 1500 milliseconds, 2000 milliseconds, 3000 milliseconds, and so forth, up to a maximum of 20000 milliseconds.如果connection_control_min_connection_delay和connection_control_ma_connection_delay分别为1500和20000，则第四个和后续失败连接的调整延迟分别为1500毫秒、2000毫秒、3000毫秒，以此类推，最大为20000毫秒。

  • If connection_control_min_connection_delay and connection_control_max_connection_delay are 2000 and 3000, the adjusted delays for the fourth and subsequent failed connections are 2000 milliseconds, 2000 milliseconds, and 3000 milliseconds, with all subsequent failed connections also delayed by 3000 milliseconds.如果connection_control_min_connection_delay和connection_control_ma_connection_delay分别为2000和3000，则第四个和后续失败连接的调整延迟分别为2000毫秒、2000毫秒和3000毫秒，所有后续失败连接也延迟了3000毫秒。

You can set the CONNECTION_CONTROL system variables at server startup or runtime. Suppose that you want to permit four consecutive failed connection attempts before the server starts delaying its responses, with a minimum delay of 2000 milliseconds. 您可以在服务器启动或运行时设置CONNECTION_CONTROL系统变量。假设您希望在服务器开始延迟其响应之前允许连续四次失败的连接尝试，最小延迟为2000毫秒。To set the relevant variables at server startup, put these lines in the server my.cnf file:要在服务器启动时设置相关变量，请将以下行放入服务器my.cnf文件中：

[mysqld]
plugin-load-add=connection_control.so
connection_control_failed_connections_threshold=4
connection_control_min_connection_delay=2000

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 = 2000;

SET PERSIST sets the value for the running MySQL instance. It also saves the value, causing it to carry over to subsequent server restarts. SET PERSIST设置正在运行的MySQL实例的值。它还保存了该值，使其延续到后续的服务器重启。To change a value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST. 要更改正在运行的MySQL实例的值而不将其转移到后续重新启动，请使用GLOBAL关键字而不是PERSIST。See Section 13.7.6.1, “SET Syntax for Variable Assignment”.请参阅第13.7.6.1节，“变量赋值的SET语法”。

The connection_control_min_connection_delay and connection_control_max_connection_delay system variables both have minimum and maximum values of 1000 and 2147483647. connection_control_min_connection_delay和connection_control_max_connection_delay系统变量的最小值和最大值均为1000和2147483647。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. 假设当前的最小和最大延迟分别为1000和2000，并且您想将它们设置为3000和5000。You cannot first set connection_control_min_connection_delay to 3000 because that is greater than the current connection_control_max_connection_delay value of 2000. 您不能首先将connection_control_min_connection_delay设置为3000，因为它大于当前connection_control_max_connection_delay的值2000。Instead, set connection_control_max_connection_delay to 5000, then set connection_control_min_connection_delay to 3000.相反，将connection_control_max_connection_delay设置为5000，然后将connection_concontrol_min_connection_delay设置为3000。

Connection Failure Assessment连接故障评估

When the CONNECTION_CONTROL plugin is installed, it checks connection attempts and tracks whether they fail or succeed. 安装CONNECTION_CONTROL插件后，它会检查连接尝试并跟踪它们是失败还是成功。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.为此，失败的连接尝试是指客户端用户和主机与已知的MySQL帐户匹配，但提供的凭据不正确，或与任何已知帐户都不匹配。

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 account for failed-connection counting is the proxying user, not the proxied user. 如果客户端用户代理另一个用户，则失败连接计数的帐户是代理用户，而不是被代理的用户。For example, if external_user@example.com proxies proxy_user@example.com, connection counting uses the proxying user, external_user@example.com, rather than the proxied user, proxy_user@example.com. 例如，如果external_user@example.com代理proxy_user@example.com，连接计数使用代理用户，external_user@example.com而不是代理用户，proxy_user@example.com。Both external_user@example.com and proxy_user@example.com must have valid entries in the mysql.user system table and a proxy relationship between them must be defined in the mysql.proxies_priv system table (see Section 6.2.18, “Proxy Users”).external_user@example.com和proxy_user@example.com两者必须在mysql.user系统表中具有有效条目，并且必须在`mysql.proxies_priv系统表中定义它们之间的代理关系（请参阅第6.2.18节，“代理用户”）。

  • If the client user does not proxy another user, but does match a mysql.user entry, counting uses the CURRENT_USER() value corresponding to that entry. 如果客户端用户没有代理另一个用户，但与mysql.user条目匹配，则计数将使用与该条目对应的CURRENT_user()值。For example, if a user user1 connecting from a host host1.example.com matches a user1@host1.example.com entry, counting uses user1@host1.example.com. 例如，如果从主机host1.example.com连接的用户user1与user1@host1.example.com条目、计数用途user1@host1.example.com。If the user matches a user1@%.example.com, user1@%.com, or user1@% entry instead, counting uses user1@%.example.com, user1@%.com, or user1@%, respectively.如果用户与user1@%example.com、user1@%.com或user1@%条目匹配，则计数将分别使用user1@%1example.com、user1@%.com或user1@%。

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. 对于刚才描述的情况，连接尝试与某些mysql.user条目匹配，请求成功或失败取决于客户端是否提供了正确的身份验证凭据。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. 如果连接尝试与mysql.user条目不匹配，则尝试失败。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 server. 在这种情况下，没有可用的CURRENT_USER()值，连接失败计数使用客户端提供的用户名和服务器确定的客户端主机。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. 例如，如果客户端试图以用户user2的身份从主机host2.example.com连接，则用户名部分在客户端请求中可用，服务器确定主机信息。The user/host combination used for counting is user2@host2.example.com.用于计数的用户/主机组合为user2@host2.example.com。

Note注意

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:服务器维护有关哪些客户端主机可能连接到服务器的信息（本质上是mysql.user条目的主机值的联合）。如果客户端尝试从任何其他主机连接，服务器会在连接设置的早期阶段拒绝该尝试：

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.因为这种类型的拒绝发生得很早，所以CONNECTION_CONTROL看不到它，也不计算它。

Connection Failure Monitoring连接故障监测

To monitor failed connections, use these information sources:要监视失败的连接，请使用以下信息源：

  • The Connection_control_delay_generated status variable indicates the number of times the server added a delay to its response to a failed connection attempt. Connection_control_delay_generated状态变量表示服务器对连接尝试失败的响应添加延迟的次数。This does not count attempts that occur before reaching the threshold defined by the connection_control_failed_connections_threshold system variable.这不计算在达到connection_control_failed_connections_threshold系统变量定义的阈值之前发生的尝试。

  • The INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table provides information about the current number of consecutive failed connection attempts per account (user/host combination). INFORMATION_SCHEMA CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS表提供了有关每个帐户（用户/主机组合）当前连续失败连接尝试次数的信息。This counts all failed attempts, regardless of whether they were delayed.这计算了所有失败的尝试，无论它们是否被延迟。

Assigning a value to connection_control_failed_connections_threshold at runtime has these effects:在运行时为connection_control_failed_connections_threshold赋值具有以下效果：

  • All accumulated failed-connection counters are reset to zero.所有累积的失败连接计数器都重置为零。

  • The Connection_control_delay_generated status variable is reset to zero.Connection_control_delay_generated状态变量重置为零。

  • The CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table becomes empty.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS表变为空。