Mysql – InnoDB MySQL account usage history

innodblogloginsMySQL

I would like to view the account usage history. I know it can be viewed
via my.cnf through the general query log. But it will slow down the usage of the
DB significantly.

Is there a way to mitigate this or is there another way?

Best Answer

It depends on what you want to log.

One solution (taken from here) that does not use the general query log is the following:

CREATE DATABASE admin;

CREATE TABLE admin.connections (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, connect_time DATETIME NOT NULL, user_host VARCHAR(50) NOT NULL, connection_id INT UNSIGNED NOT NULL);

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user, connection_id) VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

This solution uses the init_connect MySQL variable which stores SQL queries executed each time a user logs in. Now each time a user logs in, it will be logged into the table admin.connections. Be sure to check the permissions of all of your users to ensure that they can insert a record into the table.

Another solution, available only for MySQL Enterprise and that I include here for completeness' sake, is to install the MySQL Enterprise Audit plugin.

Related Question