Percona are doing great job on Xtrabackup which help us a lot. We use it on most of our MySQL server, and it works quite fine. Xtrabckup not only helps us make the daily backup(sometimes recovery), but also help a lot setup the replication with less impact on the online server.

It is pleasure to share my experience here,which may make Xtrabackup works better.

1. Is the process of “copy-back” necessary?

Although the wiki tells us do the “copy-back” after the “apply-log”, but people found it is not necessary. Actually it will take a lot of time if you have a huge databases(e.g. > 50G).

Here is the usual process to make recovery from a tar backup,which I did in the last month.

* Extraction of the tar stream:

it took about 53 minutes. The tar.gz file was about 46G,and after extraction the files were total 170G.

* Apply log

it took about 18 minutes which depends on the server’s QPS when making the backup and the DB size.

* the “copy back”

Now, I did the process “copy-back”,which took about 63 minutes.(170G/63Min = 45MB/s). The total time spent: 53+18+63 = 134. Now we notice that “copy-back” took about half of the recovery time.

After that , I do not do the “copy-back”, and try “mv” instead,which is quick and works fine.Since most time when recovery were doing, the situation would be an emergence. So,every minute we save will be valuable.

2. Backup without any lock

Xtrabackupex is cool, and can do real online backup.Innobackupex is convenient,which also make the backup of MyISAM tables,but it will set a lock, which can help u to get the master or slave position , on all the tables. Sometime, the lock last about 60 second and most time it’s ok because we always make backup at the very morning.

From 0.95rc innobackupex can use option --no-lock to avoid this lock if ALL your tables are InnoDB and you DO NOT CARE about binary log position of backup. It’s awesome.

3. Again do not forget the -i

“You must use -i (--ignore-zeros) option for extraction of the tar stream.”