backup-mysql-databases

#!/opt/local/bin/bash
#
# Dumps all databases belonging to a user to seperate dump files.
#
# Copyright (c) 2013 Jacques Marneweck.  All rights reserved.
#
# Expects that you have a ~/.my.cnf configured to allow this script
# to connect to MySQL in order to dump databases.
#

if [[ "$1" == "-v" ]]; then
    shift;
    export export PS4='[\D{%FT%TZ}] ${BASH_SOURCE}:${LINENO}: ${FUNCNAME[0]:+${FUNCNAME[0]}(): }'
    set -o xtrace
fi
set -o errexit
set -o pipefail

#
# Check that ~/.my.cnf exists
#
if [[ ! -f ~/.my.cnf ]]; then
  echo "You need to create the ~/.my.cnf file into place"
  exit 1
fi

#
# Check that the config file exitss
#
if [[ ! -f config ]]; then
  echo "You need to copy config.example to config and customise to your liking."
  exit 1
fi

#
# Load the configuration file (see config.example for a sample configuration
# file)
#
source config

#
# Check that the backups directory exists if not create it and lock down
# permissions
#
if [[ ! -d $BACKUP_BASE_DIR ]]; then
  /usr/bin/mkdir $BACKUP_BASE_DIR
  /usr/bin/chmod 700 $BACKUP_BASE_DIR
fi

if [[ ! -d $BACKUP_BASE_DIR/mysql ]]; then
  /usr/bin/mkdir $BACKUP_BASE_DIR/mysql
fi

#
# Retrieves the list of the users databases and excludes the Database (Header) and
# the information_schema from the list of databases that will be dumped to
# individual dump files.
#
DATABASES=$(/opt/local/bin/mysql --batch -e "show databases;" | /opt/local/bin/ggrep -v Database | /opt/local/bin/ggrep -v information_schema)
for db in ${DATABASES[*]}
do
  /opt/local/bin/mysqldump --quote-names --dump-date --routines --triggers --opt ${db} | /opt/local/bin/bzip2 -9 > ${BACKUP_BASE_DIR}/mysql/${db}-`date +%Y-%m-%d`.sql.bz2
done

#
# Clean up the mysql dumps directory to the specified number of days worth of
# MySQL dump files to keep around.
#
cd ${BACKUP_BASE_DIR}/mysql
/opt/local/bin/gfind *.sql.bz2 -mtime +${MYSQL_DAYS_TO_KEEP} -delete

#
# Upload the backup files to a remote server
#
if [[ $BACKUP_OFFSITE_ENABLED == "yes" ]]; then
  /opt/local/bin/rsync -avz ${RSYNC_OPTS} ${BACKUP_BASE_DIR}/mysql ${BACKUP_USER}@${BACKUP_HOST}:${BACKUP_REMOTE_DIR}
fi