MySQL Backup Script using BASH

by | Aug 2, 2014 | Backup, Common Sense, General IT, IT, Linux, MySQL, Programming

So now that I have all of the systems setup and running for my websites, I needed a way to back up the data on a regular basis. There are of course many backup applications that you could use but I wanted something easy and cheap. I hunted around the internet for a bash backup script and came across a few. I have taken a few of the items used in each of them and built this script.

The script backups the databases into a directory that is labeled with the date. It will also clean up backups older then the retention period. It also needed to automatically backup new databases that may appear from time to time unless specifically stated.

So here is the script

#!/bin/bash

##Variables
NOW=`date +"%Y-%m-%d"`;
BACKUPPATH="/home/user/MySQLBackups"
BACKUPDIR="$BACKUPPATH/$NOW";
RETENTION="+7";

### Server Setup ###
#* MySQL login user name *#
MUSER="root";

#* MySQL login PASSWORD name *#
MPASS="password";

#* MySQL login HOST name *#
MHOST="127.0.0.1";
MPORT="3306";

# DO NOT BACKUP these databases
IGNOREDB="
information_schema
mysql
test
"

#* MySQL binaries *#
MYSQL=`which mysql`;
MYSQLDUMP=`which mysqldump`;
GZIP=`which gzip`;

# assuming that /nas is mounted via /etc/fstab
if [ ! -d $BACKUPDIR ]; then
  mkdir -p $BACKUPDIR
else
 :
fi

# get all database listing
DBS="$(mysql -u $MUSER -p$MPASS -h $ MHOST -P $MPORT -Bse 'show databases')"

# SET DATE AND TIME FOR THE FILE
#NOW=`date +"d%dh%Hm%Ms%S"`; # day-hour-minute-sec format
# start to dump database one by one
for db in $DBS
do
        DUMP="yes";
        if [ "$IGNOREDB" != "" ]; then
                for i in $IGNOREDB # Store all value of $IGNOREDB ON i
                do
                        if [ "$db" == "$i" ]; then # If result of $DBS(db) is equal to $IGNOREDB(i) then
                                DUMP="NO";         # SET value of DUMP to "no"
                                #echo "$i database is being ignored!";
                        fi
                done
        fi

        if [ "$DUMP" == "yes" ]; then # If value of DUMP is "yes" then backup database
                FILE="$BACKUPDIR/$NOW-$db.gz";
                echo "BACKING UP $db";
                $MYSQLDUMP --add-drop-database --opt --lock-all-tables -u $MUSER -p$MPASS -h $MHOST -P $MPORT $db | gzip > $FILE
        fi
done

# Delete files older than the retention variable
find $BACKUPPATH/* -mtime $RETENTION -exec rm -Rf {} \;

Update the variables at the top of the script and save it into your home directory or somewhere similar, I run the script at 5 minutes past 1am each morning to try and get a quite time.

sudo crontab -e

###
5 1 * * * /home/dean/MySQLBackups/MySQLBackup.sh > /dev/null

Next I want to be able to automate the ability to send the backups offsite aswell. So I will update the script in the future to do this.