Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Friday, 24 May 2019

MySQL Incremental Backup Script

How to take incremental backup in MySQL ?

::#Point-in-Time Backup and Recovery in MySQL

Using large production databases must required an Incremental backup to ensure Safe and Reliable database system. In this article, I'm writing a shell script for automatic MySQL FULL&Incremental Backup which can be schedule by cron job for automation of their result.
This Script is written in very simple way and logic which you can easily understand.

Features....

  • Automation of Daily Full Backup using Percona XtraBackup
  • No production loss during the backup 
  • Creating a mysql-binlog check-points in every backup so that you can easily use the same for point-in-time recovery or setup new replication server.
  • Create Backup Directory if not exist.
  • No repetition of same day backup.
  • Creating a Log File for whole process.
  • Sending Email Alerts for any failure/success backup  
  • Easy-to-Understand 

Limitation...

  • You must have to installed Percona XtraBackup 2.4
  • Must have to configured Mail smtp function (mailx) for sending email alerts
  • Occupy the disk storage if data size is very large

#!/bin/bash
#########################################
# By-Alok Kumar Singh@www.getmsql.info###
# Features-                           ###
# FULL , Incremental, Restore ,Daily/ ###
# Weekly Backup Rotation, Mail Alert  ###
# on Successful/Failure               ###
######################################### 


SECRET="--user=MySQLusername --password=MySQLpassword"

## Parameters

BACKUP_DIR_ROOT=/backup/xtrabackup
BACKUP_DIR_Daily=$BACKUP_DIR_ROOT/Daily
DATA_DIR=/var/lib/mysql
HOSTIP="192.168.1.1"
SERVERNAME="Production-Slave-DB"
TAR_COMPRESS="1"  ## 0 - for disable

emails="info.getmysql@gmail.com" ## Add Multiple emails with space

usage() { 
echo "usage: $(basename $0) [option]" 
echo "option=full: Perform Full Backup"
echo "option=incremental: Perform Incremental Backup"
echo "option=restore: Start to Restore! Be Careful!! "
echo "option=help: show this help"
}

