Log table using Mysql

Question

How to create a table of log Mysql audit that stores when and what was changed in each record? Mysql provides something that can facilitate this kind of thing?

Answer 1

One option I’ve met recently is an auditor, there is an audit component provided by Mysql, the Mysql Audit Enterprise, and there is also the Mariadb Audit Plugin, of Mariadb, which is an auditor, which generates several logs that can be quite interesting, for auditing the actions of users in the database.

Below is an example log file generated by Mariadb Audit in Mysql.

20150831 14:56:48,NoteCelso,root,localhost,326,7,QUERY,,'SET GLOBAL server_audit_logging=ON',0
20150831 14:56:51,NoteCelso,root,localhost,326,0,DISCONNECT,,,0
20150831 14:56:54,NoteCelso,root,localhost,327,0,FAILED_CONNECT,,,1045
20150831 14:56:54,NoteCelso,root,localhost,327,0,DISCONNECT,,,0
20150831 14:56:57,NoteCelso,root,localhost,328,0,CONNECT,,,0
20150831 14:56:57,NoteCelso,root,localhost,328,10,QUERY,,'select @@version_comment limit 1',0
20150831 14:57:02,NoteCelso,root,localhost,328,11,QUERY,,'show databases',0
20150831 14:57:38,NoteCelso,root,localhost,328,0,DISCONNECT,,,0
20150831 14:58:05,NoteCelso,root,localhost,230,12,QUERY,,'select * from extratos LIMIT 0, 1000',0
20150831 16:46:03,NoteCelso,root,localhost,406,439,QUERY,,'SET NAMES \'utf8\'',0
20150831 16:46:03,NoteCelso,root,localhost,406,440,QUERY,,'SET character_set_connection=utf8',0
20150831 16:46:03,NoteCelso,root,localhost,406,441,QUERY,,'SET character_set_client=utf8',0
20150831 16:46:03,NoteCelso,root,localhost,406,442,QUERY,,'SET character_set_results=utf8',0
20150831 16:46:03,NoteCelso,root,localhost,406,443,QUERY,,'SELECT SQL_CALC_FOUND_ROWS idVendedor, nome, saldo_verba, saldo_verba_pendente
        FROM   vendedores

        ORDER BY  idVendedor
                    asc
        LIMIT 0, 9',0
20150831 16:46:03,NoteCelso,root,localhost,406,444,QUERY,,'SELECT FOUND_ROWS()',0
20150831 16:46:03,NoteCelso,root,localhost,406,445,QUERY,,'SELECT COUNT(idVendedor)
        FROM   vendedores',0
20150831 16:46:03,NoteCelso,root,localhost,406,0,DISCONNECT,,,0

In some cases a solution of this already solves, depending on the need.

Answer 2

Mysql does not provide anything like this, because it is not its function to give ready-made solutions to specific problems. You will have to create a structure that meets all your needs log and make the table updates in the traditional way. The entries of the log are given like any other, has nothing special in it.

Nor will I risk giving a model ready because I don’t know what you need.

Some will say to use a Myisam table for being simpler and faster. Others will say to use Innodb for allowing better scalability. Only you testing your situation will know which is more suitable. Tunar this DB is more important.

What I can help you with is to show you TRIGGER you can ensure to always write in this table when you make any changes in other tables. As its name indicates with it you create the triggers in tables, so any operation that has been triggered will generate another query which will update the log.

Another thing that might interest you is the implementation of temporal Tables. See how to simulate in Mysql on reply on the DBA.SE and in the OS.

CREATE TABLE `data_tracking` (
`tracking_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`data_id` INT NOT NULL ,
`field` VARCHAR( 50 ) NOT NULL ,
`old_value` INT NOT NULL ,
`new_value` INT NOT NULL ,
`modified` DATETIME NOT NULL
) ENGINE = MYISAM ;

To update it would do this (you can generalize and use for any table):

DELIMITER $$

DROP TRIGGER `update_data `$$

CREATE TRIGGER `update_data` AFTER UPDATE on `data_table`
FOR EACH ROW
BEGIN
    IF (NEW.field1 != OLD.field1) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field1", OLD.field1, NEW.field1, NOW());
    END IF;
    IF (NEW.field2 != OLD.field2) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field2", OLD.field2, NEW.field2, NOW());
    END IF;
    IF (NEW.field3 != OLD.field3) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field3", OLD.field3, NEW.field3, NOW());
    END IF;
END$$

DELIMITER ;

I put in the Github for future reference.

Depending on what you want, it’s not a log who needs.

Perhaps the most appropriate solution is probably to use a log available in some library for your language and she will be in charge of assembling or documenting how to make your table in Mysql. Mounting the table in the database is the least of the problems to take care of in a log.