improve mysqldump

backup-mysql.sh

#!/bin/sh

# @notes
# 
# If you have a moderately sized database ( 2gb dumps) 
# make sure to update your mysql conf to allow larger packets 
# for dump. Below are some suggested my.cnf settings
# 
# If you have a fairly large db, mysqldump is not 
# recommended. Instead try something like
# http://www.percona.com/software/percona-xtrabackup
# 
# ``` my.cnf ````````````````````````````
# 
#   [client]
#   default-character-set=utf8
#   
#   [mysql]
#   default-character-set=utf8
#
#   [mysqld]
#   innodb_file_per_table
#   innodb_flush_method=O_DIRECT
#   collation-server = utf8_unicode_ci
#   init-connect='SET NAMES utf8'
#   character-set-server = utf8
#   
#   [mysqldump]
#   max_allowed_packet = 500M
#   
# ````````````````````````````````````````

# on error ... halt continued work
set -e
# errtrace is needed for the trap
set -o errtrace

TODAY=`date +%F`

TMP_BFILE="/tmp/backup_db_in_progress.sql"
TMP_LOCK="/tmp/backup_db_in_progress.lck"

# http://wiki.bash-hackers.org/howto/conffile
# @todo extract config from an external file
# @todo clean config values to prevent bad stuff from happening

# Main configuration
DB_HOST="*"
DB_USER="*"
DB_NAME="*"
DB_PASS="*"
BACKUPS_DIR="/data/backups/appname"
BACKUPS_PREFIX="appname-mysql"

BACKUP_TODAY=""

# To ensure the string doesn't have a trailing slash
remove_trailing_slash(){
    local dir=$1
    echo ${dir%/}
}

# Prepare the configuration
configure() {
    BACKUPS_DIR=`remove_trailing_slash $BACKUPS_DIR`
    BACKUP_TODAY="${BACKUPS_DIR}/${BACKUPS_PREFIX}-${TODAY}.gz"
}


# Setup lock and other fun
prepare_backup() {
    
    # Is the configured backup dir ... a dir?
    if [ ! -d $BACKUPS_DIR ]
    then
        echo "[err] backup directory $BACKUPS_DIR does not exist"
        return 11
    fi

    # Does a lock already exist?
    if [ -f $TMP_LOCK ]
    then
        echo "[err] $TMP_LOCK already exists"
        return 12
    else
        touch $TMP_LOCK
    fi
}

# Do actual backup
backup_mysql() {
    # All InnoDB tables, use --single-transaction for consistency without locking
    # --dump-date is default, which we use to validate the backup
    eval "mysqldump -h ${DB_HOST} --user ${DB_USER} --password=${DB_PASS} --single-transaction --databases ${DB_NAME} > ${TMP_BFILE}"
}

# Is the dumpfile good?
# use --dump-date from last line of dump
validate_backup(){
    # the last line contains completed_on data
    local lastline=`cat $TMP_BFILE | tail -n 1` 
    # get the first words
    local message=`echo "$lastline" | awk '{print $2,$3}'`
    # get the datestamp
    local dstamp=`echo "$lastline" | awk '{print $5}'`
    
    # if the dump doesnt have the date or dump completed 
    # it was likely a failed dump
    if [ "$dstamp" != "$TODAY" ] || [ "$message" != "Dump completed" ]
    then
        echo "[err] dump missing completed date"
        return 53
    fi
}

# Yup
compress_backup() {
    eval "gzip -c ${TMP_BFILE} > ${BACKUP_TODAY}"
}

# Remove old files and tmp
cleanup_files() {
    # remove tmp backup
    # eval "sudo sh -c 'rm -f ${TMP_BFILE}'"
    eval "rm -f ${TMP_BFILE}"

    # remove old backups
    find /data/backups -name "${BACKUPS_PREFIX}*" -ctime +10 -exec rm -f {} \;
}

# Cleans up files etc for exit
exit_cleanup() {
    rm -f $TMP_LOCK
}

# Do the sizes difference pass the set threshold?
passes_threshold_size_diff() {
    local threshold=$1
    local size_a="${2}"
    local size_b="${3}"

    # Have awk do the math for us
    echo $(eval "awk 'function abs(x){return ((x < 0.0) ? -x : x)} BEGIN { print (abs(1 - ( ${size_a} / ${size_b} )) > ${threshold}) }'")
}

# Check backups, is there filesize significantly different?
compare_backups() {
    # what percent is the threshold no acceptable?
    local threshold=0.10
    
    # Get the last two backups 
    local last_files=(`ls $BACKUPS_DIR -l | tail -n2 | awk '{print $5}'`)

    # Get all the files and calc the avg size
    local average_size=`ls $BACKUPS_DIR -l | tail -n+2 | awk '{print $5}' | awk '{i=1;i++;total = total + $1}END{printf("%.2f", (total / NR)) }'`

    # use awk to do math for threshold evaluation
    local exceeds_last=$(passes_threshold_size_diff $threshold "${last_files[0]}" "${last_files[1]}")
    local exceeds_avg=$(passes_threshold_size_diff $threshold "${average_size}" "${last_files[1]}")

    # if [ "${files[0]}" -gt "${files[1]}" ]
    # then 
    #     echo "[log] Older back is larger?"
    # fi

    if [ "${exceeds_last}" -eq 1 ]
    then
        echo "[err] The last backup is significantly different than the previous"
        return 9
    fi

    if [ "${exceeds_avg}" -eq 1 ]
    then
        echo "[err] The last backup is significantly different than the averages"
        return 9
    fi
}

# Main thing that does it all
main() {

    configure
    prepare_backup
    backup_mysql
    validate_backup
    compress_backup
    compare_backups
    cleanup_files
    exit_cleanup

    exit $?
}

handle_error() {

    # Clean stuff up ...
    exit_cleanup

    echo "[failed] line $1, exit code $2"
    exit $?
}

trap 'handle_error $LINENO $?' ERR 

main