Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

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

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

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

Your Answer

 
discard

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.