This is not new, their are many scripts available in Google but they lack many things while taking Incremental backups using Innobackup. at-least I couldn’t find one. :).

If you guys have then yes have reinvented the wheel 😉

what’s missing?

  1. Do not apply logs
  2. LSN checks
  3. not enough conditions to check

Please go through this link to read about Incremental backups using Innobackup. I won’t be going to much detail.

http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/incremental_backups_innobackupex.html

http://www.percona.com/doc/percona-xtrabackup/2.1/howtos/recipes_ibkx_inc.html

http://www.percona.com/doc/percona-xtrabackup/2.1/xtrabackup_bin/incremental_backups.html?id=percona-xtrabackup:xtrabackup:incremental

Script from Percona: https://gist.github.com/jmfederico/1495347

The reason I wrote this script because no one wants to untar the backup, apply logs and then restore during any disaster.

What my script does?

Have divided into following modules
 
1. Prerequisite
  • Check for directory structure, create if doesn’t exists.
  • check whether mysql is running or not, if not then exit.
2. Full Backup
  • Check whether Full backup done, if not then create
  • check for the result, If fail then exit
  • Apply logs
  • check for the result, if fail then exit.
  • output Xtrabackup Checkpoints
3. Incremental Backup
  • Check whether Incremental 1 to be backed-up or Incremental 2
  • Proceed accordingly
  • check for the result, fail then exit
4. LSN checks

  • Before Applying logs, it LSN will check the checkpoints with previous backups
  • If not matched then email with the checkpoints
  • If matched then Apply logs
5. Apply logs

  • Applying logs will proceed after the backup is successfully created, LSN numbers are matching
  • If apply logs fail then exit 
6. creating tar

  • After all the checks and applying logs, will have full-backup ready
  • create a tar file
  • remove the folders
7. Delete old backups (AGE = 6, 6 days backup will be retain)
8. LSN summary report

Note: both the scripts should reside under /root/

Main Script:

#!/bin/sh
rm -f /tmp/innobackupex-runner*
rm -f /root/lsn.log /root/inno.log
TMPFILE=”/tmp/innobackupex-runner.$$.tmp”
BASEBACKDIR=/Backup/Full
INCRBACKDIR=/Backup/Incremental
START=`date +%s`
INNOBACKUPEX=/usr/bin/innobackupex
AGE=6
FILE=/Backup/verdict.txt

##Check base dir exists and create if it doesn’t

if [ ! -d “$BASEBACKDIR” ];
then
echo “<h3>$BASEBACKDIR doesn’t exists, creating it</h3>” >> /root/inno.log
mkdir –parents $BASEBACKDIR
fi

##check Incremental dir exists and create if it doesn’t

if [ ! -d “$INCRBACKDIR” ];
then
echo “<h3>$INCRBACKDIR doesn’t exists, creating it</h3>” >> /root/inno.log
mkdir $INCRBACKDIR
fi

if [ ! -f $FILE ];
then
touch $FILE
fi

##Check if Mysql is running

if [ -z “`mysqladmin status | grep ‘Uptime’`” ]
then
echo “<h3>HALTED: MySQL does not appear to be running.</h3>”; echo “<br>” >> /root/inno.log
(echo -e “From: Innobackup-Backup@exateam.com \nTo: abc@gmail.com \nMIME-Version: 1.0 \nSubject: Xtradb backup check report for CRM DB on $HOSTNAME \nContent-Type: text/html \n”; cat /root/inno.log) | /usr/sbin/sendmail -t
rm -f /root/inno.log
exit 1
fi

##Find latest backup directory and if doesn’t exists then create a NEW FULL BACKUP

LATEST=`find $BASEBACKDIR -mindepth 1 -maxdepth 1 -type d -printf “%P\n” | sort -nr | head -1`

if [ -z “$LATEST” ];
then
echo “<h3>Creating New Full Backup</h3>” >> /root/inno.log
$INNOBACKUPEX $BASEBACKDIR > $TMPFILE 2>&1

