Updated Database Backups

Navigation:

Now Playing:

Previous: AWS Marketplace Copy Protection

Next: Gravity Review

Updated Database Backups

Monday, August 4 2014

I've updated my previously-shared database backup script to take advantage of modern technology:

#!/bin/sh

DATE=`date +%Y%m%d`
DAYOFWEEK=`date +%u`
DATABASES=`mysql -ss -e 'SHOW DATABASES' | grep -v information_schema`
TMPDIR=`mktemp -d`
BUCKET='<redacted>'

for DB in ${DATABASES}; do
	FILE=${TMPDIR}/${DB}-${DATE}.bz2
	mysqldump --events ${DB} | bzip2 -9 - > ${FILE}

	# Copy this backup to Amazon S3 for durable external storage.
	s3cmd put ${FILE} s3://${BUCKET}/nightly/

	# If it's Sunday, do a weekly backup as well.
	if [ $DAYOFWEEK = 7 ]; then
		s3cmd put ${FILE} s3://${BUCKET}/weekly/
	fi
	rm ${FILE}
done
rmdir ${TMPDIR}

All of the rotation and deletion functionality has been removed. I've replaced it with S3 lifecycle rules:

  • Files in the 'nightly' directory are automatically deleted a few days after upload.
  • Files in the 'weekly' directory are kept online for a few weeks, then archived to Glacier for a few months before deletion.

There's a small but important difference in those rules compared to the previous system, which kept the most recent three nightly and five weekly backups. If my system goes offline for a period of time, or if my backup script fails, backups will continue to be expired with nothing coming in to replace them. As such, I expect to continue to tweak the lifecycle rules as I watch data going through the system to make sure I'm comfortable with the availability.

0 Comments

Copyright © 2001-2017 Chris Kuehn