2

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

Scott Pack
  • 14,717
  • 10
  • 51
  • 83
geoaxis
  • 451
  • 1
  • 6
  • 17

5 Answers5

2

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.

Community
  • 1
Mark Booth
  • 421
  • 9
  • 18
1

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.

3molo
  • 4,340
  • 5
  • 30
  • 46
1

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.

opsguy
  • 781
  • 1
  • 4
  • 12
1

You can try mk-parallel-restore

lg.
  • 4,579
  • 3
  • 20
  • 20
  • 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 : http://www.maatkit.org/doc/mk-parallel-dump.html and http://www.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
0

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

HTTP500
  • 4,827
  • 4
  • 22
  • 31
  • 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