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!