Backup: Rolling MySQL snapshots in two lines of code

Short is sweet — you can’t make that many mistakes in two lines of code. When I recently looked for a quick way to enable regular backups of a MySQL DB for a project our team started working on, I first looked at automysqlbackup. After installing and configuring, I found it didn’t work on the latest Ubuntu Server Edition the machine was running. It also did not give me any debug output, and I wasn’t very keen on debugging that 90 KB bash script line by line.

Here is my solution to the task — a short config file for cron. In two lines, this gives us rolling hourly backups for the last 24 hours and rolling daily backups the last 7 days.

To use it: Adapt the file to your environment, create the needed read-only user in MySQL and the directories for the backup, and put the file into /etc/cron.d/.

If you should feel the need for a restore, you might want to list the available snapshots sorted by time. “ls -ltr /var/backups/mysqldump/” gives me a nicely sorted list.

For a longer-term backup, you can easily add a line for 52 weekly or 12 monthly rolling snapshots — but that would be an extra line or two.

You can download the file at GitHub.

# Scope: Back up the MySQL database
# Author: Florian Sesser
# 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;
# Make sure the $BACKUPPATH exists and is writable.
# Set $MAILTO to have cron send eMails to that address.
# 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

Update 2011-12-05: Updated script to use debian.cnf login credentials, -e, and added “MAILTO” cron config option. Thanks Marco and Spray for these suggestions! Also added download link to Github as WordPress wrecks the formatting of the file.

Verwandte Beiträge