When a MySQL Server is “Tortured” by Brute-force
Have you ever checked your MySQL logs only to be horrified by thousands of Access denied lines from a completely unknown IP? I once found myself in this situation on a Saturday night. The monitoring system reported CPU usage spiking to 90%. Initially, I thought the code was stuck in a loop, but looking closely at the processlist, I saw a flurry of connections hanging in an authentication wait state.
Attackers often use automated scripts to try millions of passwords. Even if your password is strong enough, having MySQL process thousands of connection requests every second still consumes significant RAM and CPU. In fact, each connection thread can take up 5MB – 10MB of RAM. If not stopped, the server will soon run out of resources and the application will hang.
Instead of struggling with complex firewalls, you can immediately use MySQL’s Connection Control Plugin. This plugin acts like a patient “gatekeeper.” It forces failed logins to wait longer for each subsequent attempt.
How Connection Control Works
This plugin has been available since MySQL version 5.7.17. Its principle is very practical: the more you enter the wrong password, the slower the server responds. This directly targets the weakness of brute-force scripts, which rely on high speed.
How the Server “Punishes” Attackers
This configuration revolves around two important parameters:
- Threshold: The maximum number of consecutive failed login attempts allowed.
- Delay: The delay time (in milliseconds) the server applies to failed attempts after exceeding the threshold.
Let’s do the math. Normally, a script can try 100 passwords per second. If you set the delay to 1000ms (1 second), their speed drops to just 1 password per second. An attack expected to take 2 days to complete would now stretch into… several years.
Practical Installation and Configuration Steps
Below is the process for activating this feature on a production server.
1. Installing the Plugin
By default, this plugin is not enabled. You need to log in to MySQL with root privileges and check the current status:
SHOW PLUGINS LIKE 'connection%';If the list is empty, run the command to install these two main components:
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';Check again using SHOW PLUGINS. If you see the status ACTIVE, you’re halfway there.
2. Setting Optimal Parameters
Don’t stick with the default values. I usually prefer a balanced configuration so it doesn’t make things difficult for team members if they accidentally mistype their password:
-- Allow a maximum of 3 failed attempts
SET GLOBAL connection_control_failed_connections_threshold = 3;
-- Penalize starting from 2 seconds (2000ms) up to a maximum of 10 seconds (10000ms)
SET GLOBAL connection_control_min_connection_delay = 2000;
SET GLOBAL connection_control_max_connection_delay = 10000;In this setup, min_connection_delay is the initial penalty. If the attacker persists, the wait time will gradually increase until it hits the max_connection_delay ceiling.
3. Saving Configuration Permanently
The SET GLOBAL commands will vanish if MySQL restarts. Add them to your my.cnf file (or mysqld.cnf on Ubuntu) to ensure long-term security:
[mysqld]
plugin-load-add = connection_control.so
connection_control_failed_connections_threshold = 3
connection_control_min_connection_delay = 2000
connection_control_max_connection_delay = 10000After that, restart the service to apply the changes:
sudo systemctl restart mysqlMonitoring the Effectiveness of Attack Prevention
To see if the plugin is working effectively, check the following statistics table:
SELECT * FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;This table lists in detail which Users and Hosts (IPs) are in violation. If you see an unfamiliar IP with a FAILED_ATTEMPTS count in the hundreds, you know for sure that Connection Control is successfully protecting your server.
Practical Experience: Don’t Let it Backfire
I once encountered a case where a Junior developer wrote a migration script but forgot to change the old password. The script ran a continuous loop calling the database. Thanks to Connection Control, the server didn’t crash due to connection overload. The script just ran very slowly and reported timeouts, helping me identify and fix the error in just 5 minutes.
However, a small note: Avoid setting min_connection_delay too high (like 30-60 seconds) right from the start. If the network is unstable and causes random authentication errors, legitimate users will get stuck in the delay queue, resulting in a very poor experience.
Conclusion
Connection Control doesn’t completely replace a VPN or Firewall, but it’s an extremely effective layer of armor against automated scans. With just 5 minutes of configuration, you can minimize the risk of your database being hijacked and save significant server resources. If your server has port 3306 open to the public, enable it today.