fullbackup() {
local TMPFILE=/tmp/XtraBackup$$.tmp
if [ ! -d $BACKUP_DIR_ROOT ]
then
mkdir $BACKUP_DIR_ROOT
fi
if [ ! -d $BACKUP_DIR_Daily ]
then
mkdir $BACKUP_DIR_Daily
fi

rm -rf $BACKUP_DIR_Daily/*

echo "$(date +%d%m%y" ""%T.%3N") :: Cleanup the backup folder is done! Starting backup" >> $BACKUP_DIR_Daily/xtrabackup_full.log

#xtrabackup --backup $SECRET -u root -p --history --compress --slave-info --compress-threads=4 --target-dir=$BACKUP_DIR_Daily/FULL

xtrabackup --backup $SECRET --history  --target-dir=$BACKUP_DIR_Daily/FULL > $TMPFILE 2>&1
#xtrabackup --backup --user=root --password=Mysqlroot@123 --history --parallel=8 --compress --compress-threads=8 --target-dir=/tmp/FULL /tmp/log 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_FULL failed" >> $BACKUP_DIR_Daily/xtrabackup_full.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_full.log
#echo -e $ERRMSG | mail -r "root@alert.com" -s "XtraBackup failed" $emails
mail -r "root@alert.com" -s "ERROR:XtraBackup_FULL failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
rm -rf $BACKUP_DIR_Daily/FULL
exit 1
fi
rm -rf $TMPFILE
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_FULL Done!" >> $BACKUP_DIR_Daily/xtrabackup_full.log
echo -e "XtraBackup_FULL Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_FULL Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 
}

incremental_backup(){
local  TMPFILE=/tmp/XtraBackup_inc$$.tmp
if [ ! -d $BACKUP_DIR_Daily/FULL ]
then
echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:Unable to find the FULL Backup. aborting.....!" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
exit -1
fi

if [ ! -f $BACKUP_DIR_Daily/last_incremental_number ]; then
NUMBER=1
else
NUMBER=$(($(cat $BACKUP_DIR_Daily/last_incremental_number) + 1))
fi
echo "$(date +%d%m%y" ""%T.%3N") :: Starting Incremental backup inc$NUMBER" >> $BACKUP_DIR_Daily/xtrabackup_inc.log

if [ $NUMBER -eq 1 ]
then
xtrabackup --backup $SECRET --history  --incremental --target-dir=$BACKUP_DIR_Daily/inc$NUMBER --incremental-basedir=$BACKUP_DIR_Daily/FULL > $TMPFILE 2>&1
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_inc$NUMBER failed" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_inc$NUMBER failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
rm -rf $BACKUP_DIR_Daily/inc$NUMBER
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_inc$NUMBER Done!" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo -e "XtraBackup_inc$NUMBER Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_inc$NUMBER Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE

else
xtrabackup --backup $SECRET --history --incremental --target-dir=$BACKUP_DIR_Daily/inc$NUMBER --incremental-basedir=$BACKUP_DIR_Daily/inc$(($NUMBER - 1)) > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_inc$NUMBER failed" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_inc$NUMBER failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
rm -rf $BACKUP_DIR_Daily/inc$NUMBER
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_inc$NUMBER Done!" >> $BACKUP_DIR_Daily/xtrabackup_inc.log
echo -e "XtraBackup_inc$NUMBER Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_inc$NUMBER Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE

fi

echo $NUMBER > $BACKUP_DIR_Daily/last_incremental_number

}

restore()
{
local  TMPFILE=/tmp/XtraBackup_restore$$.tmp
timestamp=$(date +%Y%m%d_%H%M%S)
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing the FULL backup" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
# xtrabackup $SECRET --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR_Daily/FULL 
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing Done !!!" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Prepareing FULL Backup ..." >> $BACKUP_DIR_Daily/xtrabackup_restore.log

xtrabackup $SECRET --prepare  --apply-log-only --target-dir=$BACKUP_DIR_Daily/FULL > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_FULL_Prepare failed" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_FULL_Prepare failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_FULL_Preparation Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo -e "XtraBackup_FULL_Preparation Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_FULL_Preparation Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE        
if [ ! -f $BACKUP_DIR_Daily/last_prepare_number ]; then
P=1
else
P=$(cat $BACKUP_DIR_Daily/last_prepare_number)
fi
#P=1
while [ -d $BACKUP_DIR_Daily/inc$P ] && [ -d $BACKUP_DIR_Daily/inc$(($P+1)) ]
do
  # echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing incremental:$P" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
  # xtrabackup $SECRET --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR_Daily/inc$P 
  # echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing incremental:$P Done !!!" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
  echo "$(date +%d%m%y" ""%T.%3N") :: Prepareing incremental backup inc$P" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
  xtrabackup $SECRET --prepare --apply-log-only --target-dir=$BACKUP_DIR_Daily/FULL --incremental-dir=$BACKUP_DIR_Daily/inc$P > $TMPFILE 2>&1
  
if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_inc$P Prepare failed" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_inc$P Prepare failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_inc$P Preparation Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo -e "XtraBackup_inc$P preparation Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_inc$P Preparation Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE
P=$(($P+1))
echo $P > $BACKUP_DIR_Daily/last_prepare_number
done

if [ -d $BACKUP_DIR_Daily/inc$P ]
then
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing the last incremental:$P" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
# xtrabackup $SECRET --decompress --remove-original --parallel=4 --target-dir=$BACKUP_DIR_Daily/inc$P 
# echo `date '+%Y-%m-%d %H:%M:%S:%s'`": Decompressing the last incremental:$P Done !!!" >> $BACKUP_DIR_Daily/xtrabackup-restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Prepareing last incremental backup inc$P" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
xtrabackup $SECRET --prepare --target-dir=$BACKUP_DIR_Daily/FULL --incremental-dir=$BACKUP_DIR_Daily/inc$P > $TMPFILE 2>&1

if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then

echo "$(date +%d%m%y" ""%T.%3N") :: ERROR:XtraBackup_last_inc$P Prepare failed" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: Sending Mail Alert to $emails for Backup failed in execution time" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
mail -r "root@alert.com" -s "ERROR:XtraBackup_last_inc$P Prepare failed on $SERVERNAME@$HOSTIP at $(date)" $emails < $TMPFILE
rm -rf $TMPFILE
exit 1
else
echo "$(date +%d%m%y" ""%T.%3N") :: XtraBackup_last_inc$P Preparation Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo -e "XtraBackup_last_inc$P Preparation Successful! at $(date)" | mail -r "root@alert.com" -s "OK:XtraBackup_last_inc$P Preparation Successful! on $SERVERNAME@$HOSTIP at $(date)" $emails 

fi
rm -rf $TMPFILE

echo $P > $BACKUP_DIR_Daily/last_prepare_number 

fi
if [ ! -d $BACKUP_DIR_ROOT/Weekly ]
then
mkdir $BACKUP_DIR_ROOT/Weekly
fi
destdir="$BACKUP_DIR_ROOT/Weekly/FULLBACKUP_till_$timestamp"
mv $BACKUP_DIR_Daily $destdir

#### Compress........

if [ "$TAR_COMPRESS" == "1" ]; then
echo "$(date +%d%m%y" ""%T.%3N") :: $destdir compression Enabled!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
echo "$(date +%d%m%y" ""%T.%3N") :: $destdir compression Starting!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
#cd $BACKUP_DIR/$NOWDAY && sudo tar --create --gzip --file=$db.sql.tgz $db.sql --remove-files 
tar --create --gzip --file=$destdir.tgz $destdir --remove-files 
echo "$(date +%d%m%y" ""%T.%3N") :: $destdir compression Done!" >> $BACKUP_DIR_Daily/xtrabackup_restore.log
fi

}

if [ $# -eq 0 ]
then
usage
exit 1
fi

case $1 in
"full")
fullbackup
;;
"incremental")
incremental_backup
;;
"restore")
restore
;;
"help")
usage

;;
*) echo "invalid option";;
esac
    

##########################################################
### Copyright@ Alok Kumar Singh with www.getmysql.info ###
##########################################################
How to use the Script ?

  • Save the script as xtrabackup.sh (Script must have 777 permission)
  • Define the below parameters according to your environment...
         SECRET="--user=MySQLusername --password=MySQLpassword"
         BACKUP_DIR_ROOT=/backup/xtrabackup
         BACKUP_DIR_Daily=$BACKUP_DIR_ROOT/Daily
         DATA_DIR=/var/lib/mysql
         HOSTIP="192.168.1.1"
         SERVERNAME="Production-Slave-DB"
         TAR_COMPRESS="1"  ## 0 - for disable
  • For taking full backup run -: ./xtrabackup.sh full
  • For taking incremental backup run -:  ./xtrabackup.sh incremental
  • For taking restore backup run -: ./xtrabackup.sh restore
  • Restore command is very critical . It will prepare FULL and all incremental in FULL.

How to configure the cron job for automation of this script ?

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

##This CRON will take Daily FULL backup at 12:01 AM and then incremental on every 2 Hour . Before taking the full backup it will remove all the previous backups. You can change the remove policy according to your disk space.
## Herein I'm not automate restore command . I will perform this task manually once needed. You can do as per your policy.

01 00 * * * root cd /xtra-bkp-incr && ./xtrabackup.sh full    
01 */2 * * * root cd /xtra-bkp-incr && ./xtrabackup.sh incremental

For any Query on this script you can contact us or Submit you Query Here!

Please write you valuable comments and feedback in the comment box. Your valuable feedback is highly appreciated .

Please Follow us to update and notify for such type of articles and scripts. 


Saturday, 16 June 2018

Shell Script for MySQL daily backup


Task automation is most important part of all DBA's work for monitoring and scheduling task like Automatic backup, DB health checkup,Daily Checklist , populate & Email alerts , Replication Status , Server Utilization and much more .

In this article, I'm writing a Shell script for automatic MySQL Daily Backup which can be schedule by cron job for automation of their result.
This Script is written in very simple way and logic which you can easily understand.

Features....

  • Automation of Daily Full Backup using Percona XtraBackup
  • No production loss during the backup 
  • Creating a mysql-binlog check-points in every backup so that you can easily use the same for point-in-time recovery.
  • Create Backup Directory if not exist.
  • No repetition of same day backup.
  • Creating a Log File for whole process.
  • Sending Email Alerts for any failure/success backup  
  • Easy-to-Understand 

Limitation...

  • You must have to installed Percona XtraBackup
  • Must have to configure Mail smtp function for sending email alerts
  • Occupy the disk storage if data size is very large

#!/bin/bash -x
#By- Alok Kumar Singh @ www.getmysql.info
#Scheduling Daily Full Backup from Production/Backup Server using Xtrabackup without any table lock or any hamper on production during backup.      #Version- 1.2v
#06-Jun-2018 04:48PM

HOSTIP="191.168.1.100"
emails="query@getmysql.info alok@getmysql.info" ## add multiple email with space
BACKUP_DIR=/home/alok/DailyFullXtraBackup ##this directory must be created with 777 privileges
DATA_DIR=/var/lib/mysql
BACKUP_LOG=/home/alok/backup.log
USER_ARGS=" --user=user --password=password"
DATETIME=$(date +%y%m%d:"%T.%3N")
BAKFOLDER=$BACKUP_DIR/Backup_$(date +%Y%m%d)
TMPFILE="/tmp/XtraBackup-$(date +%y%m%d"%T").$$.tmp"
MailsubjectFAIL="Error-MySQL:DailyFullXtraBackup job Failed on $HOSTIP"
MailsubjectOK="OK-MySQL:DailyFullXtraBackup job Successful on $HOSTIP"

         echo "$DATETIME :: Full backup requesting" >> $BACKUP_LOG
         echo "$DATETIME :: Checking backup dir" >> $BACKUP_LOG
         date
         if [ ! -d $BACKUP_DIR ]
         then
         echo "$DATETIME :: ERROR: the folder $BACKUP_DIR does not exists" >> $BACKUP_LOG
  ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been failed."$'\n'""$'\n'"Reason:  ERROR: the folder $BACKUP_DIR does not exists"
  for address in $emails; do
  echo -e $ERRMSG | mail -s "$MailsubjectFAIL" $address
         done
         exit 1
         fi
  if [ -f $BAKFOLDER.tgz ]
         then
         echo "$DATETIME :: ERROR: one backup ($BAKFOLDER) already exist for current day. Rename/delete it before execute new one." >> $BACKUP_LOG
         ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been failed."$'\n'""$'\n'"Reason: ERROR: one backup ($BAKFOLDER) already exist for current day. Rename/delete it before execute new one."
  for address in $emails; do
  echo -e $ERRMSG | mail -s "$MailsubjectFAIL" $address
         done
  exit 1
  else
  mkdir $BAKFOLDER
         chmod -R 777 $BAKFOLDER
  echo "$DATETIME :: Backup folder created  $BAKFOLDER ..." >> $BACKUP_LOG
         fi
   
        date
        echo "$DATETIME :: Found backup directory $BAKFOLDER" >> $BACKUP_LOG
        echo "$DATETIME :: Starting backup in progress..." >> $BACKUP_LOG
        sudo xtrabackup $USER_ARGS --backup --no-timestamp --target-dir=$BAKFOLDER > $TMPFILE 2>&1
        if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
        echo "$DATETIME :: ERROR:Backup Failed in execution time." >> $BACKUP_LOG
    
        ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been failed."$'\n'""$'\n'"Reason: ERROR:Backup Failed in execution time."
 for address in $emails; do
 echo -e $ERRMSG | mail -s "$MailsubjectFAIL" $address
 done
 cat $TMPFILE
 rm -f $TMPFILE
 exit 1
        fi
        echo "$DATETIME :: Backup done as $BAKFOLDER" >> $BACKUP_LOG
 cat $TMPFILE
        rm -f $TMPFILE
##preparing backup
 echo "$DATETIME :: Backup preparation started..." >> $BACKUP_LOG
 sudo xtrabackup $USER_ARGS --prepare --no-timestamp --target-dir=$BAKFOLDER > $TMPFILE 2>&1
 if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
        echo "$DATETIME :: ERROR:Preparation Failed" >> $BACKUP_LOG
        ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been failed."$'\n'""$'\n'"Reason: ERROR:Backup has been done but Backup Preparation Failed."
 for address in $emails; do
 echo -e $ERRMSG | mail -s "$MailsubjectFAIL" $address
        done
 cat $TMPFILE
        rm -f $TMPFILE
        exit 1
        fi
    
        echo "$DATETIME :: Preparation done as $BAKFOLDER" >> $BACKUP_LOG

## Compress Backup 
        cd $BACKUP_DIR && sudo tar --create --gzip --file=Backup_$(date +%Y%m%d).tgz Backup_$(date +%Y%m%d) --remove-files 
## to Uncompress use  tar -xzvf FILENAME.tgz
        echo "$DATETIME :: Compress Successful as $BAKFOLDER.tgz" >> $BACKUP_LOG
   
        ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been Successful ."$'\n'""$'\n'"Backup Path: $BAKFOLDER.tgz"
 for address in $emails; do
 echo -e $ERRMSG | mail -s "$MailsubjectOK" $address
        done
        cat $TMPFILE
        rm -f $TMPFILE
    

##########################################################
### Copyright@ Alok Kumar Singh with www.getmysql.info ###
##########################################################
For any Query on this script you can contact us or Submit your Query Here!

Please write you valuable comments and feedback in the comment box. Your valuable feedback is highly appreciated .

Please Follow us to update and notify for such type of articles and scripts. 

Saturday, 9 June 2018

MySQL DBA task Automation




If you are using MySQL Community Edition and you want to Automate all your DBA task like DB health checkup, Daily Checklist, populate & Email alerts , Replication Status , Server Utilization and much more ,then you can easily move on Bash Shells Script which is one of powerful consideration to achieve all you task


Saturday, 2 June 2018

MySQL Replication Monitoring Alert Script

I have written and used this script to get MySQL replication status alert on all scenario like Replication Down/ Second_behind_master going up / and again Replication status Up .

You can modify the notification logic as per your convenience.  

Just create /usr/bin/getmysqlrepl.sh blank script file and copy-paste below script. 

#!/bin/bash
# Checks MySQL Replication status.  Sends user(s) a notification of status
# BY- Alok Kumar Singh @ www.getmysql.info
# 31-may-2018 11:26 AM

MasterHost="192.168.1.100"
SlaveHost="192.168.1.101"
emails="alok@getmysql.info" #multiple emails space separated
HOST="sudo mysql --user=user --password=password" ##for your password security we suggest you kindly create seperate user for Alert with all Show/Select Privileges.


SQLresponse=$($HOST -e "show slave status \G" |grep -i "Slave_SQL_Running"|gawk '{print $2}')
IOresponse=$($HOST -e "show slave status \G" |grep -i "Slave_IO_Running"|gawk '{print $2}')
SSTATUS=$($HOST -e "SHOW SLAVE STATUS\G" | egrep "Master_Host|Master_User|Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Last_Errno|Last_Error|Seconds_Behind_Master")
SLAVEBEHIND=$($HOST -e "show slave status \G" |grep -i "Seconds_Behind_Master"|gawk '{print $2}')



if [ "$SQLresponse" == "No" ]
 then
       ERRMSG="Replication on the slave MySQL server($SlaveHost) has stopped working"$'\n'"$SSTATUS"
    DownSubject="Replication Status Down on $SlaveHost"
      status=1
elif [ "$IOresponse" == "No" ]
 then
      ERRMSG="Replication on the slave MySQL server($SlaveHost) has stopped working"$'\n'"$SSTATUS"
      DownSubject="Replication Status Down on $SlaveHost"
   status=1
elif [ "$SLAVEBEHIND" == "NULL" ]
 then
  ERRMSG="The Slave is reporting 'NULL' (Seconds_Behind_Master)"$'\n'"$SSTATUS"
  DownSubject="Replication Status Down on $SlaveHost"
  status=1
elif [ "$SLAVEBEHIND" != 0 ]
 then
    ERRMSG="The Slave is at least $SLAVEBEHIND seconds behind the master (Seconds_Behind_Master)"$'\n'"$SSTATUS" 
 DownSubject="Replication Status Lag Behind $SLAVEBEHIND on $SlaveHost"
 status=1
else
 GoodSubject="Replication Status UP on $SlaveHost"
    GoodMessage="MySQL replication on $SlaveHost is working fine"$'\n'"$SSTATUS"
 status=0
fi

# Sending mail Alert....

if [ $status == 1 ]
 then
      for address in $emails; do
                echo -e $ERRMSG | mail -s "$DownSubject" $address 
                echo "$(date): On $SlaveHost Replication down, sent email to $address"$'\n'"$ERRMSG" >> /home/alok/maillog.log ##writing log file for mail alert
      done
elif [ $status == 0 ]
 then
      for address in $emails; do
             echo -e $GoodMessage | mail -s "$GoodSubject" $address
                echo "$(date): On $SlaveHost Replication is up, Mail sent to $address"$'\n'"$GoodMessage" >> /home/alok/maillog.log
      done
fi

#########################################################
### Copyright@ Alok Kumar Singh with www.getmysql.info###
#########################################################
If you will get any issue , kindly contact us _ Click Here or write us comment.

Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...