Mysql Backup script using mysqldump

This script is written to backup and email the status of backup

Please make sure to replace your domain with example.com, and user and passwords accordingly




#!/bin/bash

### GLOBAL VARIABLES ###
 HOST=`hostname`
 MYSQL="/usr/bin/mysql"
 MYSQLDUMP="/usr/bin/mysqldump"
 MYSQL_USER=backupuser
 MYSQL_PASS=DBPASSWORD
 TSTAMP=`date +%F`
 BACKUP_DIR=/example/backup/db/$HOST
 mkdir $BACKUP_DIR/$TSTAMP
 databases=`$MYSQL -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|phpmyadmin)"`
 DB_VERSIONS=7
 DELVERSION=`expr "$DB_VERSIONS" + 1`
 DELDATE=`date +%F --date="$DELVERSION days ago"`

### EMAIL VARIABLES ###

TO=admin@example.com
 FROM="Backup Automation <automation@example.com>"
 EMAILUSER=user@mydomain
 EMAILPASS=PASSWORD
 SMTP=mail.example.com
 SUBJECT="`hostname` - Backup status (DB) : $TSTAMP"
 SENDEMAIL="/example/scripts/sendEmail.pl"

###################### Creating Fresh Backup ###################

STARTTIME=`date +%s`
 START=`date +%r`

for db in $databases; do
 $MYSQLDUMP --routines --triggers --force --opt --max_allowed_packet=2G -u $MYSQL_USER  -p$MYSQL_PASS $db  > "$BACKUP_DIR/$TSTAMP/$db.sql" && mv "$BACKUP_DIR/$TSTAMP/$db.sql" "$BACKUP_DIR/$TSTAMP/$TSTAMP-$db.sql"
 done

BTIME=`date +%s`
 BT=`date +%r`
 BSTATUS=`echo $?`
 if [[ "$BSTATUS" == 0 ]]
 then
 BACKUPSTATUS=SUCCESSFULL
 else
 BACKUPSTATUS=FAILED
 fi

#for db in $databases;do
 #     $MYSQLDUMP --force --opt --routines --user=$MYSQL_USER -p$MYSQL_PASS --databases $db | gzip > "$BACKUP_DIR/$TSTAMP/$db.sql.gz" && mv "$BACKUP_DIR/$TSTAMP/$db.sql.gz" "$BACKUP_DIR/$TSTAMP/$TSTAMP-$db.sql.gz"
 #done

####################### Zipping Backups #########################

for db in $databases; do gzip -f $BACKUP_DIR/$TSTAMP/$TSTAMP-$db.sql; done
 ZSTATUS=`echo $?`
 CTIME=`date +%s`
 CT=`date +%r`

if [[ "$ZSTATUS" == 0 ]]
 then
 ZIPSTATUS=SUCCESSFULL
 else
 ZIPSTATUS=FAILED
 fi

####################### Deleting old backups ########################

echo rm -frv "$BACKUP_DIR/$DELDATE" >> /tmp/deleteinfo
 rm -frv "$BACKUP_DIR/$DELDATE"
 DSTATUS=`echo $?`

if [[ "$DSTATUS" == 0 ]]
 then
 DELSTATUS=SUCCESSFULL
 else
 DELSTATUS=FAILED
 fi
 #find "$BACKUP_DIR" -mtime +"$DELDATE" |while read DEL ; do rm -frv "$DEL";done

ENDTIME=`date +%s`
 END=`date +%r`

$SENDEMAIL -f "$FROM" -t "$TO" -s "$SMTP" -xu "$EMAILUSER" -xp "$EMAILPASS" -u "$SUBJECT" -m "
 Databases : `echo $databases| tr ' ' ,`

Summary :

DB Backup started at $START
 DB Backup finished at $BT
 DB Backup time is `expr $BTIME - $STARTTIME` seconds

DB Zipping starged at $BT
 DB Zipping finished at $CT
 DB Zipping time is `expr $CTIME - $BTIME` seconds

DB Deletion started at  $CT
 DB Deletion finished at $END
 Old DB Deleting time is `expr $ENDTIME - $CTIME` seconds

Total Time is `expr $ENDTIME - $STARTTIME` seconds

BACKUP is $BACKUPSTATUS
 GZIPPING SQL DB FILES is $ZIPSTATUS
 DELETING OLDVERSION is $DELSTATUS
 "
 echo Done

Leave a Reply

Your email address will not be published. Required fields are marked *

*

* Copy This Password *

* Type Or Paste Password Here *

4,473 Spam Comments Blocked so far by Spam Free Wordpress

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>