I currently have backups that were taken with Percona XtraBackup of all my mySQL databases.

My specific scenario is that I want to restore a month old InnoDB database snapshot to my local machine for testing but I can't seem to find instructions for doing that in the Percona documentation.

Reading around on Google I come to the conclusion that I have to extract the tar.gz file on my machine and "replay" the transaction log but the log is on the remote machine.

So my question is what specific steps are needed and what are the caveats for restoring the DB snapshot to my machine.

Finally it seems that using XtraBackup by itself doesn't guarantee that you will be able to restore your data if something goes wrong. For example if the server was to die the backups I have would be useless without the transaction log. So a general phrasing of my question might be what steps need to be taken to ensure that I can restore my data on any machine I wish.

Please keep in mind that my main area of expertise is not server management!


Once you decompress XtraBackup archive the directory content should look like this:

[root@twindb-dev 2014-07-15_02-15-02]# ll
total 77872
-rw-r--r-- 1 root root      357 Jul 15 02:15 backup-my.cnf
drwx------ 2 root root     4096 Jul 15 02:15 dictionary
-rw-r----- 1 root root 79691776 Jul 15 02:15 ibdata1
drwx------ 2 root root     4096 Jul 15 02:15 mysql
drwxr-xr-x 2 root root     4096 Jul 15 02:15 performance_schema
drwx------ 2 root root     4096 Jul 15 02:15 sakila
drwx------ 2 root root     4096 Jul 15 02:15 sakila_recovered
drwx------ 2 root root     4096 Jul 15 02:15 test
drwx------ 2 root root     4096 Jul 15 02:15 twindb
-rw-r--r-- 1 root root       13 Jul 15 02:15 xtrabackup_binary
-rw-r----- 1 root root       93 Jul 15 02:15 xtrabackup_checkpoints
-rw-r----- 1 root root     2560 Jul 15 02:15 xtrabackup_logfile
[root@twindb-dev 2014-07-15_02-15-02]#

The transactional log you were talking about is xtrabackup_logfile, it's not on a remote machine, it's included in XtraBackup archive.

So, now you have to apply the log (they call it "replay" log or "prepare" backup)

To do so run innobackupex with --apply-log option

[root@twindb-dev 2014-07-15_02-15-02]# innobackupex --apply-log .

If you see successful complete:

InnoDB: Shutdown completed; log sequence number 666258394
140715 02:20:30  innobackupex: completed OK!
[root@twindb-dev 2014-07-15_02-15-02]#

then content of 2014-07-15_02-15-02 is ready to use.

Copy all files from 2014-07-15_02-15-02 to your datadir (by default on Linux it's /var/lib/mysql)

[root@twindb-dev 2014-07-15_02-15-02]# cp -R * /var/lib/mysql

Fix ownership

# chown -R mysql:mysql  /var/lib/mysql

And start MySQL

# /etc/init.d/mysql start
  • I followed your instructions, they worked up to a point. Once I log in, I can see the DB's and their tables (using adminer) but once I select one I'm hit with a table does not exist error. On a side note, from what I'm reading there should be an .idb file for each table but I don't see anything like that. – user67435 Jul 29 '14 at 23:50
  • Your backup seems to be broken. If there are no .ibd files then either innodb_file_per_table is OFF and all tables are supposed to be in ibdata1. If so, maybe ibdata1 is corrupt, or the dictionary. I can only guess based on the given information. Anyway, the procedure above should work for any valid backup copy taken with Xtrabackup – akuzminsky Aug 1 '14 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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