As I recently started using MySQL (with many years of SQL Server under my belt), here are some quick tips on restoring an InnobackupEx backup.  There was a context switch for me  but I’m enjoying the experience.

Introduction

There are many ways to backup / restore database (s) for MySQL but the mechanism I’m referring to here is Percona’s InnobackupEx 2.1 for MySQL.  The instructions for this backup can be found at Preparing a Full Backup with innobackupex and  Making a Full Backup.  What’s great about this type of backup is that it backups all of the databases on the server so you can restore an entire server if it was ever to be problematic or you wanted to build a reporting replica.

Configuration

As with any typical Linux environment, it is all in the flavour and configuration you are working with.  Some particulars about the environment I’m working with:

– InnoDB mode (vs  myisam) with innodb_file_per_table configured so that way each table has its own .frm and .ibd files
-Red Hat Enterprise Linux 6
-MySQL 5.5
-Behind firewall

Steps

The steps to restore a full backup can be found at Restoring a Full Backup with innobackupex.  Here are some additional steps added in to make it a little bit more complete.

1. Copy the innobackupex to an easily accessible / robust location (e.g. another drive on the same box, fast DAS access, etc.)

2. Shutdown MySQL if you have not already done so.

e.g. mysqladmin –u <user> –p<pwd> shutdown

3. Be careful of the next step, what you are going to do here is delete the entire MySQL database folder of the existing server.  If this is a fresh installation, this is probably not that drastic a step.  But make sure you do not need the databases on this server (or already have them copied to another server).

rm –fR /the/location/of/MySQL/datadir/

4. Tip:  In some cases, you will get an error message when running innobackupex if you had specified log=/location/for/mysql.log as opposed to general-log=/location/for/mysql.log within the /etc/my.cnf.  If this is the case, ensure that log= is commented out so that way it is using general-log

5. Often the innobackupex will not have the log files with it. You will need to generate new log files (ib_logfileX) so that way the log sequence numbers (LSN) for the database and MySQL server are in sync.  You can work around this problem by running mysql in recovery mode as per Starting InnoDB on a Corrupted Database but this can be problematic even if your only goal is to run mysqldump to extract the files.  Thanks to Joseph McHenry for pointing this one out.

innobackupex --defaults-file=[source-server-my.cnf] --apply-log  --use-memory=2G [backup-source-location]

6. Restore the server databases by using the  –copy-back parameter.

innobackupex --copy-back /backup/source/location

7.  Tip:  In some cases, you will get an error message when running mysqld (to start the MySQL daemon) if you had specified general-log=/location/for/mysql.log as opposed to log=/location/for/mysql.log within the /etc/my.cnf.  If this is the case, ensure that general-log= is commented out so that way it is using log

8. Start your MySQL service (e.g. service mysql start, mysqld)

Conclusion

With this, you should be up and running with a fully restored MySQL server with all of its databases – with the LSNs in sync.

Enjoy!