Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

On a MySQL 5.1 Enterprise server db-dump takes a lot 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).

share|improve this question

5 Answers 5

up vote 2 down vote accepted

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.

share|improve this answer
    
+1 for clarification –  3molo Jul 20 '11 at 13:19

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.

share|improve this answer

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.

share|improve this answer

You can try mk-parallel-restore

share|improve this answer
    
It's OK as long as it is for a test environment. CAVEAT mk-parallel-restore has been deprecated by Percona. It still works but Percona no longer supports it for production use. In fact, these URLs explicitly state not to use mk-parallel-dump and mk-parallel-restore as backup programs : maatkit.org/doc/mk-parallel-dump.html and maatkit.org/doc/mk-parallel-restore.html –  RolandoMySQLDBA Jul 21 '11 at 17:07
    
I thought about. Since it is for a test environment only, this brief answer of yours deserves a +1 !!! –  RolandoMySQLDBA Jul 21 '11 at 17:10

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

share|improve this answer
    
XtraBackup and MySQL Enterprise Backup operate pretty much the same way. They both perform nonblocking backups using internal rsyncs and final flush before final rsync. –  RolandoMySQLDBA Jul 21 '11 at 16:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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