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
So, I am writing this one, maybe it could help someone (and for a note to myself):
Add a New Fail2ban Jail
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
/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
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.
To unblock banned IPs, run the following command
fail2ban-client set mysqld-auth unbanip 192.168.0.6
Thanks for reading!