Home / server / Questions / 292167
Accepted
geoaxis
Asked: 2011-07-21 00:50:32 +0800 CST

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

  • 772

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).

mysql backup-restoration

5 Answers

  1. Best Answer

    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.

    • 2

  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.

    • 1

  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.

    • 1

  4. You can try mk-parallel-restore

    • 1

  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

    • 0