Category: Script


Incremental Backup using Innobackup

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

It’s been a while since last post, I’ve been busy and so had been away from blogging.

from past 2 months, I was working extensively with Percona Mysql and since many applications of ours are into replication environment, I had to come up with scripts considering the manual

work and time team members were putting in this.

Their were mainly two issues I was concerned about,

  • considerable time chewing up for syncing tables and to figure out the differences.
  • also a manual eye on the screen to check.

considering this, came up with simple script.

Prerequisite :

wget http://www.maatkit.org/get/mk-table-sync

wget http://www.maatkit.org/get/mk-table-checksum

Privileges:

before that create another username with password to use for syncing tables to slaves from master.

Example: User: maatkit, Password: mat007

Server A : Master

Server B: Slave1

Server C: Slave2

On both the slaves: grant all privileges to master with username maatkit as shown below,

mysql> grant all privileges on *.* TO ‘maatkit’@’master-ip’ IDENTIFIED BY ‘mat007’;

mysql> FLUSH privileges;

I have not use mk-table-checksum since I really don’t believe in checking checksum for discrepancies. had been many occurrences where  records and size are same but still checksum value is different.

In this script, I am just considering number of records though script can be modified as per requirement and also let me know, will make the modification.

Sample output of  the script given below,

1

2

Script: single DB, 2 slaves.

DB=test_db
TBL=`mysql -e “SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘$DB’;” | grep -v table_name`
USERNAME=maatkit
PASSWORD=mat007
SLAVE1=10.1.1.3
SLAVE2=10.1.1.4
THRESHCOUNT=2
SYNCMD=/root/./mk-table-sync
SLAVES=( 10.1.1.3 10.1.1.4 )
RESULT=/root/report.log
echo “<b>Fetching the Records before the sync…</b>” >> $RESULT
echo “<center>” >> $RESULT
echo “<h4><i>Discrepancies in Number of records</i></h3>” >> $RESULT
echo “<HR ALIGN=”CENTER” SIZE=”3″ WIDTH=”70%” NOSHADE>” >> $RESULT
echo “<TABLE BORDER=4 ALIGN=center CELLPADDING=10 CELLSPACING=2>” >> $RESULT
echo “<caption><b>DB Name: $DB</b></caption>” >> $RESULT
echo “<TR>” >> $RESULT
echo “<TH WIDTH=”5%”>Table</TH>” >> $RESULT
echo “<TH WIDTH=”5%”>Master</TH>” >> $RESULT
echo “<TH WIDTH=”5%”>Primary Slave: $SLAVE1</TH>” >> $RESULT
echo “<TH WIDTH=”5%”>Secondary Slave: $SLAVE2</TH>” >> $RESULT
echo “</TR>” >> $RESULT

for TABLE in $TBL;
do
MASTERREC=`mysql -e “select count(*) from $DB.$TABLE;” | awk ‘FNR == 2 {print}’`
SLAVEREC1=`mysql -h$SLAVE1 -u$USERNAME -p$PASSWORD -e “select count(*) from $DB.$TABLE;” | awk ‘FNR == 2 {print}’`
SLAVEREC2=`mysql -h$SLAVE2 -u$USERNAME -p$PASSWORD -e “select count(*) from $DB.$TABLE;” | awk ‘FNR == 2 {print}’`

THRESH1=`expr $MASTERREC – $SLAVEREC1`
THRESH2=`expr $MASTERREC – $SLAVEREC2`

if [[ “$THRESH1” -gt “$THRESHCOUNT” || “$THRESH2” -gt “$THRESHCOUNT” ]];
then
DIFFTABLE+=( $TABLE )
echo “<TR>” >> $RESULT
echo “<TD ALIGN=”center”><PRE>$TABLE</PRE></TD>” >> $RESULT
echo “<TD ALIGN=”center”><PRE>$MASTERREC</PRE></TD>” >> $RESULT
echo “<TD ALIGN=”center”><PRE>$SLAVEREC1</PRE></TD>” >> $RESULT
echo “<TD ALIGN=”center”><PRE>$SLAVEREC2</PRE></TD>” >> $RESULT
echo “</TR>” >> $RESULT
fi
done
echo “</TABLE>” >> $RESULT
echo “</center>” >> $RESULT

