Follow us on Social Media:

MySQL Database Administrator

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

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...