3

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?

3

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.

  • Sloution looks like will be LVM COW capability and iSCSI trough tunnel. Thanks for help – Dankó Dávid Jan 30 '14 at 15:28
  • ZFS snapshots use COW but they are read-only, to mutate them, they must be converted to a clone. Operating the "forked" DB instance as a COW can be done then if it's all on the same ZFS filesystem. But it would be cool if it was possible to have that clone live on another separate machine (while still retaining COW capability, so the clone is not a full copy), which would require some sort of networked ZFS. Perhaps this could be done with iSCSI with a zfs clone. – CMCDragonkai Jul 20 '17 at 4:32
1

I usually use a MySQL replication.

I have a Slave dedicated to the tests & validations of the "SQL Script Releases" (scripts made by the dev teams).

For DML (UPDATE, INSERT, DELETE), I run thoses scripts into a transaction and rollbacked it after (InnoDB tables only):

START TRANSACTION;
DELETE FROM myTable WHERE id = 4 LIMIT 1;
ROLLBACK;

For DDL (ALTER TABLE): I stop the replication, run my DDL commands, run a rollbacked script and restart replication:

STOP SLAVE;
ALTER TABLE myTable ADD INDEX (username);
ALTER TABLE myTable DROP INDEX username;
START SLAVE;

If you want to do more complicated tests (directly in you applications for instance) you should setup a real "Test Environment" from a fresh backup of your production.

Max.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.