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 can use RSYNC to get a copy of the whole hosting directory every night. I really just need to get a mysqldump and .gzip it to a directory.
The script now date-stamps the output file, and cleans up old backups after a specified number of days. The user-editable variables are now all at the top instead of using more hard-coded values.
Below is the new single bash script. For your use, replace everything at the top in double angle brackets (ie. <<server>>).
It’s likely this script will stay in place, untouched for another 5-7 years. Set it and forget it. Automation is awesome!
#!/bin/bash
#database_backup.sh
#
# Backup specified database to a date-stamped, gzipped file.
# Delete files older than x days.
#
# 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
# Get the return code of mysqldump
result=$?
# Wait until the gzip completes
wait
# Now it is safe to remove the pipe
rm pipe
# Remove files older than 7 days
find $TARGET_DIR -type f -mtime +${DAYS_TO_RETAIN} -name '*.gz' -delete