Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted

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


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

DROP PROCEDURE IF EXISTS `mysql`.`AppendToArevaLog` $$
CREATE PROCEDURE `mysql`.`AppendToArevaLog` ()
    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 $$

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 !!!

share|improve this answer
Thank you very much, it's working really good:D – OHLÁLÁ Nov 24 '11 at 8:27

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.