Protect MySQL Auth with Fail2Ban

Since I have a remote mysql server that the public (Internet) can connect to, I've been wondering how to protect mysql auth with Fail2ban.

We have discussed Protect SSH with Fail2Ban in the previous post.

I have crawled google about this too and found this one [Setup mysqld-auth jail di fail2ban], but it's not relevant to my latest mariadb (mariadb 10.6), the default log still on /var/log/syslog.  

So, I am writing this one, maybe it could help someone (and for a note to myself):

Add a New Fail2ban Jail

Edit jail.local file:

sudo nano /etc/fail2ban/jail.local

Add a new jail if it doesn't exist:

[mysqld-auth]
enabled = true
port     = 3306
log-error = /var/log/syslog
logpath = /var/log/syslog
backend  = %(mysql_backend)s

I found out why my mariadb 10.6 is still using var/log/syslog to log rather than /var/log/mysql/error.log on /etc/mysql/mariadb.conf.d file, there is a comment:

# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
#log_error = /var/log/mysql/error.log

After save jail.local, restart fail2ban with:

sudo systemctl restart fail2ban

It is important to test your Fail2ban policies to ensure they block traffic as expected. So I tested it.

root@sql2:~# fail2ban-client status mysqld-auth
Status for the jail: mysqld-auth
|- Filter
|  |- Currently failed: 0
|  |- Total failed:     3
|  `- File list:        /var/log/syslog
`- Actions
   |- Currently banned: 1
   |- Total banned:     1
   `- Banned IP list:   192.168.0.6

The banned IP list can't connect the remote database server with port 3306 but still can connect to other normal ports like ssh, or http/https ports.

Unblock IP

To unblock banned IPs, run the following command

fail2ban-client set mysqld-auth unbanip 192.168.0.6

Thanks for reading!