Percona XtraBackup is an open-source hot backup utility for MySQL – based servers that doesn’t lock your database during the backup.

It can back up data from InnoDBXtraDB, and MyISAM tables on unmodified MySQL 5.0, 5.1 and 5.5 servers, as well asPercona Server with XtraDB.

Installation on Cent OS 5.x, 6.x series/ Red Hat Linux 5.x and 6.x series.

Step 1. uname -i

For 64 bit OS,

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

For 32 bit OS,

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm

step 2. vi /etc/my.cnf, add below parameter

datadir=/var/lib/mysql (if not added already, MySql restart is not required)

step 3. root@bt [~]# yum -y install xtrabackup

step 4. Make sure Innodb is set to Yes

root@bt [~]#mysqladmin variables | grep have_innodb
| have_innodb | YES

if it is NO then vi /etc/my.cnf and remove “skip-innodb” from the file and restart mysql service and if still not able to see it YES.

Go to /var/lib/mysql/ and delete ibdata1 and its log files ib_logfile0 and ib_logfile1 and re ran mysql service.

Check ownership for iddata1 file at /var/lib/mysql as it should be mysql:mysql and if it is different then change it by below command

root@bt[~]#chown mysql:mysql /var/lib/mysql/ibdata1

Step 5. Copy and Paste below script,

Script STARTS here

rm -f /Backup/mount.txt
rm -f /Backup/errors.txt
rm -f /root/mysql119bck.log
BACKUPDIR=/Backup

if [ ! -d “$BACKUPDIR” ];
then
mkdir /Backup
fi

INNOBACKUPEX=innobackupex-1.5.1
TMPFILE=”/tmp/innobackupex-runner.$$.tmp”
AGE=4

/usr/bin/innobackupex-1.5.1 /Backup/ > $TMPFILE 2>&1

if [ -z “`tail -1 $TMPFILE | grep ‘completed OK!’`” ] ; then
echo “$INNOBACKUPEX failed:” >> /root/mysql119bck.log
echo “” >> /root/mysql119bck.log
echo “———- ERROR OUTPUT from $INNOBACKUPEX ———-” >> /root/mysql119bck.log
cat /root/mysql119bck.log | mail -s “$HOSTNAME Backup Report” abc@domain.com — -f mysql@domain.com

cat $TMPFILE
rm -f $TMPFILE
exit 1
fi

THISBACKUP=`cat $TMPFILE | grep ‘Backup created in directory’ | awk ‘{ print $6 }’ | sed “s/’//g”`

rm -f $TMPFILE

echo “Databases backed up successfully to: $THISBACKUP” >> /root/mysql119bck.log
echo ” ” >> /root/mysql119bck.log
echo “Now applying logs to the backuped databases” >> /root/mysql119bck.log

/usr/bin/innobackupex-1.5.1 –use-memory=2G –apply-log $THISBACKUP > $TMPFILE 2>&1

if [ -z “`tail -1 $TMPFILE | grep ‘completed OK!’`” ] ; then
echo “$INNOBACKUPEX –apply-log failed:” >> /root/mysql119bck.log
echo ” ” >> /root/mysql119bck.log
echo “———- ERROR OUTPUT from $INNOBACKUPEX –apply-log ———-” >> /root/mysql119bck.log
echo ” ” >> /root/mysql119bck.log
cat $TMPFILE >> /root/mysql119bck.log
cat /root/mysql119bck.log | mail -s “$HOSTNAME Backup Report” abc@domain.com — -f mysql@domain.com

rm -f $TMPFILE
exit 1
fi

echo “Logs applied to backuped databases” >> /root/mysql119bck.log
echo

#Compress backup

echo “Compressing backup files” >> /root/mysql119bck.log


tar -czvf /Backup/backup_Mysql_119_`date +%d-%m-%Y-%H`.tar.gz $THISBACKUP
rm -rf $THISBACKUP
# Cleanup

echo “Cleaning up old backups (older than $AGE days) and temporary files” >> /root/mysql119bck.log
rm -rf $TMPFILE
cd /tmp ; find $BACKUPDIR -maxdepth 1 -ctime +$AGE -exec echo “removing: “{} \; -exec rm -rf {} \;

echo >> /root/mysql119bck.log
echo “completed: `date`” >> /root/mysql119bck.log

#Moving to Remote Location (Assuming, Remote dir is mounted on the server)

ls -lh /mysqlM3hrs > $BACKUPDIR/mount.txt

if [ ! -s $BACKUPDIR/mount.txt ];
then
echo “Backup not mounted… Can’t move backup to Remote server” >> /root/mysql119bck.log
else
file=`ls /Backup/*.tar.gz | cut -d “/” -f3`
mv -f /Backup/$file /mysqlM3hrs/ 2> /Backup/errors.txt

if [ -s /Backup/errors.txt ];
then
cat /Backup/errors.txt >> /root/mysql119bck.log
echo “Copy failed on Remote server … Please check” >> /root/mysql119bck.log
else
if [ ! -f `ls /mysqlM3hrs/$file` ]; then
echo “Copy failed on Remote server … Please check” >> /root/mysql119bck.log
else
echo “Copied Successful to Remote server” >> /root/mysql119bck.log
echo >> /root/mysql119bck.log
echo `ls -lh /mysqlM3hrs/$file` >> /root/mysql119bck.log
echo >> /root/mysql119bck.log
fi
fi
fi
cat /root/mysql119bck.log | mail -s “$HOSTNAME Backup Report”  abc@domain.com — -f  mysql@domain.com

rm -f /root/mysql119bck.log
exit 0

Script ENDS here

what does the script do?

1. well, first it will check for all the prerequisite.

2. Starts with the backup and checks for “completed OK!” in the tmp file.

3. If failed then email and exit, if all good then,

4. It will use 2G of RAM and will apply logs on the backup.

5. It will check for “Completed OK!” in the tmp file

6. If all good then proceed to next step or else email and exit.

7. will compress the backup and will clean up the backup older than 4 days in the Backup DIR.

8. Will move the backup to the remote server. Also will check whether the mount point exists, if not then email that copy to remote location failed.

Restoration from the Backup taken from above script.

step 1. tar -xzvf backup.tar.gz

step2 . service mysql stop

step 3. mv /var/lib/mysql /root/ (Just in case :))

step 4. rm -rf /var/lib/mysql/*

step 5. innobackupex-1.5.1 –copy-back /root/untared_backup (From Step 1.)

step 6: chown -R mysql:mysql /var/lib/mysql

step 7. service mysql start

why do we apply logs?

To prepare the backup use the –apply-log option and specify the timestamped subdirectory of the backup. To speed up the apply-log process, we using the –use-memory option is recommended.

In short, we are Making a Local Full Backup (Create, Prepare and Restore)

PS: Tested and trust me on this, have saved our asses big time.