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 > $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