Nathan CrauseMusings of a Software Developer

Just thought I'd share this Linux BASH script which we run nightly to backup our MySQL database.

The features of this script:

• it keeps a revolving list of files for each day of the week, for each week of year, and for each month. We wanted some history, but not to get carried away.
• each table is represented in 2 separate files - one containing the "CREATE" instructions, one containing the "INSERT" instructions. This is to help restore data without re-creating the database.

#!/bin/sh
##############################################################################
# This script performs a database backup. It performs the following tasks:
#     - get a list of database names
#    - create directories based on database names
#    - for each database, get a list of all tables
#    - for each table, perform 2 separate MySQL dump instructions:
#        - generate table creation script
#        - generate data population script
#    - replace all "INSERT" instructions in data population script to
#        "REPLACE" so that data can be restored immediately.
##############################################################################

excluded() {
    if [ -f mysql_exclude ]; then
        grep -q $1 mysql_exclude
        if [ $? -eq 0 ]; then
            return 1
        else
            return 0
        fi
    else
        return 0
    fi
}

TMP=/tmp
DATE_TODAY=`date +%Y%m%d`
BACKUP_NAME=smartbackup-$DATE_TODAY
BACKUP_DIR=$TMP/$BACKUP_NAME
DESTIN=/var/backups/mysql
MYSQL_HOST=localhost
MYSQL_USER=db_user
MYSQL_PASSWORD=my_db_password

# create backup directory if doesn't exist
[ ! -d $BACKUP_DIR ] && (mkdir $BACKUP_DIR)

# loop through databases
for DATABASE in `mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -B -q -s -e "SHOW DATABASES;"`; do
    echo "Processing database $DATABASE"

    # test if database should be excluded from backup
    excluded $DATABASE;
    if [ $? -eq 0 ]; then
        # create a directory for the database
        DATABASE_DIR=$BACKUP_DIR/$DATABASE
        [ ! -d $DATABASE_DIR ] && (mkdir $DATABASE_DIR)

        # write the database create script
        echo "DROP DATABASE IF EXISTS \`$DATABASE\`;\nCREATE DATABASE \`$DATABASE\`;" > $DATABASE_DIR.sql

        # loop through tables
        for TABLE in `mysql -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -B -q -s -e "SHOW TABLES;" $DATABASE`; do
            echo "\tProcessing table $DATABASE.$TABLE"
            # create-only: --no-data --create-options 
            echo "USE $DATABASE;" > $DATABASE_DIR/$TABLE.create.sql
            mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD --no-data --create-options --allow-keywords $DATABASE --tables $TABLE >> $DATABASE_DIR/$TABLE.create.sql
            # insert-only: --complete-insert --extended-insert --no-create-info --order-by-primary --lock-tables --tables XXX
            echo "USE $DATABASE;" > $DATABASE_DIR/$TABLE.populate.sql
            mysqldump -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD --complete-insert --extended-insert --no-create-info --order-by-primary --lock-tables --allow-keywords $DATABASE --tables $TABLE | sed 's/INSERT/REPLACE/'  >> $DATABASE_DIR/$TABLE.populate.sql
        done
    else
        echo "\tExcluded!"
    fi
done

# package up the whole directory
tar -czf $BACKUP_DIR.tar.gz -C $TMP $BACKUP_NAME/

# remove any existing backups in that directory
rm -f $DESTIN/*.tar.gz

# move it into the backups drectory
[ ! -d $DESTIN ] && (mkdir $DESTIN)
mv $BACKUP_DIR.tar.gz $DESTIN/

# make a day-of-week copy
DOW=`date +%a`
[ ! -d $DESTIN/daily ] && (mkdir $DESTIN/daily)
rm -f $DESTIN/daily/$DOW-*.tar.gz
cp $DESTIN/$BACKUP_NAME.tar.gz $DESTIN/daily/$DOW-$DATE_TODAY.tar.gz

# see if it's time to make a weekly backup
if [ "$DOW" = "Fri" ]; then
    [ ! -d $DESTIN/weekly ] && (mkdir $DESTIN/weekly)
    cp $DESTIN/$BACKUP_NAME.tar.gz $DESTIN/weekly/`date +%Y`-week`date +%V`-ending`date +%d%b`.tar.gz
fi

# see if it's time to make a monthly backup
LAST_DOM=`date -d "$(date -d month +%Y-%m-1) -1 day" +%d`
if [ "$(date +%d)" = "$LAST_DOM" ]; then
    [ ! -d $DESTIN/monthly ] && (mkdir $DESTIN/monthly)
    cp $DESTIN/$BACKUP_NAME.tar.gz $DESTIN/monthly/`date +%Y%b`.tar.gz
fi