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