Linux Mysql Backup Script

2017-01-01

Use with cron on a daily schedule and to keep 90 days worth of dumps

#!/bin/bash
SERVER=$1
DATABASE=$2
PASSWORD=$3
EMAIL=$4
DATE=$(date +%Y-%m-%d) # TODAYS DATE
FOLDER=/backups/$SERVER/$DATABASE # FOLDER FOR BACKUPS
FILENAME=$FOLDER/$DATE.sql # FILENAME FOR BACKUPS
NUMDAYS=90 # NUMBER OF DAYS TO KEEP

mkdir -p $FOLDER

echo "CREATE DATABASE $DATABASE;" > $filename
echo "USE $DATABASE;" >>  $filename
mysqldump -q --routines --skip-triggers -h $SERVER -u root -p$PASSWORD $DATABASE  >> $FILENAME
EXITCODE=$?

if [ $EXITCODE -ne 0 ] ; then

        #EMAIL
        cat << EOF |
        Subject:$DATABASE Backup Failed
        Backup Failed with exit code $EXITCODE
        EOF
        /usr/sbin/ssmtp $EMAIL
        
else

        FILESIZE=$(du -h $FILENAME)
        
        #CLEAN UP
        NUMFILES=$(find $FOLDER -type f | wc -l)
        DIF=$(expr $NUMFILES - $NUMDAYS)
        
        if [ $DIF -gt 0 ] ; then
                find $FOLDER -type f | sort -r | tail -n $DIF > tmp.txt
                FILESTOREMOVE=$(<tmp.txt)
                cat tmp.txt | xargs rm
                rm tmp.txt
        fi
        
        #EMAIL
        cat << EOF |
        Subject:$DATABASE Backup Complete
        Backup Completed Successfully ($FILESIZE)
        
        Files Removed:
        $FILESTOREMOVE
        EOF
        /usr/sbin/ssmtp $EMAIL
        
fi

---

mysql   database   backup   automatic   script