MySQL 8.0+ deprecated config log-warnings in filter.d/mysqld-auth.conf comments, massaging MariaDB, tuning regex

[PhilipClarke]

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]" (Filter: mysqld-auth not compatible with MySQL 8.0.13 #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 ‘’ regex so there needs a tweak from

filter.d/mysqld-auth.conf line 20

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*$

which matches original test log line

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\)$