intro
Follow this step-by-step tutorial to learn everything you need to know about protecting MySQL with Fail2Ban.
Managing a database is not just about writing queries. It also involves taking care of the servers that host these databases, implementing security measures, and more. Considering that bots account for about 50% of global Internet traffic, you must protect your server against automated attacks, such as brute-force attacks. This is where Fail2Ban comes in—a tool that bans IP addresses after too many failed login attempts. Learn how to protect MySQL with Fail2Ban!
What Is Fail2Ban?
Fail2Ban is a security tool that protects servers from various attacks by monitoring log files for suspicious activity. In detail, it is a daemon that blocks hosts after multiple failed authentication attempts.
It works by scanning system logs for failed login attempts and other signs of malicious behavior. When an issue is detected, Fail2Ban temporarily blocks the offending IP address using firewall rules, preventing further unauthorized attempts.
To operate, Fail2Ban relies on "jails," which are rulesets tied to specific services—such as SSH or MySQL. Each “jail” defines the criteria for blocking an IP and the duration of the block. By configuring these jails, system administrators can tailor Fail2Ban to safeguard various services, including MySQL.
Why Is Fail2Ban Useful in a MySQL Server?
The main benefits you gain when you protect MySQL with Fail2Ban are:
Protect MySQL With Fail2Ban for IP Blacklisting: Step-By-Step Guide
Follow this tutorial section and learn how to configure Fail2Ban for MySQL.
Step #1: Configure MySQL to Log Failed Login Attempts
By default, MySQL might not log failed login attempts in the error log file. On Debian-based distros, the error log file is typically located at /var/log/mysql/error.log.
When a user fails to log in to the MySQL server, you should see an entry similar to this in the error log:
1
Access denied for user 'root'@'localhost' (using password: YES)
To verify if failed login attempts are being logged, you can run the following command:
1
cat /var/log/mysql/error.log | grep "Access denied"
Try to perform some wrong login attempts. If the result is empty, it means MySQL is not logging failed login attempts.
Fail2Ban relies on these error log messages to block malicious IP addresses. To ensure MySQL logs failed login attempts, you need to configure it to log more detailed messages. This can be done by adjusting the log_error_verbosity setting in MySQL’s my.cnf configuration file.
The location of MySQL's my.cnf file may vary, but it is generally found in one of these two locations:
To modify the configuration, open the my.cnf file with a text editor like nano:
1
nano /etc/mysql/my.cnf
In the [mysqld] section, add or modify the log_error_verbosity setting to 3. This will instruct MySQL to log errors, warnings, and informational messages:
1
[mysqld]
2
log_error_verbosity = 3
If the [mysqld] section is not already present in the file, add it yourself.
After saving the changes, restart the MySQL service:
1
systemctl restart mysql
Now, perform a few failed login attempts on localhost. Then, run the following command again to check the error log:
1
cat /var/log/mysql/error.log | grep "Access denied"
You should now see log entries like:
1
2025-01-09T15:47:16.606443Z 8 \\[Note\\] [MY-010926] [Server] Access denied for user 'root'@'localhost' (using password: NO)
2
2025-01-09T15:51:37.416867Z 9 \\[Note\\] [MY-010926] [Server] Access denied for user 'root'@'localhost' (using password: NO)
3
2025-01-09T15:51:41.799049Z 10 \\[Note\\] [MY-010926] [Server] Access denied for user 'root'@'localhost
Great! MySQL is now logging failed login attempts that Fail2Ban will read to ban IPs.
Step #2: Install Fail2Ban
First, log into the server where your MySQL installation is located. Then, you are ready to install Fail2Ban.
On Ubuntu or Debian systems, you can install Fail2Ban with the following command:
1
sudo apt install fail2ban
If this command fails, try to update your package lists before re-launching the above command:
1
sudo apt update
Alternatively, if you need to install Fail2Ban from the source, refer to the guide on the official repository.
After installation, Fail2Ban sets up a background service by default. The service is initially disabled to prevent unintended actions before proper configuration.
You can verify the service status and confirm it's disabled with the following command:
1
systemctl status fail2ban.service
The output should look like this:
1
* fail2ban.service - Fail2Ban Service
2
Loaded: loaded (/usr/lib/systemd/system/fail2ban.service; enabled; preset: enabled)
3
Active: inactive (dead)
4
Docs: man:fail2ban(1)
Great! You just installed Fail2Ban on your server.
Step #3: Configure Fail2Ban
The Fail2Ban service stores its configuration files in the /etc/fail2ban directory. The default configuration file used by Fail2Ban is jail.conf. This file contains settings for enabled jails, filters, and other configurations.
You can view the first 30 lines of the jail.conf file with the following command:
1
head -n 30 /etc/fail2ban/jail.conf
This is what you should see:
1
# # WARNING: heavily refactored in 0.9.0 release. Please review and
2
# customize settings for your setup.
3
# # Changes: in most of the cases you should not modify this
4
# file, but provide customizations in jail.local file,
5
# or separate .conf files under jail.d/ directory, e.g.:
6
# # HOW TO ACTIVATE JAILS:
7
# # YOU SHOULD NOT MODIFY THIS FILE.
8
# # It will probably be overwritten or improved in a distribution update.
9
# # Provide customizations in a jail.local file or a jail.d/customisation.local.
10
# For example to change the default bantime for all jails and to enable the
11
# ssh-iptables jail the following (uncommented) would appear in the .local file.
12
# See man 5 jail.conf for details.
13
# # [DEFAULT]
14
# bantime = 1h
15
# # [sshd]
16
# enabled = true
17
# # See jail.conf(5) man page for more information
Note: Different versions of Fail2Ban may have different default configuration files.
In the commented lines of jail.conf, it is clearly explained that you should not modify this file directly. The reason is that when Fail2Ban is upgraded (for example, when a new package is installed), the configuration could be overwritten, and any changes you made will be lost.
Instead, you have two options to customize Fail2Ban:
In this guide, we will follow the first approach. However, the second approach is also viable.
To begin, create or edit the jail.local file by running:
1
nano /etc/fail2ban/jail.local
Then, add the following lines to protect MySQL with Fail2Ban:
1
[mysqld-auth]
2
enabled = true
3
port = 3306
4
filter = mysqld-auth
5
logpath = /var/log/mysql/error.log
6
findtime = 3600
7
maxretry = 5
8
bantime = 86400
These are what the above parameters do:
Once you have added the configuration, save the file and restart the Fail2Ban service to apply the changes:
1
systemctl restart mysql
This will activate the MySQL protection and Fail2Ban will start monitoring failed login attempts based on the configuration you have set.
Step #4: Verify Fail2Ban Is Working
Fail2Ban is not based on magic. Instead, it works by reading error messages from the specified MySQL error log file (e.g., /var/log/mysql/error.log). This is made possible by the default mysqld-auth.conf filter file, located in the /etc/fail2ban/filter.d/ folder.
You can inspect that file by running the following command:
1
nano /etc/fail2ban/filter.d/mysqld-auth.conf
You should see something like this:
1
Fail2Ban filter for unsuccessful MySQL authentication attempts
2
# #
3
To log wrong MySQL access attempts add to /etc/my.cnf in [mysqld]:
4
log-error=/var/log/mysqld.log
5
log-warnings = 2
6
# If using mysql syslog [mysql_safe] has syslog in /etc/my.cnf
7
[INCLUDES]
8
Read common prefixes. If any customizations available -- read them from
9
common.local
10
before = common.conf
11
[Definition]
12
_daemon = mysqld
13
failregex = ^%(__prefix_line)s(?:(?:\\d{6}|\\d{4}-\\d{2}-\\d{2})[ T]\\s?\\d{1,2}:\\d{2}:\\d{2} )?(?:\\d+ )?[\\w+] (?:[[^]]+] )Access denied for user '[^']+'@''(?:\\s+(?:to database '[^']'|(using password: (?:YES|NO))){1,2})?\\s*$
14
ignoreregex =
15
DEV Notes:
16
# Technically __prefix_line can equate to an empty string hence it can support
17
syslog and non-syslog at once.
18
Example:
19
130322 11:26:54 [Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
20
# Authors: Artur Penttinen
21
Yaroslav O. Halchenko
Note: The content in this file may vary depending on the version of Fail2Ban you are using. You can refer to the official Fail2Ban documentation to see the latest version of the mysqld-auth.conf file.
The above mysqld-auth.conf file contains patterns used to identify failed MySQL authentication attempts in the MySQL error log. in detail, the failregex defines the regular expression that searches for specific log entries indicating a failed login attempt in the format:
1
Access denied for user 'root'@'localhost' (using password: YES)
The filter ensures that any failed login attempts that match this regular expression are flagged. That is what allows Fail2Ban to detect attacks targeting your MySQL server.
You can test the filter using the following command:
1
fail2ban-regex /var/log/mysql/error.log /etc/fail2ban/filter.d/mysqld-auth.conf
In our server, the result is:
1
Running tests
2
=============
3
4
Use failregex filter file : mysqld-auth, basedir: /etc/fail2ban
5
Use datepattern : {^LN-BEG} : Default Detectors
6
Use log file : /var/log/mysql/error.log
7
Use encoding : utf-8
8
9
Results
10
=======
11
12
Failregex: 71 total
13
|- #) [# of hits] regular expression
14
| 1) [142] ^(?:\\[\\])?\\s*(?:<[^.]+\\.[^.]+>\\s+)?(?:\\S+\\s+)?(?:kernel:\\s?\\[ *\\d+\\.\\d+\\]:?\\s+)?(?:@vserver_\\S+\\s+)?(?:(?:(?:\\[\\d+\\])?:\\s+[\\[\\(]?mysqld(?:\\(\\S+\\))?[\\]\\)]?:?|[\\[\\(]?mysqld(?:\\(\\S+\\))?[\\]\\)]?:?(?:\\[\\d+\\])?:?)\\s+)?(?:\\[ID \\d+ \\S+\\]\\s+)?(?:(?:\\d{6}|\\d{4}-\\d{2}-\\d{2})[ T]\\s?\\d{1,2}:\\d{2}:\\d{2} )?(?:\\d+ )?\\[\\w+\\] (?:\\[[^\\]]+\\] )*Access denied for user '<F-USER>[^']+</F-USER>'@'<HOST>'(?:\\s+(?:to database '[^']*'|\\(using password: (?:YES|NO)\\)){1,2})?\\s*$
15
`-
16
17
Ignoreregex: 0 total
18
19
Date template hits:
20
|- [# of hits] date format
21
| [721] {^LN-BEG}ExYear(?P<_sep>[-/.])Month(?P=_sep)Day(?:T| ?)24hour:Minute:Second(?:[.,]Microseconds)?(?:\\s*Zone offset)?
22
`-
23
24
Lines: 721 lines, 0 ignored, 71 matched, 650 missed
25
[processed in 0.11 sec]
26
27
Missed line(s): too many to print. Use --print-all-missed to print all 650 lines
From the output, we can see that Fail2Ban detected 71 failed login attempts in the MySQL error log, confirming that the filter is working as expected.
Step #5: Monitor Your Fail2Ban Setup
Lastly, always monitor your setup by testing the policies of Fail2Ban. By doing so, you will stay informed, educated, and equipped to fight potential attackers.
You can test the status of your Fail2Ban policies for the mysqld-auth jail using the following command:
1
fail2ban-client status mysqld-auth
The result will look something like this:
1
Status for the jail: mysqld-auth
2
|- Filter
3
| |- Currently failed: 1
4
| |- Total failed: 26
5
| `- File list: /var/log/mysql/error.log
6
`- Actions
7
|- Currently banned: 1
8
|- Total banned: 2
9
`- Banned IP list: 21.19.196.161
Here is an explanation for the output parameters:
Congratulations! You just learned how to protect MySQL with Fail2Ban.
Conclusion
In this article, you learned what Fail2Ban is and how to use it to protect MySQL servers from brute-force attacks. As discussed, configuring Fail2Ban does not take much time, but the benefits are significant.
To connect to your MySQL database, consider using an advanced and visual database client like DbVisualizer. With features like SSH tunneling, it allows you to access database servers even if a firewall is in place.
These are just a few of the many features DbVisualizer offers, including visual query execution, data exploration, and table discovery. Additionally, it comes with advanced tools like SQL formatting and ERD-like schema generation. Try DbVisualizer for free today!
FAQ
Why should you protect MySQL with Fail2Ban?
If you protect MySQL with Fail2Ban, you can prevent brute-force attacks. By monitoring failed login attempts, Fail2Ban automatically bans malicious IP addresses. Users who exceed the configured number of failed login attempts are banned to reduce the risk of unauthorized access and potential data breaches.
Is it possible to keep Fail2Ban data in a MySQL database?
Yes, it is possible to store Fail2Ban data in a MySQL database. By configuring Fail2Ban to log events to a MySQL database, you can centralize the storage of failed login attempts, bans, and other security-related data. For more guidance, refer to the following two tutorials:
Does Fail2Ban also work with MariaDB?
Yes, Fail2Ban works with MariaDB. The setup for MariaDB is similar to MySQL, with the key difference being that you may need to update the path to the specific error log file. Also, make sure the port is the right one.
How can you whitelist IPs in your Fail2Ban MySQL setup?
To whitelist IPs in Fail2Ban, add the IP addresses to the ignoreip directive in the jail.local configuration file. For example:
1
[mysqld-auth]
2
ignoreip = 127.0.0.1/8 192.168.1.100
This ensures that the specified IP addresses are never banned by Fail2Ban.
Why use a database client for MySQL?
Database clients offer a powerful and feature-rich environment for anyone dealing with database management systems.
One of those is DbVisualizer — it’s a powerful, highly-rated multi-database client that allows you to manage different databases from a single platform. This streamlines database management and makes databases more accessible for users of all skill levels. Grab a 21-day DbVisualizer free trial today!
Where can I find other useful resources on MySQL security?
“Hacking MySQL: Breaking, Optimizing, and Securing MySQL for Your Use Case” by Lukas Vileikis is definitely what you are looking for!
