Automating MySQL Backups with Bash

I’ve been putting more effort lately on disaster-planning, and a big part of that is having backups. Linode automatically backs up my entire system, but I noticed something troubling in their documentation:

This method is very reliable, but can fail to properly back up the data files for database services like MySQL. If the snapshot occurs during a transaction, the database’s files may be backed up in an unclean state.

Yikes! So while my system and files may be backed up, my databases- which mean everything to the various WordPress sites I host- could potentially be unrecoverable. That is no solution at all.

Following their advice, I decided to write a script I could use to automate this. mysqldump is the obvious tool for this job, and the output can be compressed with gzip to conserve space. The resulting bash script could then be added as a cron job to automate it.

For my own organizational sanity, I wanted to place each backup in a folder specific to that database, inside of a general backups folder, and append the name with the current date. The resulting heirarchy looks like this:

backup_directory >
- db_1_name >
- - db_1_name-2015-04-30.sql.gz
- - db_1_name-2015-05-01.sql.gz
- db_2_name >
- - db_2_name-2015-04-30.sql.gz
- - db_2_name-2015-05-01.sql.gz

This would make it easy to locate and restore from these files, should the need arise. It would also make it easy to download them to a remote location, which I will be doing manually.

Okay, enough already! Here’s the script:

#!/bin/bash
# note: current day's backup will be overwritten if run twice on the same day

# to unzip the gzipped file:
# gunzip name.sql.gz

# to unzip the gzipped file while preserving the original:
# gunzip -c name.sql.gz > name.sql


########## USER SETTINGS ##########

# mysql user info
USER=""
PSWD=""
HOST=""

# declare databases to be backed up
# syntax ( "db_1_name" "db_2_name" ... )
declare -a DBS=( "" )

# this is the base directory where the backups will live
# no trailing slash
DIRBASE=""

#toggle logging true/false
LOG=false


########## PERFORM BACKUPS  ##########
TODAY=$(date +"%Y-%m-%d")

if [ ! -d $DIRBASE ];
then
        echo "ERROR directory does not exist: $DIRBASE"
        exit 1
fi

if [ ! -w $DIRBASE ];
then
        echo "ERROR directory not writeable: $DIRBASE"
        exit 1
fi

for DBNAME in "${DBS[@]}"
do
        DIRFULL="$DIRBASE/$DBNAME"
        FILENAME="$DBNAME-$TODAY.sql.gz"
        PATHFULL="$DIRFULL/$FILENAME"

        # make subdirectory if necessary
        if [ ! -d $DIRFULL ];
        then
                mkdir $DIRFULL
        fi

        # backup, gzip
        mysqldump --user=$USER --password=$PSWD --host=$HOST $DBNAME | gzip -c > $PATHFULL

        # log
        if [ "$LOG" = true ];
        then
                logger "mysql-backup: $DBNAME to $PATHFULL"
        fi
done

That’s it! I just added it as a daily cron job and now I’m all set. The only drawback is that this script creates infinite backups, which could eat up space after a while. Later on I’ll modify the script to automatically prune away really old backups, but for now, I’ll just keep an eye on it.

Posted in Coding, How To, Website