Introducing the Connection-Control Component in MySQL 9.2 : A Powerful Tool for Connection Management

Overview

In a world where database performance and security are paramount, MySQL’s robust component architecture offers numerous ways to enhance and protect the customer’s data. Chief among them is the Connection-Control component now available in MySQL 9.2.0 for Community and Enterprise Editions, and HeatWave. This new component helps administrators manage and mitigate the risk of excessive connection attempts or brute-force attacks on the MySQL server. A key feature of the component, is to introduce an increasing delay in server response to connection attempts after a configurable number of consecutive failed attempts.

This blog post dives into the features, benefits, and configuration of the Connection-Control component to help you make the most of it.

 

Key Features of the Connection-Control Component

Connection Rate Limiting

The component limits the number of connection attempts a client can make within a specified time frame. This helps prevent denial-of-service (DoS) or brute-force attacks by temporarily blocking clients that exceed the defined threshold.

Temporary Connection Blocking

It automatically blocks a client IP address for a configurable period if it exceeds the allowed number of failed connection attempts within a given timeframe.

Fine-grained Configuration

The component’s parameters can be configured to adjust the threshold and time interval:

• component_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.
• component_connection_control.min_connection_delay: The minimum delay in milliseconds for connection failures above the threshold.
• component_connection_control.max_connection_delay: The maximum delay in milliseconds for connection failures above the threshold.

Integration with MySQL error log

It logs connection control events (such as blocking and unblocking) in the MySQL error log, which aids in monitoring and auditing.

Runtime Configuration

The component’s settings can be adjusted dynamically without restarting the MySQL server.

Ease of Installation and Management

It can be installed, uninstalled, and managed like other MySQL components using SQL statements such as INSTALL COMPONENT and UNINSTALL COMPONENT.

 

Connection-Control Component Installation