for SLAVE in “${SLAVES[@]}”
do
echo “<br>” >> $RESULT
echo “<center><b><FONT COLOR=RED>Slave: $SLAVE</FONT></b></center>” >> $RESULT
echo “<br>” >> $RESULT

for DIFF in “${DIFFTABLE[@]}”
do
echo “<i>Syncing records on Master to Slave, $SLAVE</i>” >> $RESULT
echo “<PRE>`$SYNCMD u=$USERNAME,p=$PASSWORD,h=localhost,D=$DB,t=$DIFF $SLAVE –execute –verbose`</PRE>” >> $RESULT
echo “<br>” >> $RESULT
echo “<i>Sync completed, After sync ..</i>” >> $RESULT
AFTSYNMASTER=`mysql -e “select count(*) from $DB.$DIFF;” | awk ‘FNR == 2 {print}’`
AFTSYNSLAVE2=`mysql -h$SLAVE -u$USERNAME -p$PASSWORD -e “select count(*) from $DB.$DIFF;” | awk ‘FNR == 2 {print}’`

THRESHHOLD=`expr $AFTSYNMASTER – $AFTSYNSLAVE2`

if [ “$THRESHHOLD” -gt “$THRESHCOUNT” ];
then
echo “<FONT COLOR=RED>Sync failed for $DB.$DIFF… still diffrence greater than $THRESHCOUNT …</FONT>” >> $RESULT
echo “<FONT COLOR=RED>IT please check ..</FONT>” >> $RESULT
else
echo “<PRE>” >> $RESULT
echo “” >> $RESULT
echo -e “\t\t\t On Master \t\t\t\t\t On Slave: $SLAVE” >> $RESULT
echo “” >> $RESULT
echo -e “\t\t\t Number of Records: $AFTSYNMASTER \t\t\t Number of Records: $AFTSYNSLAVE2” >> $RESULT
echo “<FONT COLOR=”GREEN”><b>sync completed successfully for $DIFF</b></FONT>” >> $RESULT
echo -e “\t\t\t ==========================================================================” >> $RESULT
echo “</PRE>” >> $RESULT
fi
done
done

(echo -e “From: syn-report@example.com \nTo: abc@gmail.com,xyz@example.com \nMIME-Version: 1.0 \nSubject: sync from master to slave on $HOSTNAME \nContent-Type: text/html \n”; cat $RESULT) | /usr/sbin/sendmail -t
rm -f $RESULT

Xtrabackup Script

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. 

Logic: Speed=Size/time (MB/s)

Step 1: Create a blank file of some size, say 10MB.

Step 2: Transfer this file to the server for which we want to find out speed

Step 3: Binary operation to get speed i.e divide size by time.

Step4: Repeat  steps 2 and 3, 10 times and then take average of 3 best iterations:

Script:

#!/bin/bash

file_size=10

SERVER= ### Put server name here

### Create file of “$file_size”MB

if [ ! -f /opt/speedtestfile_”$file_size”MB ]

then

dd if=/dev/zero of=/opt/speedtestfile_”$file_size”MB bs=1024k count=”$file_size”

fi

##### Test upload speed 10 times #####

for (( i=0; i<10; i++))

do

/usr/bin/time -f %e -o /opt/real.log scp /opt/speedtestfile_”$file_size”MB $SERVER:/opt/speedtestfile_”$file_size”MB

real=$(cat /opt/real.log)

upspeed=$(echo “scale=3;”$file_size”/$real” | bc)

echo $upspeed >> /opt/upload.log

done

###### To sort best 3 iterations #######

cat /opt/upload.log | sort -r | head -n 3 > /opt/upload_new.log

#### calculate average Upload speed ####

upsum=0

un=0

for u in `cat /opt/upload_new.log`

do

upsum=`echo $upsum + $u|bc`

if [ “$?” -eq “0” ]; then

un=`expr $un + 1`

fi

done

upload_average=$(echo “scale=3;$upsum/$un” | bc)

###### find download speed 10 times #####

echo “Download speed test for Server_name”

for (( i=0; i<10; i++))

do

/usr/bin/time -f %e -o /opt/dreal.log scp $SERVER:/opt/speedtestfile_”$file_size”MB /opt/speedtestfile_”$file_size”MB

dreal=$(cat /opt/dreal.log)

downspeed=$(echo “scale=3;”$file_size”/$dreal” | bc)

echo $downspeed >> /opt/download.log

done

### To sort best 3 iterations #####