if [ -z “`tail -1 $TMPFILE | grep ‘completed OK!’`” ] ; then
echo “<h3>$INNOBACKUPEX failed:</h3>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<PRE>———- ERROR OUTPUT from $INNOBACKUPEX ———-</PRE>” >> /root/inno.log
rm -f $TMPFILE
(echo -e “From: Innobackup-Backup@exateam.com \nTo: abc@gmail.com \nMIME-Version: 1.0 \nSubject: Xtradb backup check report for CRM DB \nContent-Type: text/html \n”; cat /root/inno.log) | /usr/sbin/sendmail -t
rm -f /root/inno.log
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/inno.log
echo “<br> ” >> /root/inno.log
echo “Xtrabackup Checkpoints” >> /root/inno.log
echo “<br> ” >> /root/inno.log
echo “<PRE>`cat $THISBACKUP/xtrabackup_checkpoints`</PRE>” >> /root/inno.log
echo “<br> ” >> /root/inno.log
echo “Now applying logs to the backuped databases” >> /root/inno.log
$INNOBACKUPEX –apply-log –redo-only $THISBACKUP > $TMPFILE 2>&1

if [ -z “`tail -1 $TMPFILE | grep ‘completed OK!’`” ] ; then
echo “<h3>$INNOBACKUPEX failed:</h3>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<b>Applied Logs Failed</b>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<PRE>———- ERROR OUTPUT from $INNOBACKUPEX ———-</PRE>” >> /root/inno.log
(echo -e “From: Innobackup-Backup@exateam.com \nTo: abc@gmail.com \nMIME-Version: 1.0 \nSubject: Xtradb backup check report for CRM DB \nContent-Type: text/html \n”; cat /root/inno.log) | /usr/sbin/sendmail -t
echo “1” >> $FILE
rm -f /root/inno.log
rm -f $TMPFILE
exit 1
fi

echo “Logs applied to backuped databases successfully” >> /root/inno.log
(echo -e “From: Innobackup-Backup@exateam.com \nTo: abc@gmail.com \nMIME-Version: 1.0 \nSubject: Xtradb backup check report for CRM DB \nContent-Type: text/html \n”; cat /root/inno.log) | /usr/sbin/sendmail -t
echo “Full Backup: Done” >> $FILE
rm -f /root/inno.log
exit 1

fi

##Incremental Backups

LATESTINCR=`find $INCRBACKDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`

if [ ! $LATESTINCR ]
then
echo “<h3>First Incremental Backup</h3>” >> /root/inno.log
BCKTYPE=INCR1
INCRBASEDIR=$BASEBACKDIR/$LATEST
INCRDIR=$INCRBACKDIR/Incr1
OPTIONS=”–apply-log –redo-only”
else
echo “<h3>Second Incremental Backup</h3>” >> /root/inno.log
BCKTYPE=INCR2
INCRBASEDIR=$LATESTINCR
INCRDIR=$INCRBACKDIR/Incr2
OPTIONS=”–apply-log”
fi

$INNOBACKUPEX –no-timestamp –incremental $INCRDIR –incremental-basedir=$INCRBASEDIR > $TMPFILE 2>&1

if [ -z “`tail -1 $TMPFILE | grep ‘completed OK!’`” ] ; then
echo “<b>$INNOBACKUPEX failed:</b>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<PRE>———- ERROR OUTPUT from $INNOBACKUPEX ———-</PRE>” >> /root/inno.log
(echo -e “From: Innobackup-Backup@exateam.com \nTo: abc@gmail.com \nMIME-Version: 1.0 \nSubject: Xtradb backup check report for CRM DB \nContent-Type: text/html \n”; cat /root/inno.log) | /usr/sbin/sendmail -t
rm -f /root/inno.log
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/inno.log
echo “<br>” >> /root/inno.log
echo “Xtrabackup Chckpoints Summary” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<PRE>`cat $INCRDIR/xtrabackup_checkpoints`</PRE>” >> /root/inno.log
echo “<br>” >> /root/inno.log

##LSN checks before applying logs

fullpath=$(find $BASEBACKDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1)
echo “<b>Log Sequence Number Checking:</b>” >> /root/inno.log
echo “<br>” >> /root/inno.log

if [ “$BCKTYPE” = INCR1 ];
then
full_to_lsn=`cat $fullpath/xtrabackup_checkpoints | grep -i “to_lsn” | awk ‘{print $3}’`
incr1_from_lsn=`cat $THISBACKUP/xtrabackup_checkpoints | grep -i “from_lsn” | awk ‘{print $3}’`

