Getting a MySQL database difference

Question

I have a mysql database. What I'd like to do is perform an arbitrary action on it, and then figure out what changed. Something like this:

//assume connection to db already established

before();//saves db state
perform_action();//does stuff to db
diff();//prints what happened

I'd want it to output something like:

Row added in table_0 ]details]
Row added in table_1 [details]
Row modified in table_5 [details]
Row deleted in table_2 [details]

Any ideas?

---

To further clarify: You know how on stackoverflow, if you check a post's edits, you can see red lines/green highlights indicating what's been changed? I want something like that, but for mysql databases.

Answer 1

Instead of copying your whole database in order to save the state for a later diff, you might be better off by using triggers:

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

When you setup appropriate triggers, you can log changes to a table - for example, you can setup a trigger that automatically logs the old values and the new values for every update. To see the changes, query the table that was filled by the trigger.

Of course, the trigger is not restricted to changes made by your application, it will also log updates done by other applications. But this is also the case if you diff the old version of the database with the new version of the database.