As correctly identified in the release notes for ver. 0.10.5, MySQL v8.0+ has
deprecated a server config log-warnings and changed to log-error-verbosity
* `filter.d/mysqld-auth.conf`:
- MYSQL 8.0.13 compatibility (log-error-verbosity = 3), log-format contains few
additional words
enclosed in brackets after "[Note]" (gh-2314)
so the comments for fail2ban/config/filter.d/mysqld-auth.conf
# To log wrong MySQL access attempts add to /etc/my.cnf in [mysqld]:
# log-error=/var/log/mysqld.log
# log-warnings = 2
should be changed to (to avoid MySQL crashing on start up)
# To log wrong MySQL access attempts add to /etc/my.cnf in [mysqld]:
# log-error=/var/log/mysqld.log
# MySQL v8.0+
# log-error-verbosity = 3
# mariaDB
# log-warnings = 2
also MySQL [MY-010925] and MariaDB ER_ACCESS_DENIED_NO_PASSWORD_ERROR, error
messages have no white space after the ‘<HOST>’ regex so there needs a tweak
from
failregex = ^%(__prefix_line)s(?:(?:\d{6}|\d{4}-\d{2}-\d{2})[
T]\s?\d{1,2}:\d{2}:\d{2} )?(?:\d+ )?\[\w+\] (?:\[[^\]]+\] )*Access denied for
user '<F-USER>[^']+</F-USER>'@'<HOST>' (to database '[^']*'|\(using password:
(YES|NO)\))*\s*$
to
failregex = ^%(__prefix_line)s(?:(?:\d{6}|\d{4}-\d{2}-\d{2})[
T]\s?\d{1,2}:\d{2}:\d{2} )?(?:\d+ )?\[\w+\] (?:\[[^\]]+\] )*Access denied for
user '<F-USER>[^']+</F-USER>'@'<HOST>' ?(to database '[^']*'|\(using password:
(YES|NO)\))*\s*$
filter.d/mysqld-auth.conf line 20
which matches original test log
2019-01-03T08:50:04.634875Z 113 [Note] [MY-010926] [Server] Access denied for
user 'root'@'192.0.2.1' (using password: NO)
and
2022-04-16T08:37:50.024313Z 16 [Note] [MY-010925] [Server] Access denied for
user 'root'@'localhost'
2022-04-15 11:28:21 162 [Warning] Access denied for user 'root'@‘localhost'
Sources:
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
https://mariadb.com/docs/reference/mdb/error-codes/ER_ACCESS_DENIED_NO_PASSWORD_ERROR/
Additional notes:
1. It may be preferable in ./fail2ban/config/paths-common.conf to change from
mysql_log = %(syslog_daemon)s
to
mysql_log = /var/log/mysql/error.log
or similar so that the comment in mysql-auth.conf is the defacto error log
location across distros.
2. MariaDD setting log-warnings = 4 detects probes and unauthorised host
connections in the format
2022-04-15 16:35:46 175 [Warning] Aborted connection 175 to db: 'unconnected'
user: 'unauthenticated' host: '192.241.216.111' (CLOSE_CONNECTION)
which can be picked up by the regex
^%(__prefix_line)s(?:(?:\d{6}|\d{4}-\d{2}-\d{2})[ T]\s?\d{1,2}:\d{2}:\d{2}
)?(?:\d+ )?\[\w+\] (?:\[[^\]]+\] )*Aborted connection \d+ to ((db|user):
('unconnected'|'unauthenticated') )+host: '<HOST>' \(CLOSE_CONNECTION\)$
_______________________________________________
Fail2ban-users mailing list
Fail2ban-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/fail2ban-users
