We have the database design requirement for audit trail purpose in MySQL 5.7.

There is one main table and there are few sub-tables to the main table. For every record in the main table, there will be multiple records in the sub-tables. If there is any change in any of the tables, we want to capture all the information in all the tables for a main table record together.

We initially thought of designing a single table for audit trail which consists of all the columns of all these tables. Whenever there is a change, trigger will capture the pre-image in this table and sub-table records for one main-table record would be stored in one column per sub-table in JSON format.

But we may make the change of 1 million records at the same time. Using trigger is still fine, but manipulation of all the records to put it in a JSON format would kill the performance.

The audit trail is not for reconstructing inserts or to see the entire database changes. If something got changed in any of the tables, we want to identify who changed it and when.

closed as unclear what you're asking by mustaccio, Laurenz Albe, Marcello Miorelli, hot2use, Marco Sep 12 at 8:10

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • There's loads of stuff out there about setting up audit trails in MySQL. I don't see why you want to use JSON for this - it doesn't strike me as a JSON type task. Triggers into other tables is normally the way to go - or Googling "mysql cdc" gave me this - might be worth a look. p.s. welcome to the forum! :-) – Vérace Sep 9 at 7:30

Browse other questions tagged or ask your own question.