Insert and Update Queries Log in MySQL

Question

I have sensitive information in multiple databases on the MySQL Server. I need to record which user have inserted or updated the record and incase of update we should be able to see the previous and the new values.

Is it possible in MySQL?

Answer 1

Usually for this purpose, I add a column in the table saying end user who updated it, and each client populates it when it it inserted/updated.

But if want to track each changes, usually I also add 2 datetime(6) columns in the table called StartDate and EndDate (use DateTime for mysql version before 5.6 not supporting microseconds)

EndDate is added to primary index (preferably at the end).

When row I insert a row into it, I put Sysdate(6) in StartDate and '9999-12-31' in EndDate (year 9999, yes it is hardcoded but if your application is still active at this time, it won't be your problem)

When I want to update an existing row, I close it first with a query like update MyTable set EndDate=sysdate(6), UpdatedBy='currentuser' where key=somekeyvalue and EndDate='9999-12-31' then I insert row with new values as explained earlier

When I look to current values, I always add " and EndDate='9999-12-31') in query

When I look to values applicable on a certain date, ie 2017-02-01 I perform query on a range of dates like this select * from MyTable where key=somekeyvalue and StartDate<='2017-02-01' and EndDate>'2017-02-01'

Answer 2

You can create a specific text log file using mysql CSV engine like:

CREATE TABLE sensitivechanges 
(mytable VARCHAR(30) NOT NULL, mykey text NOT NULL, 
oldvalue text NOT NULL, newvalue text NOT NULL, 
mydate datetime(6) NOT NULL, updatedByUser varchar(30)NOT NULL) 
ENGINE=CSV;

and populate it by a trigger that is activated on a change :

something like :

INSERT INTO sensitivechanges (mytable, mykey, oldvalue, newvalue, mydate, updatedByUser) 
VALUES ('table', 'key', 'oldvalue', 'newvalue', sysdate(6), 'user');

csv files generated by mysql can be recycled (for example every day) by an administrator :

1. use query flush table sensitivechanges so that recent changes are stored in the file
2. the file is called sensitivechanges.CSV when you rotate it, leave an empty file
3. query again : flush table sensitivechanges;
4. don't change sensitivechanges.CSM file
5. if you have slave, keep in mind that if you empty the file with a system command, it will grow on your slave, so don't replicate it on track changes only the slave

you can also

perform rotation with some queries :

CREATE TABLE sensitivechanges_bkp20170201 ENGINE=CSV as select * from sensitivechanges ;
flush table sensitivechanges_bkp20170201;

truncate sensitivechanges;

zip sensitivechanges_bkp20170201.csv

drop TABLE sensitivechanges_bkp20170201;

orginal question was about mysql-5.5 which doesn't support datetime(6) (in microsecond), so replace datetime(6) by datetime and sysdate(6) by sysdate()

Answer 3

• General query log will log every user activity - Can grow huge.
• You can use binary logs to review changes but again you won't be able to see user info / previous version of the record.

So here I'd suggest to implement triggers on the tables you require the logging. It'd create additional load to the system but if that's the requirement...

Answer 4

Having the application record the before and after details would be a monumental pain - it would have to know the existing content before doing the update, which would add an extra read query, and you'd have to ensure that this was done in all areas of the application, and it would be easy to missing an area / bypass the audit requirement.

A trigger is probably the best way to go with this, but there are some caveats around doing it this way. I agree with @rick-james comments on the other answer regarding triggers, but you could mitigate the drawbacks partially.

• You should only enable the triggers on the tables you want to audit, and not across all tables, as that can cause a lot of additional overhead.

• If your application uses a common user account to connect to the db, then the trigger will only be able to record against that user, and not the specific user of the application - for that you would need to also write the actual user information to the table you are auditing at the same time as the data update, and pull that information out into your audit table within the trigger

• If you write the audit information to a separate database, into a single table which holds information about the update such as timestamp, user, table, field, before_content, after_content - this would then record a row for each field that was updated, rather than the entire row

• You could define the audit database / table using the blackhole storage engine, and then replicate that database to a dedicated slave (excluded from all other slaves) so that you can more easily restrict access to the information - the slave would need to use a physical storage engine such as innodb or archive. This would also not use much more storage on the master other than the extra binary log content

• all audit analysis could then be done against the dedicated slave and not the master, as this table could grow rapidly depending on the application.