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

One way is to parse the log files, which will give you exact SQL statements executed in your database. I'm not exactly sure how to separate SQL statements made by your application from other applications (if thats the case)

Answer 2

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.

Answer 3

The only thing I can think of is to do some combination of a few somewhat hackey things:

1. Save a [temporary?] table of row IDs, to check for new rows. If you need to know what was in deleted or modified rows before, you'll need to copy the whole DB, which would be rather messy.

2. Have each row have a datestamp that gets modified on update; grab rows for whom the updated datestamp is newer than when the analysis started.

3. Have a layer between your application and the database (if you have something like the classic $db->query(), it would make this easy), log queries sent, which can then be looked at.

I suppose the real question is if you want to know what queries are being executed against the DB, or if you want to know what they queries you're running are actually doing.

Answer 4

I think normally your application would log any interesting changes as it makes them. Or you would set up history tables for everything with datetimes.

To do it the way you describe, you could dump the contents of the database into a file before and after your action and do a diff on the two files. In php, you can check out xdiff: http://us.php.net/manual/en/book.xdiff.php

If this is something you're doing only occasionally in controlled circumstances to test some queries you're not sure about, you can dump and diff on the command line.