Skip to content

Instantly share code, notes, and snippets.

Last active May 20, 2020 07:55
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save GAS85/671087c83f36c46367f51ff400b41510 to your computer and use it in GitHub Desktop.
Fail2Ban and Cacti - read MySQL table

Fail2Ban and Cacti - read MySQL/MariaDB table

Please Note: This is an old way of working, for a newer please refer to

I took it from here, but becasue of newer MySQL it needs to be updated a bit.


  • Ubuntu 16.04
  • MySQL 5.7
  • cacti, fail2ban and e.g. iptables are installed


Cacti logs failed login attempts to a MySQL table, which Fail2ban can't read. By using MySQL's CSV storage engine we can write login attempts to an additional table, which on-disk format is ASCII or UTF-8, and thus readable by Fail2ban. Note: This includes at least a trigger and and an optional scheduled event inside the database. I consider this "dirty", especially within a replication scenario.


Connect to your Cacti database, e.g.:

$ mysql cacti

Create the new table:

mysql> CREATE TABLE fail2ban (
    -> `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> ip varchar(40) NOT NULL DEFAULT '',
    -> username varchar(50) NOT NULL DEFAULT '')
    -> ENGINE=CSV;

Create the trigger. For this, we have to alter the input delimiter of the MySQL client temporarily:

mysql> delimiter |
mysql> CREATE DEFINER=cacti@localhost TRIGGER failed_logins
    -> BEGIN
    ->   IF NOT NEW.result = 1
    ->     THEN INSERT INTO fail2ban SET time=NEW.time, ip=NEW.ip, username=NEW.username; 
    ->   END IF;
    -> END; |

Optionally, start the event scheduler and create an event which deletes any old records from that table:

mysql> SET GLOBAL event_scheduler = 1 |
mysql> CREATE DEFINER=cacti@localhost EVENT delete_old_logs
    -> ON SCHEDULE EVERY 1 hour COMMENT 'wipe old failed login attempts'
    -> DO DELETE FROM fail2ban WHERE time <= subtime(now(), '01:00:00') |
mysql> quit|

Do not forget to set enable (event scheduler)[] in your MySQL server:

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

As the trigger writes only failed login attempts (result=1) to the new table, we can get away with a fairly simple Fail2ban filter:

failregex = ","<HOST>","
ignoreregex =

Save it to a file called filter.d/cacti.local within your Fail2ban configuration directory, e.g. /etc/fail2ban, or /usr/local/etc/fail2ban on FreeBSD. Finally, extend your jail.local, e.g.:

enabled = yes
filter = cacti
action = iptables-multiport
         sendmail-whois-lines[name=cacti, logpath=/data/mysql/cacti/fail2ban.CSV]
logpath = /var/lib/mysql/cacti/fail2ban.CSV

Either restart Fail2ban, or activate the new jail:

# fail2ban-client add cacti-ipfw
# fail2ban-client start cacti-ipfw


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment