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!
Assumptions:
· 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
/bkp
/bkp/Hourly
/bkp/Daily
/bkp/Monthly
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/
ls
(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;
Quit MYSQL:
\q
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>
--eof
-noveck
