Mysql – Restore InnoDB to different machine

backupinnodbMySQLrestorextrabackup

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

Best Answer

OK, following @jynus pointers, I finally got the backups to restore. I ended up creating another mysql instance on the server and restoring there.

Just in case someone runs into the same problem, the steps I took are the following...

My environment is CentOS 6.4, MySQL 5.1 with stock InnoDB as far as I can tell, innobackupex --version gives me InnoDB Backup Utility v1.5.1-xtrabackup, xtrabackup --version gives me xtrabackup version 2.1.8 for Percona Server 5.1.73 and the server has Plesk 11.0.9 installed.

Firstly I set up another mysql instance by copying and modifying the default /etc/init.d/mysql startup script. The problem that I had there was that to load a different config file for that server, the --defaults-file parameter that mysqld_safe needs, has to be given before any other parameters (see here). I then copied the default /etc/my.cnf file and modified the values in there (port, log locations etc) to suit my setup.

To log in to the new mysql instance and set up a new root user, the first mysqld_safe run was with the --skip-grant-tables option in the init script (see here).

To be able to manage the new mysql instance with phpmyadmin I created a new database server in Plesk (Server->Database Servers->Add Database Server) and entered the new port and/or new ip the server is running on and the new admin user I set up in the previous step. Plesk won't let you use a "root" username so I had to add a different username with root privileges in the previous step.

After that, the server can be started/stopped/reloaded using something like /etc/init.d/mysql-backup start.

For the actual recovery process I extracted the backup file (taking care to use the -i flag for tar) and once in the backup dir, I applied the log using the xtrabackup executable itself (xtrabackup --prepare --target-dir=/path/to/extracted/backup/) because innobackupex requires that it connect to a running mysql server to determine the version, something that I didn't want to figure out how to do.

I stopped my new mysql server, copied over the folder that contained my database to the datadir, copied over the ibdata1 file, changed permissions and started the server.

I could then browse/manage my backup database using phpmyadmin.

Related Question