Create a custom log table in MySQL

Question

Is it possible to create a custom log table. For example I have 10 schema and I would like to log if somebody is using the "areva" schema. I want to know the last executed command time and the user. Is this possible in MySQL?

Answer 1

I experimented with trying to create a trigger for a system table (mysql.slow_log). It simply cannot be done. However, there is a rather unconventional method you may want to try:

Step 01) Activate the slow_log with the output as a table and restart mysql

[mysqld]
log-output=TABLE
long-query_time=0.0001

Step 02) Convert slow_log to MyISAM, copy it to areva_log, and index slow_log on db

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX (start_time);
CREATE TABLE mysql.areva_log LIKE mysql.slow_log;
ALTER TABLE mysql.slow_log ADD INDEX (db);
SET GLOBAL slow_query_log = @old_log_state;

Step 03) Create a Stored Procedure to Copy all Slow Log entries whose db is areva into mysql.areva_log

DELIMITER $$
DROP PROCEDURE IF EXISTS `mysql`.`AppendToArevaLog` $$
CREATE PROCEDURE `mysql`.`AppendToArevaLog` ()
BEGIN
    SET @old_log_state = @@global.slow_query_log;
    SET GLOBAL slow_query_log = 'OFF';
    INSERT INTO mysql.aerva_log
    SELECT * FROM mysql.slow_log WHERE db = 'areva';
    SET GLOBAL slow_query_log = @old_log_state;
END $$
DELIMITER ;

Step 04) Call the stored procedure every 15 minutes using a crontab or using a mysql event

Step 05) Zap the slow log every week, month, or year:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
CREATE TABLE mysql.slow_log_empty LIKE mysql.slow_log;
ALTER TABLE mysql.slow_log RENAME mysql.slow_log_to_zap;
ALTER TABLE mysql.slow_log_empty RENAME mysql.slow_log;
SET GLOBAL slow_query_log = @old_log_state;
DROP TABLE mysql.slow_log_to_zap;

Give it a Try !!!