if [ “$full_to_lsn” -ne “$incr1_from_lsn” ];
then
echo “Can’t Apply Logs, LSN not matching with Incremental Backup1” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<b>Xtrabackup Checkpoints for Full Backup</b>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<PRE>`cat $fullpath/xtrabackup_checkpoints`</PRE>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<b>Xtrabackup Checkpoints for Incremental Backup 1</b>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<PRE>`cat $INCRDIR/xtrabackup_checkpoints`</PRE>” >> /root/inno.log
(echo -e “From: Innobackup-Backup@exateam.com \nTo: abc@gmail.com \nMIME-Version: 1.0 \nSubject: Xtradb backup check report for CRM DB \nContent-Type: text/html \n”; cat /root/inno.log) | /usr/sbin/sendmail -t
rm -f /root/inno.log
rm -f $TMPFILE
exit 1
fi
else
incr1_to_lsn=`cat $LATESTINCR/xtrabackup_checkpoints | grep -i “to_lsn” | awk ‘{print $3}’`
incr2_from_lsn=`cat $THISBACKUP/xtrabackup_checkpoints | grep -i “from_lsn” | awk ‘{print $3}’`

if [ “$incr1_to_lsn” -ne “$incr2_from_lsn” ];
then
echo “Can’t Apply Logs, LSN not matching with Incremental Backup2” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<b>Xtrabackup Checkpoints for Incremental Backup 1</b>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<PRE>`cat $THISBACKUP/xtrabackup_checkpoints`</PRE>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<b>Xtrabackup Checkpoints for Incremental Backup 2</b>” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<PRE>`cat $INCRDIR/xtrabackup_checkpoints`</PRE>” >> /root/inno.log
(echo -e “From: Innobackup-Backup@exateam.com \nTo: abc@gmail.com \nMIME-Version: 1.0 \nSubject: Xtradb backup check report for CRM DB \nContent-Type: text/html \n”; cat /root/inno.log) | /usr/sbin/sendmail -t
rm -f /root/inno.log
rm -f $TMPFILE
exit 1
fi
fi

echo “Log Sequence Number Matches with previous backup” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “Now applying logs to the backuped databases of $INCRDIR” >> /root/inno.log
echo “<br>” >> /root/inno.log
$INNOBACKUPEX $OPTIONS $BASEBACKDIR/$LATEST –incremental-dir=$INCRDIR > $TMPFILE 2>&1

if [ -z “`tail -1 $TMPFILE | grep ‘completed OK!’`” ] ; then
echo “$INNOBACKUPEX failed:” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “———- ERROR OUTPUT from $INNOBACKUPEX ———-” >> /root/inno.log
(echo -e “From: Innobackup-Backup@exateam.com \nTo: abc@gmail.com \nMIME-Version: 1.0 \nSubject: Xtradb backup check report for CRM DB \nContent-Type: text/html \n”; cat /root/inno.log) | /usr/sbin/sendmail -t
echo “1” >> $FILE
rm -f /root/inno.log
rm -f $TMPFILE
exit 1
fi

echo “Logs applied to backuped databases successfully” >> /root/inno.log
echo “$BCKTYPE: Done” >> $FILE

##Creating TAR

COUNT=$(grep -i ‘done’ $FILE | wc -l)

