While my original script works, something like this may be far simpler:
mysqldump [options] | gzip -c > /backups/data-$(date +%a-%H00).sql.gz
This specifically will keep 7 days worth of backups. It works by using the day of the week plus the hour. So it would look like data-Mon-00.sql.gz (Monday at midnight). When the week rolls around, the previous backups get overwritten.
If you set your cron to run every 6 hours you will get up to 28 backups (24/6) * 7 = 28.
To get a consistent backup with mysqldump you have to lock the tables, so this should only be done from a replica, or a low-volume server. mysqldump also makes a full snapshot, so you end up having a full snapshot for each backup, which can end up taking up a lot of disk space. This can quickly become unmanageable with a large database. You also may not want to trust a replica to take backups from as the replica also has to stay in sync.
A better option is xtrabackup by Percona. It's open source, so it's free. It requires InnoDB tables and is able to take a hot backup of your primary MySQL server without downtime or locks (you shouldn't be using MyISAM anyway). It uses a modified InnoDB engine with InnoDB's crash recovery feature to ensure the backups are consistent. It even takes incremental backups of a base snapshot, so you can make hundreds of backups and have it take up the size of a single snapshot. It works with MySQL, MariaDB, PerconaDB (forks of MySQL), and both InnoDB and XtraDB (improved InnoDB in both in MariaDB and Percona).
Personally I would stick with xtrabackup and not even bother with mysqldump. You have to do just as much work command-line wise, and you have the added benefit of small incremental backups. There are tools out there that automate the use of xtrabackup, even in a Galera cluster. Facebook uses it.
Original script:
The script below takes "backup.sql.gz", for example, and turns it into something like backup-13Nov2012-01_30.sql.gz, then rotates files using hard links.
#!/bin/bash
###########################################################################
# snapshot
#
# Rotates snapshots of backups using hard links
#
# Keeps track of:
# - 48 hours of snapshots (48*60/interval)
# - 60 days worth of midnight snapshots
# - 24 months of snapshots from the 1st
# Ussage:
# snapshot /path/to/backup.sql.gz
# mysqldump ... | gzip -c | snapshot /path/to/backup.sql.gz
#
###########################################################################
if ! hash date 2> /dev/null; then
echo "-snapshot: date command not found" 1>&2
exit 1
fi
if ! hash ln 2> /dev/null; then
echo "-snapshot: ln: command not found" 1>&2
exit 1
fi
# Date Info
month=$(date +"%b")
day=$(date +"%d")
year=$(date +"%Y")
time=$(date +"%H_%M")
date=$(date +"%d%b%Y")
dateFirst=$(date --date="$month 01" +"%d%b%Y" 2> /dev/null)
# Test to see if we're using GNU date or BSD date
if [ "$dateFirst" == "" ]; then
dateFirst=$(date -v1d +"%d%b%Y")
back2date=$(date -v-2d +"%d%b%Y")
back2monthY=$(date -v-2m +"%b%Y")
back2year=$(date -v-2y +"%Y")
else
back2date=$(date --date="-2 day" +"%d%b%Y")
back2monthY=$(date --date="-2 month" +"%b%Y")
back2year=$(date --date="-2 year" +"%Y")
fi
if [ "$dateFirst" == "" ]; then
echo "-snapshot: Unknown version of date command." 1>&2
exit 1
fi
# Directories
filepath=$1
backup=$2
if [ "$filepath" == "" ]; then
echo "-snapshot: Expecting filename as first argument" 1>&2
exit 1
fi
if [ "$backup" == "" ]; then
backup=/backup
fi
if [ ! -d "$backup" ]; then
echo "-snapshot: Directory "$backup" doesn't exist" 1>&2
exit 1
fi
snapshots=$backup/snapshots
daily=$backup/daily
monthly=$backup/monthly
basename=${filepath##*/}
ext=${basename#*.}
basename=${basename%%.*}
filename=$basename-$date-$time.$ext
##############################
# Make new snapshot
##############################
if [ ! -d "$snapshots/$date" ]; then
mkdir -p "$snapshots/$date"
fi
if [ -t 0 ]; then
if [ ! -f "$filepath" ]; then
echo "-snapshot: '$filepath' doesn't exist" 1>&2
exit 1
fi
ln "$filepath" "$snapshots/$date/$filename"
else
cat > "$snapshots/$date/$filename"
fi
##############################
# Daily/monthly snapshots
##############################
if [ "$time" == "00_00" ]; then
if [ ! -d "$daily/$month$year" ]; then
mkdir -p "$daily/$month$year"
fi
ln "$snapshots/$date/$filename" "$daily/$month$year/$filename"
if [ "$day" == "01" ]; then
if [ ! -d "$monthly/$year" ]; then
mkdir -p "$monthly/$year"
fi
ln "$snapshots/$date/$filename" "$monthly/$year/$filename"
fi
fi
##############################
# Clean up old snapshots
##############################
if [ -d "$snapshots/$back2date" ]; then
rm -fr "$snapshots/$back2date"
fi
if [ -d "$daily/$back2monthY" ]; then
rm -fr "$daily/$back2monthY"
fi
if [ -d "$monthly/$back2year" ]; then
rm -fr "$monthly/$back2year"
fi
