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?
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.
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?