How should I audit changes in a MySQL table (using MySQL 4)?

Question

I have been asked to audit any/all changes in a MySQL table. Does anyone know of any tools out there to help me do this or do I need to write my own solution?

If I write my own audting, my initial thought it to make a separate table and build a string of changes in the PHP code. Something like "fieldname1 -> oldvalue | fieldname2 -> oldvalue, ...". If you see a major problem with this method, please let me know.

Answer 1

The only sure-fire way to capture all changes to a DB table is to use triggers on the Server. The risk of modifing your own code to audit the changes is that changes from another application/user etc will not be captured.

Having said that, I'm not sure that MySQL 4 had trigger support.