Log table using MySQL

MySQL does not offer anything like this, even because it is not his job to provide ready-made solutions to specific problems. You will have to create a structure that meets all your logging needs and make updates to the table in the traditional way. The log entries are given as any other, there is nothing special about it.

I will not even risk giving you a ready-made model because I do not know what you need.

Some will say to use a MyISAM table because it is simpler and faster. Others will tell you to use InnoDB for better scalability. Only you testing your situation will know which is best. Tunar this DB is more important.

What I can help you with is showing you TRIGGER which you can guarantee to always write to this table when you make some changes to other tables. As the name implies with it you create the triggers in tables, then any operation that was triggered will generate another query that will update the log table.

Another thing that may interest you is the implementation of temporal tables . Here's how to simulate it in response in 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 it 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 ;

Depending on what you want, it's not a log system you need.

Perhaps the most likely solution is to use an available log system in some library for your language, and it will mount or document how to make your table in MySQL. data is the least of the problems to take care of in a log system.

One option I've recently met is an auditor, there is an audit component provided by MySQL, the MySQL Audit Enterprise , and there is also MariaDB Audit Plugin , from MariaDB, which is an auditor, which generates several logs that can be very interesting, for auditing user actions in the database.

Below is an example of a 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 resolves, depending on the need.