A simple DB backup script that keeps weeklies, monthlies, and yearlies, and ages.

gistfile1.sh

#!/bin/sh

#
# A very simple database backup and rotation script, in four stages:
#
#   1. dump
#   2. compress
#   3. link
#   4. age
#
# The first two are self-explanatory.  As for the third, on special days
# of the week, month, and year, make a hard link of today's backup in the
# weekly, monthly, or yearly directory.
#
# In the fourth stage --- aging --- throw out all but the n most recent
# backups.  Sort them, conveniently, by their filenames, which should work
# properly with simple sort(1).  When the last reference to a backup is deleted,
# the OS will throw it away.
# 

# Absolute toolbox
MYSQLDUMP=/usr/bin/mysqldump
BZIP2=/usr/bin/bzip2
DATE=/bin/date
LS=/bin/ls
SORT=/bin/sort
TAIL=/usr/bin/tail
EXPR=/usr/bin/expr
LN=/bin/ln
RM=/bin/rm

# Directory layout
TOP=/vol/db/mysql
LIVE=${TOP}/live
ARCHIVE=${TOP}/archive

# DB credentials
USER=backup
PW="your-pw-here!"

# derived filenames
ts=`$DATE +"%Y%m%d-%H%M%S" `
file="dump-${ts}.sql"
filebz="${file}.bz2"
daily=${ARCHIVE}/daily/${file}
dailybz=${ARCHIVE}/daily/${filebz}

dump() {
	$MYSQLDUMP -u$USER -p$PW --all-databases > $daily
}

compress() {
	$BZIP2 ${daily}
}

link1() {
	f=$1
	dir=$2

	if [ `$DATE +"%$f"` -eq 1 ]
	then
		$LN $dailybz ${ARCHIVE}/${dir}/
 	fi
}

age1() {
	dir=${ARCHIVE}/$1

	# Add 1 since we're going to passing this as an argument to tail, which is
	# 1-indexed, and not 0-indexed.
	n=`$EXPR $2 + 1`

	# Exploit the fact that our files are all the same length and are in the
	# right order due to timestamp format we've chosen.
	list=`$LS -1 $dir | $SORT -r | $TAIL -n +$n`
	for f in $list
	do
		$RM $dir/$f
	done
}

link() {
	link1 w weekly
	link1 d monthly
	link1 j yearly
}

age() {
	# Keep 10 dailies, 4 weeklies, 6 monthlies, and infinite yearlies
	age1 daily   10
	age1 weekly  4
	age1 monthly 6
}

# the "main" function
dump
compress
link
age