I am trying to use fail2ban with MYSQL 5.7.19
The error.log shows entries like this:
2017-10-03T19:17:45.014850Z 127207 [Note] Access denied for user 'trafic'@'localhost' (using password: YES)
2017-10-03T19:17:46.222136Z 127209 [Note] Access denied for user 'user'@'localhost' (using password: YES)
2017-10-03T19:17:47.440388Z 127211 [Note] Access denied for user 'user'@'localhost' (using password: YES)
2017-10-03T19:17:48.625799Z 127213 [Note] Access denied for user 'wordpress'@'localhost' (using password: YES)
2017-10-03T19:17:49.849088Z 127219 [Note] Access denied for user 'root'@'localhost' (using password: YES)
Neither any IP is captured in the error.log nor it is shown as warning, but is logged as a [note]
I have set the log-warnings =2 in mysqld.cnf
Thanks.
update: I have these logs from mysql error.log more than 20 times from the same ip.
2017-10-04T14:35:45.932144Z 218879 [Note] Access denied for user 'root'@'123.129.218.64' (using password: YES)
2017-10-04T14:35:46.551180Z 218881 [Note] Access denied for user 'root'@'123.129.218.64' (using password: YES)
2017-10-04T14:35:47.169756Z 218882 [Note] Access denied for user 'root'@'123.129.218.64' (using password: YES)
and my fail2ban filter code is like this:
[Definition]
_daemon = mysqld
failregex = ^%(__prefix_line)s(\d{6} \s?\d{1,2}:\d{2}:\d{2} )?\[Warning\] Access denied for user '\w+'@'<HOST>' (to database '[^']*'|\(using password: (YES|N$
ignoreregex =
and jail.conf looks like this:
[mysql]
enabled = true
port = 3306
filter = mysqld-auth
logpath = /var/log/mysql/error.log
maxretry = 3
but fail2ban is not picking up the failed attempts.
I have also tried for a wrong user multiple times with PhpMyadmin in browser. so it automatically adds localhost to the user, even though I am trying it remotely.
so the question is I can try innumerable times in PhpMyadmin with failed attempts without being banned.
If MySQL isn't behaving, you can edit your fail2ban regex (failregex) to detect the "Note" instead of "Warning"
failregex = ^%(__prefix_line)s(\d{6} \s?\d{1,2}:\d{2}:\d{2} )?\[Warning\] Access denied for user '\w+'@'<HOST>' (to database '[^']*'|\(using password: (YES|NO)\))*\s*$
to:
failregex = ^%(__prefix_line)s(?:\d+ |\d{6} \s?\d{1,2}:\d{2}:\d{2} )?\[\w+\] Access denied for user '[^']+'@'<HOST>' (to database '[^']*'|\(using password: (YES|NO)\))*\s*$
You can test this using:
fail2ban-regex "2017-10-04T14:35:47.169756Z 218882 [Note] Access denied for user 'root'@'123.129.218.64' (using password: YES)" /etc/fail2ban/filter.d/mysqld-auth.conf
And you will see a match in the output:
Lines: 1 lines, 0 ignored, 1 matched, 0 missed [processed in 0.00 sec]