cat /opt/download.log |  sort -r | head -n 3 > /opt/download_new.log

#### To calculate average of 3 iterations ####

downsum=0

dn=0

for d in `cat /opt/download_new.log`

do

downsum=`echo $downsum + $d|bc`

if [ “$?” -eq “0” ]; then

dn=`expr $dn + 1`

fi

done

download_average=$(echo “scale=3;$downsum/$dn” | bc)

echo -e “\nServer upload speed: $upload_average”

echo -e “\nServer Download Speed: $download_average”

What does 2>&1 means ?

I found this line at the end of each script in crontab. I was amazed to find what’s the importance and use of 2>&1.

If you are familiar with UNIX I/O redirection, syntax similar to the following should not be new to you:

command > file 2>&1

Briefly, when command runs it sends “normal” output to file, and any error messages generated by command are also written to file. “2>&1” handles the latter.

Have you ever wondered where the numbers 2 and 1 come from?

When a UNIX program wants to use a file, it must first open that file. When it does so, UNIX will associate a number with the file. This number, which is used by the program when reading from and writing to the file, is the file descriptor.

A typical UNIX program will open three files when it starts. These files are:

– standard input (also known as stdin)
– standard output (also known as stdout)
– standard error (also known as stderr)

Standard input has a file descriptor of 0, standard output uses 1, and the number 2 is used by standard error. Are you starting to see where this is headed?

Looking at our command again,

command > file 2>&1

you should now recognize that 2>&1 instructs the shell to send messages headed to stderr (2) to the same place messages to stdout (1) are sent. In our example, that place is file.

“Lazy” suits more to a Linux System Engineer. 🙂 …..

Introducing scripting with a touch of HTML formatting.

The below script will grep all DB’s and will,

  • Check for the corrupted tables
  • Repair it
  • Optimize it
  • If unable to do so then will email with a DB name, Table name and an Engine information.

Script

for db in $(mysql -e “SHOW DATABASES;” | grep -ve Database -ve information)
do

ISAM=$(mysql -e “SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = ‘”$db”‘;”| grep -v TABLE_)
for isam in $ISAM
do
mysqlcheck $db $isam > /root/check.txt
tbls=$(cat /root/check.txt | grep -ie Corrupt -ie dump\/reload)
if [ ! -z “$tbls” ];
then
echo “<h3>Database: $db</h3>” >> /root/result.log
echo “<i>Table Name: $isam<i>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b><font color =”GREEN”>Repairing the table</font></b>” >> /root/result.log
echo “USE $db; REPAIR TABLE $isam” | mysql -H > /root/repair.txt
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<PRE>`cat /root/repair.txt`</PRE>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b><font color =”GREEN”>Optimizing the table</font></b>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “USE $db; OPTIMIZE TABLE $isam” | mysql -H > /root/optimize.txt
echo “<PRE>`cat /root/optimize.txt`</PRE>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b>done</b>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b>Checks after REPAIR AND OPTIMIZE</b>” >> /root/result.log
echo “<br>” >> /root/result.log
mysqlcheck $db $isam > /root/check.txt

tbls=$(grep -i Corrupt /root/check.txt)

if [ ! -z “$tbls” ];
then
echo “<br>” >> /root/result.log
echo “<b><font color =”RED”>Can’t be Repaired or Optimized</font></b>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<b>Table Engine Information</b>” >> /root/result.log
echo “<br>” >> /root/result.log
echo “<br>” >> /root/result.log
mysql -H -e “SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = ‘”$db”‘ AND TABLE_NAME = ‘”$isam”‘;” > /root/engine.txt
echo “<PRE>`cat /root/engine.txt`</PRE>” >> /root/result.log
echo “<HR ALIGN=”LEFT” SIZE=”3″ WIDTH=”70%” NOSHADE>” >> /root/result.log
else
echo “<br>” >> /root/result.log
echo “<b><font color =”GREEN”>Status OK for $isam</font></b>” >> /root/result.log
echo “<HR ALIGN=”LEFT” SIZE=”3″ WIDTH=”70%” NOSHADE>” >> /root/result.log
fi

fi

done
done
cat result.log | mail -s “$(echo -e “118 Status check for MYISAM Tables\nContent-Type: text/html”)” your-email-address@ — -f MYISAM@google.com
rm -f /root/result.log

Output:

MYISAM check output