I'm trying to speed up a development task that I'm working on. I'm writing some code that is accessing and analyzing a large-ish MySQL database (about 5GB). I want to test my code as I go along. After making a code change, I need to try it out (which will do some inserts/updates/deletes on the DB). It'll take several iterations of code tweaks to get it working right. But after each iteration I need to restore the DB to the state it was before running the code. It's very time consuming to do a full DB drop/restore after each test, which is what I'm doing now. So, I'm looking for a way to simplify the rewind process - perhaps by logging DB changes with enough information such that the statements that manipulated data (done over the course of about 30 seconds) can be undone in reverse chronological order.
Does anyone know of any tools out there that will allow for a more rapid, incremental restore? Basically, is there a way replay the query log in reverse? Or, at the very least do a data diff against a snapshot in order to undo the recent changes?
FYI, I'm using MySQL 5.5.x with InnoDB. I'm coding in Ruby on Rails, but there's other non-Ruby code so ideally, I'd be looking for something more of a language independent command line utility that I could run before and after executing a test.
If you use a filesystem like btrfs or ZFS you can use filesystem snapshots to capture the state. If you keep a special partition for the database contents it should be easy to stop mysqld, go back to the snapshot and start the server again.