Rolling MySQL snapshots in two lines of code

mysql_backup_cron

# Scope: Back up the MySQL database
# Author: Florian Sesser <fs@it-agenten.com>
# Date: 2011-11-11, 2011-12-05

# This script is to be placed into /etc/cron.d/ (it's a cron config file)

# We are on a debian-based distribution and use the debian-sys-maint
# account. If you need to create an account for backup purposes, it
# has to have permissions to "read" and "lock tables:
# 
# mysql> grant select, lock tables on *.* to 'backup_script'@'localhost';
# mysql> flush privileges;

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# Make sure the $BACKUPPATH exists and is writable.
BACKUPPATH=/var/backups/mysqldump
# Set $MAILTO to have cron send eMails to that address.
MAILTO='fs@it-agenten.com'

# m h dom mon dow user    command

# Back up the database every day of week (at 5:23 AM)
23 05   * * *   root    mysqldump --defaults-extra-file=/etc/mysql/debian.cnf -e -A | gzip > ${BACKUPPATH}/mysql_dump_`date +\%A`.sql.gz

# Back up the database every hour (at min 42).
42  *   * * *   root    mysqldump --defaults-extra-file=/etc/mysql/debian.cnf -e -A | gzip > ${BACKUPPATH}/mysql_dump_`date +\%H`h.sql.gz

#EOF#