MYSQL - keep track of record changes

[peterchan75]

Hi All,
I am trying to setup a database consisting of 5 tables. The records in these 5 tables are subjected to changes. Need to keep track of changes for audit trail purposes. Any tip or suggestion is much appreciated. Thanks in advance.

 

[davidktw]

What you want is putting in triggers
https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

In this way, you can setup a separate table and record down the changes.

Another way is to activate the audit log functionality for enterprise mysql. If you are using AWS RDS, this feature is available too.

Otherwise you can also perform logging at your application tier at the driver level or ORM if you are using one

I also found this might interest you. Triggers are synchronous, so for high load tables, there will be performance penalty, if so, you can intercept via binlogs using this tool http://maxwells-daemon.io/. Take note that binlogs do rotate.

 

[peterchan75]

Thanks David.

Alternatively, can you provide contacts for a software contractor can provide a turnkey solution for a local SME.

 

[davidktw]

Thanks David.

Alternatively, can you provide contacts for a software contractor can provide a turnkey solution for a local SME.

I am afraid this you will need to look it up yourself. I don't do business recommendation in this forum.

 

[davidktw]

Because HWZ can't easily enter SQL text without incurring WAF filtering, I have included a simple trigger technique using images

 

[davidktw]

As you can observe the method above is kinda rigid even though it works, but doesn't scale well for large number of tables.

However if you are using Java applications, you can perform logging via the MySQL JDBC drivers.

 

[Trader11]

Why not use Mariadb??

 

[jackoats.sg]

Just a silly idea, have you explored the possibility of combining all the 5 tables into one big one and store it in Google Sheets?

You have all the audit trail provided there.

Hi All,
I am trying to setup a database consisting of 5 tables. The records in these 5 tables are subjected to changes. Need to keep track of changes for audit trail purposes. Any tip or suggestion is much appreciated. Thanks in advance.

 

[peterchan75]

Thanks for all the suggestions.

 

[Hafi]

Thanks David.

Alternatively, can you provide contacts for a software contractor can provide a turnkey solution for a local SME.

you might want to specifically list out what your SME intend to do , maybe there SaaS available or even Zapier integrations which can accomplish the task.

 

[cwchong]

Personally i would think logging changes to the database, by writing to the database, a less than ideal scenario; unless it is already abstracted in the application layer

Logging changes to a log file in a standard format would be a better design imo

You can view said logs in ur local logviewet or pipe to services like stackdriver to do more analysis

 

[peterchan75]

Thanks again. Background to this thread, it's a Company Secretarial System i.e. keeping track of company shareholders and compliance. The user already has a cloud based system but has an impression that it's not cost competitive. Might not be a good idea to create a system where multiple solutions already exist in the market.

 

[Trader11]

Personally i would think logging changes to the database, by writing to the database, a less than ideal scenario; unless it is already abstracted in the application layer

Logging changes to a log file in a standard format would be a better design imo

You can view said logs in ur local logviewet or pipe to services like stackdriver to do more analysis

Log the orm queries

 

[Hafi]

Hi All,
I am trying to setup a database consisting of 5 tables. The records in these 5 tables are subjected to changes. Need to keep track of changes for audit trail purposes. Any tip or suggestion is much appreciated. Thanks in advance.

maybe check out Airtable to see if its fit what you are trying to do.

 

[Azzizz81]

There are some db plugins that allow u to keep audit trail

Sent from Sinkieland using GAGT