Monday, March 11, 2013

InnoDB Restore Backup Procedure

My previous post elaborated on some scripts on backing up a MySQL Database with the InnoDB storage engine.

This post documents the restore procedure using the actual backup generated. Steps 7 and 8 are optional, but recommended. Better safe than sorry!

· Target restore candidate server has Percona Xtrabackup installed.
· Target database server (restore candidate), has no active/production databases running.

Test Environment:
CentOS 5.x
MySQL 5.0 (distro version)
Percona Xtrabackup 2.0.3 installed as per this howto

Backup Directory Structure

0. Got root? 
1. Locate appropriate backup file
Assumption that it is compressed like the Daily or Monthly in my previous post.

cp –r /bkp/Daily/mydbnamexxxxxx.tgz /root/Desktop

2. Uncompress backup file

cd /root/Desktop
tar xzf mydbnamexxxxxx.tgz

3. Prepare the backup file 
cd /path/to/extracted/backup/ 
(expected output should be ibdata1 file, folder containing database base and other xtrabackup named files)

innobackupex –apply-log /root/Desktop/*_dbxxxx/201xxxxx/

4. Stop the MYSQL Daemon and copy the files from the desktop to the mysql directory
service mysqld stop
rsync –rvt --exclude ‘xtrabackup_checkpoints’ --exclude ‘xtrabackup_logfile’ * /var/lib/mysql

5. Change ownership of the restored files and restart the MYSQL Daemon
chown –R mysql:mysql /var/lib/mysql
chmod –R 771 /var/lib/mysql
service mysqld start

6. Login to mysql and ensure the database has been restored via a table count
(It helps to have this documented in your production environment)
mysql –u root –p
<enter password when prompted>
show databases;
use mydbname; (use appropriate database name from list)
show tables;

7: Execute a mysqlcheck with check flag to determine any data inconsistencies
mysqlcheck –c mydbname –u root –p
<enter password when prompted>

8: Execute a mysqlcheck with optimize flag to optimize restored table data
mysqlcheck –o mydbname –u root –p
<enter password when prompted>