Restore InnoDB to different machine 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.

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 database management!

Following instructions that were given to me in an answer, I extracted the backup archive and "prepared" the backup with innobackupex --apply-log . which completed fine. I then stopped mysql, copied ALL the files from the backup dir to the mysql data dir and restarted mysql.

Once I log in though (after resetting the root password), 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. So I'm still at my original question of firstly how to restore to a different machine and secondly how to make sure my backups are usable whenever/wherever I need them.

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.

It seems that I'm doing everything correctly. My backup script includes

#!/bin/bash
BDIR="/disk2/backup/mysql"

/usr/bin/innobackupex --user=backup --password=xxx --ibbackup=xtrabackup --stream=tar /tmp | gzip -c -9 &gt; $BDIR/`date -u +%Y-%m-%dT%H:%M:%SZ`.tar.gz


which also rsyncs the files off-site.

I download the file, extract (tar -ixvzf), apply the log as mentioned above, stop mysql and copy the files to the datadir (cp), apply permissions, start up and log-in. Browsing to my backup db gives me the error


  140730 16:28:49 [ERROR] Cannot find or open table lead@002dmanager@002dbackup/crm_word_ban from
  the internal data dictionary of InnoDB though the .frm file for the
  table exists. Maybe you have deleted and recreated InnoDB data
  files but have forgotten to delete the corresponding .frm files
  of InnoDB tables, or you have moved .frm files to another database?
  or, the table contains indexes that this version of the engine
  doesn't support.


for all the db tables.

MySQL version on server is 5.1, local version is 5.5
Please refer to the official documentation on how to create, store and restore a full backup with Percona XtraBackup. Percona XtraBackup does guarantee having everything for no data loss:

It can be summarized as:


Perform the backup (innobackupex). You can optionally send it remotely, compress it, encrypt it, etc. at the same time the backup is running. This is the only point where you need access to the original database server.
If you did not send it remotely on the first step, do it now. A local backup, while useful, is not a real backup strategy. For the backup to be complete, you may want to copy also your config file (/etc/my.cnf and your binary executables, if you cannot get them from other source -your distro, for example).
At any time between the backup and the recovery process, you need to do the prepare phase (innobackupex --apply-log). This only requires the backup files, not the original server. This will create the transaction logs for you
After you have applied it, just copy back the files to the original directory while the server is down. You can do that with innobackupex --copy-back if you have the files locally, or just do (s)cp/rsync. Remember to change the file permissions to the appropriate user (usually the mysql user).
Start the server. You are done.


This will restore a full backup. To assure you this is a secure method, Galera cluster can use it to clone nodes automatically (without human intervention) and it is the safest and fastest method available.

If you do not see individual .ibd files, it could be for several reasons- either you are using MyISAM or another engine that do not use those, or you are using InnoDB with innodb_file_per_table = 0, or you lost some of the files on the copy process (or they have wrong permissions). Please provide the full error message and the error log since the last start, and a recursive listing of your datadir.

As the writer of the training for Percona XtraBackup, I am quite familiar with all the typical silly mistakes we do when working with it.



Thank you for the extra information, here are my suggestions as a follow up:


The latest versions of XtraBackup are not compatible with 5.1 without the innodb plugin. Are you using MySQL 5.1 with stock InnoDB (not the plugin, or Percona/MariaDB)? Also, recovering to a newer version of MySQL in binary form may require additional upgrade steps/precautions- it can be done in binary form. Suggestion: try using an older version of Xtrabackup or upgrading the server. 5.1 without the plugin is a 6-year-old release with very little support everywhere. I can also provide alternative backup tools if you want to stick to &lt;5.1 (but very few are so fast).
I do not like the database/table name- just to double check, can you confirm that db: lead@002dmanager@002dbackup table: crm_word_ban is the right name for one of your tables, and that it is the exact name of a .frm file? I want to discard filesystem issues.