To be usable by the server, the component library file must be located in the MySQL plugin directory (the directory named by the plugin_dir system 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 component_connection_control. The file name suffix differs per platform (for example, .so for Unix and Unix-like systems, .dll for Windows).

To load the component at runtime, use the following statement:

INSTALL COMPONENT 'file://component_connection_control';

INSTALL COMPONENT loads the component immediately, and also registers it in the mysql.component system table to cause the server to load it for each subsequent normal startup.

mysql> SELECT * FROM mysql.component;
+--------------+--------------------+-------------------------------------+
| component_id | component_group_id | component_urn                       |
+--------------+--------------------+-------------------------------------+
|            1 |                  1 | file://component_connection_control |
+--------------+--------------------+-------------------------------------+

 

Connection Delay Configuration

The Connection-Control component exposes the following system variables to enable configuring connection delay:

component_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, set component_connection_control.failed_connections_threshold to zero.

component_connection_control.min_connection_delay

The minimum delay in milliseconds for connection failures above the threshold.

component_connection_control.max_connection_delay

The maximum delay in milliseconds for connection failures above the threshold.

If component_connection_control.failed_connections_threshold is nonzero, failed-connection counting is enabled and has these properties:

• The delay is zero up through component_connection_control.failed_connections_threshold consecutive failed connection attempts.
• 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. That is, once the delay has been activated for an 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_delay and component_connection_control.max_connection_delay system variables, inclusive.
• Once the 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 component_connection_control.failed_connections_threshold value of 3, there is no delay for the first three consecutive failed connection attempts by an account. The actual adjusted delays experienced by the account for the fourth and subsequent failed connections depend on the component_connection_control.min_connection_delay and component_connection_control.max_connection_delay values:

• If component_connection_control.min_connection_delay and component_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. The fourth and subsequent failed connections are delayed by 1000 milliseconds, 2000 milliseconds, 3000 milliseconds, and so forth.
• If component_connection_control.min_connection_delay and component_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.
• If component_connection_control.min_connection_delay and component_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.

You can set the Connection-Control system variables at 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.

To set and persist the variables at runtime, use these statements:

SET PERSIST component_connection_control.failed_connections_threshold = 4;
SET PERSIST component_connection_control.min_connection_delay = 2000;

SET PERSIST sets a value for the running MySQL instance. It also saves the value, causing it to carry over to subsequent server restarts. To change a value for the running MySQL instance without having it carry over to subsequent restarts, use the GLOBAL keyword rather than PERSIST. See SET Syntax for Variable Assignment.

The component_connection_control.min_connection_delay and component_connection_control.max_connection_delay system variables both have minimum and maximum values of 1000 and 2147483647. In addition, the permitted range of values of each variable also depends on the current value of the other:

• component_connection_control.min_connection_delay cannot be set greater than the current value of component_connection_control.max_connection_delay.
• component_connection_control.min_connection_delay cannot be set less than the current value of component_connection_control.max_connection_delay.

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 component_connection_control.min_connection_delay to 3000 because that is greater than the current component_connection_control.max_connection_delay value of 2000. Instead, set component_connection_control.max_connection_delay to 5000, then set component_connection_control.min_connection_delay to 3000.

 

Connection Failure Assessment

When the 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.

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. 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.
• 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. 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. If the user matches a user1@%.example.com, user1@%.com, or user1@% entry instead, counting uses user1@%.example.com, user1@%.com, or user1@%, 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 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 user2@host2.example.com.

 

Connection Failure Monitoring

To monitor failed connections, use these information sources:

The component_connection_control_delay_generated status variable

It 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 component_connection_control.failed_connections_threshold system variable.

The performance_schema.connection_control_failed_login_attempts table

It provides information about the current number of consecutive failed connection attempts per account (user/host combination). This counts all failed attempts, regardless of whether they were delayed.

mysql> select * from performance_schema.connection_control_failed_login_attempts;
+---------------------+-----------------+
| USERHOST            | FAILED_ATTEMPTS |
+---------------------+-----------------+
| ‘user1’@‘localhost’ |               1 |
+---------------------+-----------------+

Assigning a value to component_connection_control.failed_connections_threshold at runtime has these effects:

• All accumulated failed-connection counters are reset to zero.
• The component_connection_control_delay_generated status variable is reset to zero.
• The connection_control_failed_login_attempts table becomes empty.

 

The Connection-Control Plugin

MySQL Server already includes a plugin library connection_control which provides the same functionality as the component. But it is deprecated and users are encouraged to use the component instead for several reasons:

• Improved Modularity and Integration
• Better Dependency Management
• Security Improvements
• Simplified Configuration and Deployment
• Uniform API and Development
• Future-Proofing
• Easier Maintenance

It is quite simple to migrate from the plugin to the component, the steps are outlined below.

 

Migration from the plugin to the component

Stop Using the Plugin

Disable the plugin to prepare for migration. Run the command:

UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
UNINSTALL PLUGIN CONNECTION_CONTROL;

Remove any configuration (if present) related to the plugin from e.g. my.cnf or my.ini. Ensure there are no active dependencies on the plugin, such as users or scripts relying on its functionality.

Install the Component

Use the to INSTALL COMPONENT statement to install the new component:

INSTALL COMPONENT 'file://component_connection_control';

Configure the Component

This is optional if you want to go with the default configuration.

Update the MySQL configuration file (my.cnf or my.ini) to include component-related options: component_connection_control.failed_connections_threshold, component_connection_control.min_connection_delay and component_connection_control.max_connection_delay.

Alternatively, you can also set it during runtime using SET GLOBAL or SET PERSIST commands.

Test the Component

Verify that the new component is functioning correctly. Check component installation using the query:

mysql> SELECT * FROM mysql.component;
+--------------+--------------------+-------------------------------------+
| component_id | component_group_id | component_urn                       |
+--------------+--------------------+-------------------------------------+
| 1            | 1                  | file://component_connection_control |
+--------------+--------------------+-------------------------------------+

Connection Failure Monitoring

The component_connection_control_delay_generated status variable indicates the number of times the server added a delay to its response to a failed connection attempt.

Unlike the plugin which uses an Information Schema table, the component uses a Performance Schema table to provide information about the current number of consecutive failed connection attempts per account (user/host combination).

mysql> select * from performance_schema.connection_control_failed_login_attempts;
+---------------------+-----------------+
| USERHOST            | FAILED_ATTEMPTS |
+---------------------+-----------------+
| ‘user1’@‘localhost’ | 1               |
+---------------------+-----------------+

 

Conclusion

The Connection-Control component is a robust tool for managing client connections in real-time. Whether you are concerned about security threats or server overload, this component gives you the flexibility and control needed to maintain a stable database environment.

By integrating this component into your setup, you can ensure that your database remains secure and resilient against misuse.

And once more, the Connection-Control component in now available in MySQL 9.2.0 for Community and Enterprise Editions, and HeatWave.