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!

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

InnoDB Backup Scripts

Following up on my earlier post about converting the storage engine on a MySQL Database from MyISAM to InnoDB, I'd like to share the following scripts with backup rotation built in.
The next blog entry should be a step-by-step restore procedure using the actual backups below.

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

Script Output Log Directory
/tmp

Script Directory
/scripts

Disclaimer: I do not guarantee this is the BEST way of doing this, but it works for me. Copy and paste the following into a *.sh file in your scripts directory. Ensure the executable flag is set

chmod +x scriptname.sh

Actual Scripts
innodb_backup_monthly.sh

#!/bin/sh
# An InnoDB Backup Script to backup database Monthly
#
# Written by: Noveck Gowandan
# 02-10-2012
# Version 1.1
# Modified filename convention
# Uses tar-gzip to further compress final archive
# Added script timer and modified output to log time
# Start timer

time_start=`date +%s`

# Go to backup location and create Monthly folder with datestamp
cd /bkp/
mkdir M_db_$(date +%Y%m%d)

# Execute backup using innobackupex and send to folder created previously
innobackupex --defaults-file=/etc/my.cnf --user=****** --password=****** --databases=mydbname /bkp/M_db_$(date +%Y%m%d)

# Compress backup into a tarball
tar czf mydbname_$(date +%Y%m%d).tgz M_db*

# Backup rotation section
rm -rf M_db*
rm -rf /bkp/Monthly/$(date +"%B")
mkdir /bkp/Monthly/$(date +"%B")
mv mydbname* /bkp/Monthly/$(date +"%B")

# Stop timer and calculate total time
time_end=`date +%s`
total_time=`expr $(( $time_end - $time_start ))`

# Log output: datestamp and time takes to execute
echo "____________" >> /tmp/db_backup_monthly.log
date  >> /tmp/db_backup_monthly.log
echo "Execution Time was $total_time seconds." >> /tmp/db_backup_monthly.log

 innodb_backup_monthly.sh

 #!/bin/sh
# An InnoDB Backup Script to backup database DAILY
#
# Written by: Noveck Gowandan
# 02-10-2012
# Version 1.1
# Modified filename convention
# Uses tar-gzip to further compress final archive
# Added script timer and modified output to log time

# Start timer
time_start=`date +%s`

# Go to backup location and create Daily folder with datestamp
cd /bkp/
mkdir D_db_$(date +%Y%m%d)

# Execute backup using innobackupex and send to folder created previously
innobackupex --defaults-file=/etc/my.cnf --user=****** --password=****** --databases=mydbname /bkp/D_db_$(date +%Y%m%d)

# Compress backup into a tarball
tar czf mydbname_$(date +%Y%m%d).tgz D_db*

# Backup rotation section
rm -rf D_db*
rm -rf /bkp/Daily/$(date +"%A")
mkdir /bkp/Daily/$(date +"%A")
mv mydbname* /bkp/Daily/$(date +"%A")

# Stop timer and calculate total time
time_end=`date +%s`
total_time=`expr $(( $time_end - $time_start ))`

# Log output: datestamp and time takes to execute
echo "____________" >> /tmp/db_backup_daily.log
date  >> /tmp/db_backup_daily.log
echo "Execution Time was $total_time seconds." >> /tmp/db_backup_daily.log

innodb_backup_hourly.sh

#!/bin/sh
# An InnoDB Backup Script to backup database HOURLY
#
# Written by: Noveck Gowandan
# 02-10-2012
# Version 1.1
# Modified filename convention
# Added script timer and modified output to log time

# Start timer
time_start=`date +%s`

# Go to backup location and create Hourly folder with datestamp
cd /bkp/
mkdir H_db_$(date +%Y%m%d)

# Execute backup using innobackupex and send to folder created previously
innobackupex --defaults-file=/etc/my.cnf --user=****** --password=****** --databases=mydbname /bkp/H_db_$(date +%Y%m%d)

# Backup rotation section
rm -rf /bkp/Hourly/$(date +"%H")
mkdir /bkp/Hourly/$(date +"%H")
mv H_db* /bkp/Hourly/$(date +"%H")

# Stop timer and calculate total time
time_end=`date +%s`
total_time=`expr $(( $time_end - $time_start ))`

# Log output: datestamp and time takes to execute
echo "____________" >> /tmp/db_backup_hourly.log
date  >> /tmp/db_backup_hourly.log
echo "Execution Time was $total_time seconds." >> /tmp/db_backup_hourly.log

Testing Percona Xtrabackup (innobackupex)

My last post dealt with the installation of the Percona Xtrabackup tool for use on a Mysql innodb database.
After some tweaking and help from the Percona Docs, the following is a test backup, prep and restore of the full database (not incremental).

This assumes a test environment, so please don't try this on a running production environment. Before the database can be restored, the existing one will have to be renamed or moved. The options for this are straightforward. Stop the mysql daemon and make a binary copy of the files, or rename the source folder.

0. Login as root/su

1. Prerequisite configuration items

Configure datadir and in my.cnf and reboot mysqld service

nano /etc/my.cnf

 --find section

[mysqld]
--add line
datadir=/var/lib/mysql
service mysqld restart

2. Run full backup of specific database
innobackupex --defaults-file=/etc/my.cnf --user=***** --password=***** --databases=mydbname /path/to/backup/

3. Prepare backup files
innobackupex --apply-log --user=****** --password=****** /path /to/backup

4. Restore backup
service mysqld stop
cp –r /path/to/backup /var/lib/mysql

chown –R mysql:mysql /var/lib/mysql
chmod –R 771 /var/lib/mysql
service mysqld start

-n

Installing Percona xtrabackup on CentOS 5.x

This covers the basic installation of the xtrabackup package on CentOS. This is necessary in order to take hot backups of a database using the InnoDB storage engine. By default, Mysql has no options to take a full hot backup of a live InnoDB database.

For more information,  please see Percona's website located here: http://www.percona.com/doc/percona-xtrabackup/

0. Login as root

1. Get rpm from Percona
(check your server version before installing, there are specific RPM’s for 32-bit and 64-bit)
see here for rpm list: http://www.percona.com/downloads/percona-release/

cd /tmp
wget  http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm

2. Install rpm (32-bit on this test machine)
Rpm –Uvh percona-release-0.0-1.i386.rpm

3. Check repo for package
yum list | grep percona

4. Install xtrabackup
yum install xtrabackup --nogpgcheck
I still have testing to do on actual usage, so I'll create a new post on tests of backups and restoring databases.

-n