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;
#
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#

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.

This post was written by

Florian Sesser – who has written posts on IT:Agenten GmbH.
Systems Architect. Security Engineer, Software Developer

Email

Tags: , ,

12 Responses to “Backup: Rolling MySQL snapshots in two lines of code”

  1. Marco Stipek 15. November 2011 at 17:08 #

    Cool thing, I already used it today for the satisfaction of another customer

  2. Marco Stipek 15. November 2011 at 17:10 #

    Shouldn’t be there any passphrase for that user if the data is critical, so the file permissions decides who can get access to the backup user ;)

  3. Marco Stipek 15. November 2011 at 17:16 #

    Here my proposal for Debian based systems there is already a user automagically created “debian-sys-maint”. The account data is stored in a root only readable file /etc/mysql/debian.cnf. So make your script root only executable (chown root:root and chmod 750) and insert the switch -p (yes without space!) into the mysqldump command in your script.

    • Florian Sesser 15. November 2011 at 18:21 #

      Glad you like it, and thank you for your input! About the debian-sys-maint user: I didn’t solve it that way because one could easily retrieve the password for the sys maint user with a simple listing of processes when the backup is running. On the other hand, my proposal is risky, too: Adding a user that can read everything and lock the database, and using it without a password.

      The proper way to backup a MySQL database in a production environment is by using replication.

      My quick hack above is used on our development server. If you want to run the above script (or the very similar automysqlbackup) on a production machine, I recommend running it only once a day, possibly early in the morning, when sane people are sleeping and the others already start to arrive at their workplace.

      • Florian Sesser 15. November 2011 at 18:57 #

        The best way to enhance the security of this IMHO is to use an option file: http://dev.mysql.com/doc/refman/5.1/en/password-security-user.html

        • Florian Sesser 15. November 2011 at 18:57 #

          … and maybe let some dedicated user that is not “root” execute the backup.

        • Marco Stipek 15. November 2011 at 19:12 #

          yeah, I haven’t seen your post ;)

      • Marco Stipek 15. November 2011 at 19:05 #

        ok. you are right, should not be used as parameter. The sollution is really simple:

        mysqldump –defaults-extra-file=/etc/mysql/debian.cnf -e –all-databases

        :)

  4. spray 19. November 2011 at 23:56 #

    u cant compare automysqlbackup with this short mysqldump cron.

    automysqlbackup uses mysqldump also, but with useful extras.

    In your case in a few days/weeks/months your diskspace is full with (automysqlbackup offers a rotation) and no one will realize (automysqlbackup is sending resultmails).

    think it would be better to patch automysqlbackup.

    • Florian Sesser 21. November 2011 at 09:39 #

      Thanks for your comment, spray!

      I believe you are mistaken, though. The backup cron job above _does_ rotate (by using “date”, check out the command line) and also cron sends eMails whenever a command (here: mysqlbackup) exits with an error status (i.e. not null). Check cron’s manpage.

      Also, I would have used automysqlbackup if it worked. But it didn’t, so I haven’t :) The useful extra in this cron.d file above is that it is super easy to debug, given that it’s really short.

  5. spray 21. November 2011 at 21:32 #

    most cronjobs mail to root or nobody or both ;)

    but youre right. its easier to config cron to send it to a humanadress instead of using automysqlbackup only for this purpose

    would you append this to your description?

    and overwirting with the weekday name (date +\%A) is a simple rotation, but its simpel, yes;)

  6. Florian Sesser 5. Dezember 2011 at 10:35 #

    Thank you for your suggestions, Marco and Spray! I updated the article accordingly.