Track user&date service use - MySQL 8.0

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hi everyone,

Apologies if I missed any related discussions on this topic.

We use machines running MySQL services, and I want to track user logins to delete users who haven’t connected in the last 180 days .

I thought of creating a table with user_name and last_login_date. When a user connects to the service, it would check if the user exists; if they do, it updates the date; if not, it adds a new entry.

I’ve encountered some issues:

  • Using audit logs overwhelms my logs, and cleaning them up doesn’t solve the problem.
  • Filtering the logs didn’t help.
  • Using triggers didn’t work, as we have multiple databases and a large number of connections.

Do you have any suggestions? Does Percona have a tool that could help with this?

Tnx in advance !!

2

The audit log is the only way you can track login/disconnect in MySQL. You say filtering didn’t help, but what filtering did you try? I would set this filter:

SET GLOBAL audit_log_include_commands= 'set_option';

This limits the log entries to only login, logout, and if any user runs SET ... which should drastically reduce your log entries.

3

Thank you for the quick replay !
I did ‘exclude’ for a lot commands - set_connection among them
I will try it !