Automating MySQL Backups (Revised)

As things do, times change, needs change, scripting knowledge changes. About 9 years ago I worked out a quick script to automate the backup and email of MySQL databases. It ran flawlessly for all these years, every night. With a recent hosting provider change, it was time to revisit the script and my needs.

I don't really need the script to email me the backups every night. I have a server at home that uses rsync to get a copy of the whole hosting directory every night. I really just need a mysqldump compressed to a directory.

The script now date-stamps the output file and cleans up old backups after a specified number of days. All user-editable variables are at the top.

Replace everything in double angle brackets (e.g. <<server>>) with your values:

#!/bin/bash
#database_backup.sh

# User Variables:
SERVER=<<server>>
DATABASE=<<database name>>
USERNAME=<<username>>
PASSWORD='<<password>>'   # Put in single quotes
TARGET_DIR=<<directory>>  # Include '/' at the end
DAYS_TO_RETAIN=7

# Script Variable:
NOW=$(date +%y%m%d)

# Create a pipe named 'pipe'
mkfifo pipe

# Set the input/output
gzip -9 < pipe > ${TARGET_DIR}${NOW}_${DATABASE}.sql.gz &

mysqldump \
    --host=$SERVER --user=$USERNAME --password=${PASSWORD} \
    --databases $DATABASE \
    --add-drop-table --extended-insert --quick > pipe

result=$?
wait
rm pipe

# Remove files older than DAYS_TO_RETAIN days
find $TARGET_DIR -type f -mtime +${DAYS_TO_RETAIN} -name '*.gz' -delete

It's likely this script will stay in place, untouched for another 5–7 years. Set it and forget it. Automation is awesome.