Mysql takes alot of time to restore backup for test environment. How to work around it?

Question

On a MySQL 5.1 Enterprise server db-dump takes alot of time. I would like to reduce the amount of time to restore the db-dump or to be able to quickly go back to a known state. This machine is used for testing. So Lets say we have a DB Dump at point A. I do some changes to the DB with an application and after a while I want to go back to the same db dump. I have the following options.

• Delete every thing and start with a fresh db-dump restore, takes about 14 hours
• Use a backup of the entire mysql folder

Do I have any other options. Some way to quickly get back to state A. Or to speed up my dbdump. I will try to provide you with more details if you ask (I am not sure what should I be putting here)

Answer 1

As 3molo suggested, another option is to run your test server as a virtual machine.

Set up your test machine VM with the initial conditions of the test you want to run, take a snaphot and then run your tests. If you want to get back to a clean test setup, use the snapshot to revert the state of the whole VM.

Answer 2

Mysqldump:

Use '--single-transaction' with mysqldump.
Unless you're heavily cpu burden, use gzip.
Defrag the tables using 'OPTIMIZE TABLE' to release space.
Add more spindles.
Clear out unnecessary data.

Other options:

You can use a virtualization technology's snapshots.
Some filesystems support snapshots.
InnoDB support snapshots.

Answer 3

This feels like a space vs time trade off. The fastest restore you're going to get is just deleting the existing directory and copying the backed up copy of the data directory to the correct location.

The only catch is to make sure mysql is silent (either shut down or with a global read lock) to make sure the data on disk is consistent before you make your backup copy of the data directory.

Answer 4

You can try mk-parallel-restore

Answer 5

Checkout Xtrabackup. You didn't say what kind of tables you have but Xtrabackup's innobackupex script supports raw backups of InnoDB (and XtraDB) as well as MyISAM tables.

As @opsguy suggested it is a bit of a space tradeoff as a gzipped mysqldump usually is a lot less than a raw backup but Xtrabackup offers compression options too and there is no comparison in terms of a restore of a raw versus a logical backup.

HTH

Answer 6

You should look into performing parallel backups using shell scripting and mysqldump.

Every day I backup a MySQL Client whose Server has 800 user DBs (over 600 GB). It takes 4 hours. I perform parallel dumps of the DBs 50 at a time.

Since I have scripted the parallel backup of all DBs, I am sure the restoration of those backups to a test server can also easily be scripted.

You may want to script making a backup of a running server using rsync instead of XtraBackup.