How should I check for changes to a MySQL table (using MySQL 4)?

Question

I was asked to check any / all changes to the MySQL table. Does anyone know any tools out there to help me do this, or do I need to write my own solution?

If I write my own audience, my initial thought led her to make a separate table and create a row of changes in the PHP code. Something like "fieldname1 → oldvalue | fieldname2 → oldvalue, ...". If you see a serious problem with this method, let me know.

Answer 1

The only reliable way to capture all the changes in the database table is to use triggers on the server. The risk of changing your own code to check for changes is that the changes are from another application / user, etc. Will not be fixed.

Having said that, I'm not sure MySQL 4 supports the trigger.

Answer 2

Use trigger to detect changes and write before / after values ​​in the log table.

Answer 3

If you complete the distribution manually due to the lack of trigger support, I highly recommend that you do not just discard the changes in the string blob. One day you will be prompted to request this data, and you will have to do a bunch of string parsing to return the data. (I speak from experience here.)

The easiest way is to create a shadow audit table that has all the same columns as the original table, plus a change date column and a sequential identifier. Then you have a whole story to reconstruct in any format you need, and you can request it as you wish.

Answer 4

SoftTree DB Audit supports MySQL, can do what you need:

http://www.softtreetech.com/idbaudit.htm