Database replication with ability to revert to previous states

Question

I want to setup a development database such that it'll initially be a mirror of the database used in production. At fixed points during the day (maybe midnight or something) and/or manually, I want it to be sync'd once again with the production DB.

My goal is to have a database I can play and mess with, with no lasting consequences. I've looked at replication, and it doesn't seem to be a good fit for this scenario. See, what I thought initially is that I could set up a slave, dump it, do my work, then use the dump to revert it to its previous (pristine?) state, before syncing with the master. Repeat when needed. While I might be able to get away with this with relatively small DBs, 8 gigs worth of dumps tend to take a while to be generated and imported. Plus I'd like to be able to have something that will scale to several devs.

Any hints as to where I should look next?

Answer 1

You can look into MySQL snapshots using the file system. More details in this link. Snapshots are very quick to create, and can be rolled back to.

Answer 2

For Oracle it's quite easy, a snapshot standby database would do the trick for you. Maybe Oracle will integrate this capability in mysql .... With 8GB databases, making a new copy should not give a lot of problems .... 15 minutes for a copy?