mysqlfail2ban

MYSQL error.log showing failed login attempts as [note]

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.

Solution

  • 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]