mysql database backup script

dbbackup.sh

#!/usr/bin/env bash

# Simple mysql database backup script
# This can be easily put into a cron job to automatically maintain a rolling 7 day backup of all
# databases accessible to the user running the mysql command. Ensure a ~/.my.cnf file exists in order
# for the script to be able to authenticate to mysql.
# 
# Usage with optional destination path as the only parameter:
# dbbackup.sh
# dbbackup.sh /data/dbbackup
#
# Reference: https://github.com/classyllama/ansible-role-dbbackup/blob/master/templates/dbbackup.sh.j2

set -e

# Set variables
DUMP_DIRECTORY="/var/dbbackup"
[ -n "$1" ] && DUMP_DIRECTORY=$1

TIMESTAMP=$(date --utc +%Y%m%dT%H%M%SZ) # Get ISO 8601 UTC Timestamp

# Set default file permissions 600
umask 177

# Make sure dump directory exists
mkdir -p ${DUMP_DIRECTORY}

# Get list of all databases
DATABASES=$(mysql -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")

# Loop through each database in list and dump to file
for DATABASE in ${DATABASES}; do
    
     # Dump Database Schema
     mysqldump \
         --single-transaction \
         --triggers \
         --routines \
         --events \
         --no-data \
         --dump-date \
         ${DATABASE} | \
     gzip > ${DUMP_DIRECTORY}/${TIMESTAMP}_${DATABASE}_schema.sql.gz

     # Dump Database Data
     mysqldump \
         --single-transaction \
         --no-create-info \
         --no-create-db \
         --dump-date \
         ${DATABASE} | \
     gzip > ${DUMP_DIRECTORY}/${TIMESTAMP}_${DATABASE}_data.sql.gz
done

# Delete files older than 7 days
find ${DUMP_DIRECTORY}/* -mtime +7 -exec rm {} \;