Procedure for backing up live DB for test system

[drew010]

For doing development, we have a separate development database server used for testing where data can be modified or erased or added to freely but the system gains over 500,000 new records a day coming from systems in the field.

To be able to test effectively, it is important that our test DB has data that is fairly fresh (within the last 15 minutes to an hour depending on what we are doing).

The test systems are completely segregated from the live system, so create a replication slave doesn’t seem ideal, especially since we would like to be able to change data without effective the live system.

I thought it might make sense to use Xtrabackup to help copy data from the live system to the test system periodically and maybe even on demand at the click of a button.

I was thinking about doing incrementals and then using rsync to copy the data from the live to the test system (we don’t care if the test system goes down now and again for updates).

I was just wondering if this makes sense or if anyone has done something similar and had some wisdom to share on the matter.

Thanks a lot.

[scott.nemes]

Incremental backups could work, but they can be a pain to maintain properly and you may run into issues if you are changing the schema that the incremental backups would later be applied to.

What we do is take a daily backup from one of our replication slave servers each night, and then I have a script that distributes that backup to all of our test environments into a “daily” backup folder. Some of the test servers are then instructed to kick off an automatic restore, so they get the fresh snapshot loaded daily. Other servers do not automatically reload, but are able to be reloaded on-demand with the latest snapshot.

This process takes a while to complete, but since it’s done late at night it works out for us since we work a typical workday.

[toddiuszho]

scott.nemes wrote on Wed, 30 May 2012 08:29

Some of the test servers are then instructed to kick off an automatic restore, so they get the fresh snapshot loaded daily.

Do the test servers have to stop the percona process, or can the restore be done live? Will it still work if the schema named changes from the restore to the destination?