Restore InnoDB to different machine

Question

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

Answer 1

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.

Answer 2

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:

1. 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.

2. 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).

3. 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

4. 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).

5. 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:

1. 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 <5.1 (but very few are so fast).

2. 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.