How do you determine how much information you should keep in

How do you determine how much information you should keep in the audit trail in MySQL?

Solution

we need to keep track of changes were made to the database and by whom this concept is called as Audit trail or Audit logging.

To store audit trail information we need to create history table for each data table you want to track(example query below).This table will have an entry for each insert, update, and delete query performed on each row in the data table.

The structure of the history table will be the same as the data table it tracks except for three additional columns: a column to store the operation that occured (let\'s call it \'action\'), the date and time of the operation, and a column to store a sequence number (\'revision\'), which increments per operation and is grouped by the primary key column of the data table.

To do this sequencing behavior a two column (composite) index is created on the primary key column and revision column. Note that you can only do sequencing in this fashion if the engine used by the history table is MyISAM (See \'MyISAM Notes\' on this page)

The history table is fairly easy to create. In the ALTER TABLE query below (and in the trigger queries below that), replace \'primary_key_column\' with the actual name of that column in your data table.

CREATE TABLE MyDB.data_history LIKE MyDB.data;

And you\'re done. Now, all the inserts, updates and deletes in \'MyDb.data\' will be recorded in \'MyDb.data_history\', giving you a history table like this (minus the contrived \'data_columns\' column)

To display the changes for a given column or columns from update to update, you\'ll need to join the history table to itself on the primary key and sequence columns. You could create a view for this purpose, for example:

This trigger will copy an old value to a history table if it is changed when someone edits a row. Editor ID and last mod are stored in the original table every time someone edits that row; the time corresponds to when it was changed to its current form.