Fail2Ban monitoring MySQL
I’ve only started using Fail2Ban recently and at that only since I have started using the WordPress WP Fail2ban plugin – which I will post again about that at a later date. For now though I will write about how I have added MySQL as a monitored service on my ubuntu 12.04 server.
To start off we have to log failed connection attempts and this is done without using the general log and by adding:
log_warnings = 2
to the my.cnf file which on an ubuntu server is in /etc/mysql. You will also need to make sure that the error log is enabled. I have changed mine to be:
log_error = /var/log/mysql/error.log
in the same file.
Now we need to look at a failed login to see what is logged. Here is an example:
140501 11:18:05 [Warning] Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES)
Now that we have a format we can start writing our Fail2ban filter.
# Fail2Ban configuration file
#
# Author: Andy Lear
#
# $Revision$
#
[Definition]
# Option: failregex
# Notes.: regex to match the password failures messages in the logfile. The
# host must be matched by a group named "host". The tag "" can
# be used for standard IP/hostname matching and is only an alias for
# (?:::f{4,6}:)?(?P[\w\-.^_]+)
# Values: TEXT
#
failregex = Access denied for user .*@''.*$
# Option: ignoreregex
# Notes.: regex to ignore. If this regex matches, the line is ignored.
# Values: TEXT
#
ignoreregex =
and save it to:
/etc/fail2ban/filter.d/mysql.conf
Now we need to add to the jail.local in /etc/fail2ban to activate the filter:
[mysql] enabled = true port = 3306 filter = mysql logpath = /var/log/mysql/error.log maxretry = 3
When we restart fail2ban and look at the debug log we’ll see something like this:
2014-05-01 11:04:59,522 fail2ban.filter : DEBUG Found a match for '140501 11:04:59 [Warning] Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES) ' but no valid date/time found for '140501 11:04:59 [Warning] Access denied for user 'root'@'xxx.xxx.xxx.xxx' (using password: YES) '. Please contact the author in order to get support for this format
So doing a little digging I found that the date/time format is parsed in:
/usr/share/fail2ban/server/datedetector.py
A touch more googling and I was able to add a new template to that file. you will see near the top that a class DateDetector is created. Scroll down the file until you get to finally: and insert the following before it.
# MySQL date detector
template = DateStrptime()
template.setName("YearMonthDay Hour:Minute:Second")
template.setRegex("\d{2}\d{2}\d{2} {1,2}\d{1,2}:\d{2}:\d{2}")
template.setPattern("{0485a2dee41ac5bc19be369b86ade435390b5d2f09c107e6e6935a4e65879846}y{0485a2dee41ac5bc19be369b86ade435390b5d2f09c107e6e6935a4e65879846}m{0485a2dee41ac5bc19be369b86ade435390b5d2f09c107e6e6935a4e65879846}d {0485a2dee41ac5bc19be369b86ade435390b5d2f09c107e6e6935a4e65879846}H:{0485a2dee41ac5bc19be369b86ade435390b5d2f09c107e6e6935a4e65879846}M:{0485a2dee41ac5bc19be369b86ade435390b5d2f09c107e6e6935a4e65879846}S")
self.__templates.append(template)
At this point we can restart Fail2Ban again and now you should see that the log file is picking up everything as we want it to:
2014-05-01 11:53:24,728 fail2ban.filter : DEBUG Got event: 1 for /var/log/mysql/error.log 2014-05-01 11:53:24,728 fail2ban.filter : DEBUG File changed: /var/log/mysql/error.log 2014-05-01 11:53:24,777 fail2ban.filter : DEBUG Processing line with time:1398941602.0 and ip:xxx.xxx.xxx.xxx 2014-05-01 11:53:24,777 fail2ban.filter : DEBUG Found xxx.xxx.xxx.xxx 2014-05-01 11:53:24,777 fail2ban.filter : DEBUG Currently have failures from 1 IPs: ['xxx.xxx.xxx.xxx']
After a few more attempts we then get:
2014-05-01 11:54:44,770 fail2ban.actions: WARNING [mysql] Ban xxx.xxx.xxx.xxx
2014-05-01 11:54:44,778 fail2ban.actions.action: DEBUG iptables -I fail2ban-mysql 1 -s xxx.xxx.xxx.xxx -j DROP
2014-05-01 11:54:44,783 fail2ban.actions.action: DEBUG iptables -I fail2ban-mysql 1 -s xxx.xxx.xxx.xxx -j DROP returned successfully
2014-05-01 11:54:44,786 fail2ban.actions.action: DEBUG printf {0485a2dee41ac5bc19be369b86ade435390b5d2f09c107e6e6935a4e65879846}b "Subject: [Fail2Ban] mysql: banned xxx.xxx.xxx.xxx
The IP xxx.xxx.xxx.xxx has just been banned by Fail2Ban after
There we go Fail2Ban blocking failed logins to MySQL!