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][1].
``# Scope: Back up the MySQL database<br />
# Author: Florian Sesser<br />
# Date: 2011-11-11, 2011-12-05<br />
# This script is to be placed into /etc/cron.d/ (it's a cron config file)<br />
#<br />
# We are on a debian-based distribution and use the debian-sys-maint<br />
# account. If you need to create an account for backup purposes, it<br />
# has to have permissions to "read" and "lock tables:<br />
#<br />
# mysql> grant select, lock tables on *.* to 'backup_script'@'localhost';<br />
# mysql> flush privileges;<br />
#<br />
SHELL=/bin/sh<br />
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin<br />
#<br />
# Make sure the $BACKUPPATH exists and is writable.<br />
BACKUPPATH=/var/backups/mysqldump<br />
# Set $MAILTO to have cron send eMails to that address.<br />
MAILTO='fs@it-agenten.com'<br />
#<br />
# m h dom mon dow user command<br />
#<br />
# Back up the database every day of week (at 5:23 AM)<br />
23 05 * * * root mysqldump --defaults-extra-file=/etc/mysql/debian.cnf -e -A | gzip > ${BACKUPPATH}/mysql_dump_`date +\%A`.sql.gz<br />
#<br />
# Back up the database every hour (at min 42).<br />
42 * * * * root mysqldump --defaults-extra-file=/etc/mysql/debian.cnf -e -A | gzip > ${BACKUPPATH}/mysql_dump_`date +\%H`h.sql.gz<br />
#<br />
#EOF#``
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.
[1]: https://gist.github.com/1433149