Backup script for MySQL backups locally, both mysqldumps and binary backupos using percona's xtrabackup

Bash MySQL backupo script

#!/bin/bash

# Configuration
. /etc/mysql/mysql_backup.conf

# Dump the mysql databases to a daily dump file.
function log()
{
  logger -i -p daemon.info -t mysqldump "$1"
	echo $1 >> $LOGFILE

	if [ -n "$2" ] ;then
		echo $1 | $HIPCHATALERT
	fi
}

log "starting backup on `hostname -f`" hipchat

# Test for lock file.
if [ -e ${LOCKFILE} ] ;then
  log "Lockfile ${LOCKFILE} exists, skipping backup on `hostname -f`" hipchat
  # Mail reports
  mail -s "hetzner backup02 skipped for $(date): lockfile ${LOCKFILE} found" $ALERT_EMAILS < $LOGFILE
  exit 1
fi

# Write pid to lockfile.
echo $$ > ${LOCKFILE}

## BINARY BACKUPS FIRST

log "starting binary backups for `hostname -f`"

# Check if the highest numbered backup exist and delete it
if [ -e "${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.${KEEP_BINARY_BACKUPS}.tar.gz" ]; then
  log "$(date): Expire oldest daily backup: ${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.${KEEP_BINARY_BACKUPS}.tar.gz"
  /bin/rm -rf ${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.${KEEP_BINARY_BACKUPS}.tar.gz >> $LOGFILE
fi

# Rename all backups to N+1
for i in $(seq ${KEEP_BINARY_BACKUPS} -1 0); do
  if [ -e "${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.${i}.tar.gz" ]; then
    new=$((${i} + 1))
    log "$(date): Rename backup ${i} to ${new}"
    /bin/mv -f "${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.${i}.tar.gz" "${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.${new}.tar.gz" >> $LOGFILE
  fi
done

# Create new full binary backup
log "$(date): Creating full backup in ${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0"
if [ -d "${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0" ]; then
  /bin/rm -rf ${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0
fi
mkdir -p ${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0
$BACKUP_BIN "${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0" --user=${MYSQL_USER} --password=${MYSQL_PASS} --use-memory=12GB --slave-info --safe-slave-backup >> $LOGFILE
#touch ${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0

# # Tar it up
log "$(date): Creating archive of daily backup in ${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0.tar.gz"
/bin/tar -zcf "${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0.tar.gz" "${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0" >> $LOGFILE

## MYSQLDUMP BACKUPS NEXT

log "Dump to .sql files"

# Check if the highest numbered backup exist and delete it
if [ -d "${BACKUP_DIR}/${DUMP_BACKUP_BASE_NAME}.${KEEP_DUMP_BACKUPS}" ]; then
  log "$(date): Expire oldest daily backup: ${BACKUP_DIR}/${DUMP_BACKUP_BASE_NAME}.${KEEP_DUMP_BACKUPS}"
  /bin/rm -rf ${BACKUP_DIR}/${DUMP_BACKUP_BASE_NAME}.${KEEP_DUMP_BACKUPS} >> $LOGFILE
fi

# Rename all backups to N+1
for i in $(seq ${KEEP_DUMP_BACKUPS} -1 0); do
  if [ -d "${BACKUP_DIR}/${DUMP_BACKUP_BASE_NAME}.${i}" ]; then
    new=$((${i} + 1))
    log "$(date): Rename backup ${i} to ${new}"
    /bin/mv -f "${BACKUP_DIR}/${DUMP_BACKUP_BASE_NAME}.${i}" "${BACKUP_DIR}/${DUMP_BACKUP_BASE_NAME}.${new}" >> $LOGFILE
  fi
done

# Create new full dump backup
log "$(date): Creating full dump backup in ${BACKUP_DIR}/${DUMP_BACKUP_BASE_NAME}.0"

DUMP_DIR="${BACKUP_DIR}/${DUMP_BACKUP_BASE_NAME}.0"

if [ -d "${DUMP_DIR}" ]; then
  /bin/rm -rf ${DUMP_DIR}
fi
mkdir -p ${DUMP_DIR}

# Get a re-useable list of databases.
DATABASES="$(mysql -u ${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -B -s -e 'show databases')"

# Dump mysql tables.
#
for db in ${DATABASES}; do
        if [ "z${db}" != "zinformation_schema" ]; then
                log "Dumping database '${db}'...";
                /usr/bin/ionice -c3 /usr/bin/mysqldump -u ${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} --single-transaction --triggers --quick --routines --master-data=2 --result-file "${DUMP_DIR}/${db}.sql" --databases "${db}";
                log " ok."

                log "Dumping database definitions '${db}'...";
                /usr/bin/ionice -c3 /usr/bin/mysqldump -u ${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} --no-data --single-transaction --triggers --quick --routines --master-data=2 --result-file "${DUMP_DIR}/${db}.frm.sql" --databases "${db}";
                log " ok."
        fi
done


# # Tar it up
log "$(date): Creating archive of daily backup in ${DUMP_DIR}"

# Do a compression run.
# Note: do not compress the archive as a whole so it is easier to retrieve individual backups
for db in ${DATABASES}; do
        # If the database dump exists, compress it.
        #
        log "Compressing ${db}.sql";
        if [ -f "${DUMP_DIR}/${db}.sql" ]; then
                /usr/bin/ionice -c3 /bin/tar -zcf "${DUMP_DIR}/${db}.sql.tar.gz" "${DUMP_DIR}/${db}.sql";
		/bin/rm -rf "${DUMP_DIR}/${db}.sql";
        fi
        log " ${db}.frm.sql";
        if [ -f "${DUMP_DIR}/${db}.frm.sql" ]; then
                /usr/bin/ionice -c3 /bin/tar -zcf "${DUMP_DIR}/${db}.frm.sql.tar.gz" "${DUMP_DIR}/${db}.frm.sql";
		/bin/rm -rf "${DUMP_DIR}/${db}.frm.sql";
        fi
        log " ok."
done



# reset variables
log "$(date): Cleaning up"
log "$(date): Backup size: `du -sh ${BACKUP_DIR}/${BINARY_BACKUP_BASE_NAME}.0`" hipchat
log "$(date): Backup size: `du -sh ${DUMP_DIR}`" hipchat
log "$(date): Free disk space: `df -h`" hipchat
log "$(date): MySQL Slave Status: `/usr/bin/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -e 'show slave status\G'`" hipchat

# Remove lockfile.
/bin/rm -f ${LOCKFILE}

# Mail reports
mail -s "hetzner backup02 report for $(date)" $ALERT_EMAILS  < $LOGFILE
log "backup complete for $(date) on `hostname -f`. Log: $LOGFILE" hipchat