MySQL – Copy On Write Transparent Database

MySQL

It is possible to create a transparent database to another production database server? I mean, testing new functions in a production database is too dangerous. What if we create a database which based on Copy On Write mechanism (because don't want to copy a big amount of data every time), and make some modification on that? The original production database will not modified, only the COW database. After the test, the COW database can be dropped or saved, etc., but the original can operate without any kind of danger.

Doe this solution exist? Has anyone implemented it?

Best Answer

I am not aware of anything doing this kind of operation within mysql itself. For Oracle there is something, I think it is called Oracle Workspace Manager, but I am not sure, there are a lot of options available.

For mysql I think you need to do this on storage level, e.g. use a filesystem with good cloning functionality like ZFS. Or a SAN.

  • Put the data part of the mysql instance on a seperate filesystem
  • Run prod instance like usual
  • Clone the data fs when needed (does not distured prod instance)
  • Configure a second mysql instance which uses the cloned fs as data dir
  • Fire up second instance, which will do a recovery now
  • Use second instance for testing

I have not setup this myself, so minor things might be missing, but generally this is a way to do this - and its not too complicated I think.

It will not help you for things like load testing as prod and test share the same storage subsystem, but for the usual tests it is fine.

Related Question