if [ “$COUNT” = 3 ];
then
LATEST=`find $BASEBACKDIR -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`
echo “<br>” >> /root/inno.log
echo “Compressing Full backup files” >> /root/inno.log
tar -czvf /Backup/backup_Mysql_CRMDB_`date +%d-%m-%Y-%H`.tar.gz $LATEST
echo “<br>” >> /root/inno.log
echo “completed: `date`” >> /root/inno.log
echo “<br>” >> /root/inno.log
echo “<h4><i>Checkpoints Summary</i></h4>” >> /root/inno.log
sh /root/lsn.sh
cat /root/lsn.log >> /root/inno.log
rm -rf $BASEBACKDIR/* $INCRBACKDIR/* $FILE

fi

 ##Clean-up

echo “<br>” >> /root/inno.log

echo “Cleaning up old backups (older than $AGE days) and temporary files” >> /root/inno.log
rm -rf $TMPFILE
cd /tmp ; find /Backup -maxdepth 1 -ctime +$AGE -exec echo “removing: “{} \; -exec rm -rf {} \;
(echo -e “From: Innobackup-Backup@exateam.com \nTo: abc@gmail.com \nMIME-Version: 1.0 \nSubject: Xtradb backup check report for CRM DB \nContent-Type: text/html \n”; cat /root/inno.log) | /usr/sbin/sendmail -t
rm -f /root/inno.log

##Main Script Ends here##

LSN output script:

echo “<center>” >> /root/lsn.log
echo “<h3><i>Innobackup/Xtrabackup Backup Report</i></h3>” >> /root/lsn.log
echo “</center>” >> /root/lsn.log
echo “<HR ALIGN=”CENTER” SIZE=”3″ WIDTH=”70%” NOSHADE>” >> /root/lsn.log
echo “<TABLE BORDER=4 ALIGN=center CELLPADDING=10 CELLSPACING=2>” >> /root/lsn.log
echo “<TR>” >> /root/lsn.log
echo “<TH WIDTH=”5%”>Backup Type</TH>” >> /root/lsn.log
echo “<TH WIDTH=”5%”>From LSN</TH>” >> /root/lsn.log
echo “<TH WIDTH=”5%”>To LSN</TH>” >> /root/lsn.log
echo “<TH WIDTH=”5%”>Last LSN</TH>” >> /root/lsn.log
echo “</TR>” >> /root/lsn.log

full=`find /Backup/Full -mindepth 1 -maxdepth 1 -type d | sort -nr | head -1`

full_backup_type=$(cat $full/xtrabackup_checkpoints | grep -i “backup_type” | awk ‘{print $3}’)
full_from_lsn=$(cat $full/xtrabackup_checkpoints | grep -i “from_lsn” | awk ‘{print $3}’)
full_to_lsn=$(cat $full/xtrabackup_checkpoints | grep -i “to_lsn” | awk ‘{print $3}’)
full_last_lsn=$(cat $full/xtrabackup_checkpoints | grep -i “last_lsn” | awk ‘{print $3}’)

incr1_backup_type=$(cat /Backup/Incremental/Incr1/xtrabackup_checkpoints | grep -i “backup_type” | awk ‘{print $3}’)
incr1_from_lsn=$(cat /Backup/Incremental/Incr1/xtrabackup_checkpoints | grep -i “from_lsn” | awk ‘{print $3}’)
incr1_to_lsn=$(cat /Backup/Incremental/Incr1/xtrabackup_checkpoints | grep -i “to_lsn” | awk ‘{print $3}’)
incr1_last_lsn=$(cat /Backup/Incremental/Incr1/xtrabackup_checkpoints | grep -i “last_lsn” | awk ‘{print $3}’)

incr2_backup_type=$(cat /Backup/Incremental/Incr2/xtrabackup_checkpoints | grep -i “backup_type” | awk ‘{print $3}’)
incr2_from_lsn=$(cat /Backup/Incremental/Incr2/xtrabackup_checkpoints | grep -i “from_lsn” | awk ‘{print $3}’)
incr2_to_lsn=$(cat /Backup/Incremental/Incr2/xtrabackup_checkpoints | grep -i “to_lsn” | awk ‘{print $3}’)
incr2_last_lsn=$(cat /Backup/Incremental/Incr2/xtrabackup_checkpoints | grep -i “last_lsn” | awk ‘{print $3}’)

if [[ “$full_to_lsn” -ne “$incr1_from_lsn” && “$incr1_to_lsn” -ne “$incr2_from_lsn” ]];
then
echo “<TR>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE><b>$full_backup_type</b></PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$full_from_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=RED><PRE>$full_to_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$full_last_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “</TR>” >> /root/lsn.log
echo “<TR>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE><b>$incr1_backup_type</b></PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=RED><PRE>$incr1_from_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=RED><PRE>$incr1_to_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$incr1_last_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “</TR>” >> /root/lsn.log
echo “<TR>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE><b>$incr2_backup_type</b></PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=RED><PRE>$incr2_from_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$incr2_to_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$incr2_last_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “</TR>” >> /root/lsn.log
else
echo “<TR>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE><b>$full_backup_type</b></PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$full_from_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$full_to_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$full_last_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “</TR>” >> /root/lsn.log
echo “<TR>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE><b>$incr1_backup_type</b></PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$incr1_from_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$incr1_to_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$incr1_last_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “</TR>” >> /root/lsn.log
echo “<TR>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE><b>$incr2_backup_type</b></PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$incr2_from_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$incr2_to_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “<TD ALIGN=”center”><FONT COLOR=GREEN><PRE>$incr2_last_lsn</PRE></FONT></TD>” >> /root/lsn.log
echo “</TR>” >> /root/lsn.log

fi

Advertisements