How to enable logging of failed accesses on MySQL 8.0

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hi everyone,
just installed my first MySQL 8.0 version. I need to log all failed access to mysql. I added “log-error-verbosity = 3” on my “mysqld.cnf” file of my Debian 10 distribution and restarted the server. Then I tried to emulate a failed login, but this event is not saved by any log file (in particular, the /var/log/mysql/error.log remains empty).

How can I enable failed log attempts on MySQL 8.0 so that I can use it in conjunction with fail2ban?

2

Hi ultratonix thanks for asking. Can I just check with you if you’re using Percona Server for MySQL 8.0 or MySQL Community edition? I’m just wondering if the audit log plugin might help here? [URL]https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html[/URL]

3

Hi, thank you for your reply.
I’m using MySQL Community edition. At the moment, no failed login attempts on any of the log files…

4

You can use the Percona audit log plugin on MySQL Community edition. Just download the equivalent version of Percona Server from our downloads site, extract the audit_log.so file and put it in the plugin directory. Install the audit_log plugin and then test.

Here’s a sample of a failed login using wrong password:

<AUDIT_RECORD

NAME=“Connect”

RECORD=“12_2020-03-29T12:38:54”

TIMESTAMP=“2020-03-29T12:44:32Z”

CONNECTION_ID=“12”

STATUS=“1045”

USER=“root”

PRIV_USER=“root”

OS_LOGIN=“”

PROXY_USER=“”

HOST=“localhost”

IP=“”

DB=“”

/>

5

I use audit_log_plugin on MySQL 8.0.24 and the plugin is working, but the record time is not valid:

audit_record":{“name”:“Query”,“record”:“544629_1970-01-01T00:00:00”," timestamp":"2021-04-23T13:37:25Z ",

SETUP:
audit_log_format = JSON
audit_log_policy = ALL
audit_log_handler = SYSLOG
audit_log_strategy = SYNCHRONOUS
audit_log_syslog_facility = LOG_USER
audit_log_syslog_ident = MySQL-Audit-Log
audit_log_syslog_priority = LOG_INFO

Do you know what is the issue?

6

Hello @GergoGalamb
What do you mean by the record time is not valid?
The time is in UTC, that’s what the “Z” means at the end of the time