Using cron to create website and MySQL backups that rotate nicely over a week

I wrote this for a PHP/MySQL site that needed to have it’s databases backed up hourly and the whole site including uploads backed up daily. They wanted all databases going back 24 hours every hour, and then all content going back 7 days every day.  This creates a directory structure that looks like the following:

In this scenario assume the following:

• /mnt/backup/servername is an NFS mounted location I am backing up to
• /var/www is the location where the website is stored

Firstly for the impatient, here is the 2 lines you need to put into crontab:

0 0 * * * mkdir -p /mnt/backup/servername/`date +\%A`;  mysqldump -uUser -pPassword --all-databases > /mnt/backup/servername/`date +\%A`/all-databases-daily.sql; tar -czf /mnt/backup/servername/`date +\%A`/www.tar.gz /var/www/
0 * * * * mysqldump -uUser -pPassword --all-databases > /mnt/backup/servername/all-databases-hourly-`date +\%H`.sql

For the less impatient I’ll explain how it works.

Daily Backups

I created 2 cronjobs. The first is to do the daily backups. Lets break this job down:

First of all we want the job to run every day at midnight

0 0 * * *
^----------Run at minute 0
  ^--------Run at hour 0
    ^------Run every day of month
      ^----Run every month of year
        ^--Run every day of week

Next we need to check our backup target directory exists and is named correctly:

mkdir -p /mnt/backup/servername/`date +\%A`;
  ^------------------------------------------Create directory
       ^-------------------------------------Create recursively, so if /mnt doesn't exist (god forbit!) it will create it as well
         ^---------------------^-------------Root of backup folder
                                ^---------^--Output the day of the week in English (eg "Saturday")

Next we need to dump the databases into the just created folder:

mysqldump -uUser -pPassword --all-databases > /mnt/backup/servername/`date +\%A`/all-databases-daily.sql;
   ^------------------------------------------------------------------------------------------------------MySQL cli tool used to dump content to stdout
          ^---------------^-------------------------------------------------------------------------------Your username and password
                                   ^----------------------------------------------------------------------This switch tells mysqldump to output every database in the server the user can access
                                            ^-------------------------------------------------------------Using > diverts stdout to a given file
                                              ^--------------------------------^--------------------------Backup folder root and date as above example
                                                                                        ^-----------------Target filename

And finally, we tar and gzip the web root for the daily backup into the same folder:

tar -czf /mnt/backup/servername/`date +\%A`/www.tar.gz /var/www/
^------^---------------------------------------------------------Create a new archive using gzip and output to a file
         ^-------------------------------------------^-----------File to output
                                                       ^-------^-Directory to archive

Hourly Backups

First the frequency:

0 * * * *
^----------Run at minute 0
  ^--------Run every hour
    ^------Run every day of month
      ^----Run every month of year
        ^--Run every day of week

And then the database dumps themselves:

mysqldump -uUser -pPassword --all-databases > /mnt/backup/servername/all-databases-hourly-`date +\%H`.sql
   ^------------------------------------------------------------------------------------------------------MySQL cli tool used to dump content to stdout
          ^---------------^-------------------------------------------------------------------------------Your username and password
                                   ^----------------------------------------------------------------------This switch tells mysqldump to output every database in the server the user can access
                                            ^-------------------------------------------------------------Using > diverts stdout to a given file
                                              ^--------------------------------^--------------------------Backup folder root and date. This time using %H instead of %A tells it to output the hour
                                                                                        ^-----------------Target filename

Looks like…

The final output, after it’s run for a week, should look something like this:

root@localhost:~# ls -l /mnt/backup/servername/
total 33216
-rw-r--r-- 1 768 500 1416355 2009-05-08 23:38 all-databases-hourly-00.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 00:38 all-databases-hourly-01.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 01:38 all-databases-hourly-02.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 02:38 all-databases-hourly-03.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 03:38 all-databases-hourly-04.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 04:38 all-databases-hourly-05.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 05:38 all-databases-hourly-06.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 06:38 all-databases-hourly-07.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 07:38 all-databases-hourly-08.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 08:38 all-databases-hourly-09.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 09:38 all-databases-hourly-10.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 10:38 all-databases-hourly-11.sql
-rw-r--r-- 1 768 500 1416355 2009-05-09 11:38 all-databases-hourly-12.sql
-rw-r--r-- 1 768 500 1416457 2009-05-08 12:38 all-databases-hourly-13.sql
-rw-r--r-- 1 768 500 1416457 2009-05-08 13:38 all-databases-hourly-14.sql
-rw-r--r-- 1 768 500 1416383 2009-05-08 14:38 all-databases-hourly-15.sql
-rw-r--r-- 1 768 500 1416383 2009-05-08 15:38 all-databases-hourly-16.sql
-rw-r--r-- 1 768 500 1416355 2009-05-08 16:38 all-databases-hourly-17.sql
-rw-r--r-- 1 768 500 1416355 2009-05-08 17:38 all-databases-hourly-18.sql
-rw-r--r-- 1 768 500 1416355 2009-05-08 18:38 all-databases-hourly-19.sql
-rw-r--r-- 1 768 500 1416355 2009-05-08 19:38 all-databases-hourly-20.sql
-rw-r--r-- 1 768 500 1416355 2009-05-08 20:38 all-databases-hourly-21.sql
-rw-r--r-- 1 768 500 1416355 2009-05-08 21:38 all-databases-hourly-22.sql
-rw-r--r-- 1 768 500 1416355 2009-05-08 22:38 all-databases-hourly-23.sql
drwxr-xr-x 2 768 500      54 2009-03-05 23:41 Friday
drwxr-xr-x 2 768 500      54 2009-03-08 23:41 Monday
drwxr-xr-x 2 768 500      54 2009-03-06 23:41 Saturday
drwxr-xr-x 2 768 500      54 2009-03-07 23:41 Sunday
drwxr-xr-x 2 768 500      54 2009-03-04 23:41 Thursday
drwxr-xr-x 2 768 500      54 2009-03-03 16:21 Tuesday
drwxr-xr-x 2 768 500      54 2009-03-03 23:41 Wednesday
root@localhost:~# ls -l /mnt/backup/servername/Friday/
total 357600
-rw-r--r-- 1 768 500   1414721 2009-05-07 23:38 all-databases-daily.sql
-rw-r--r-- 1 768 500 364579398 2009-05-08 00:30 home.tar.gz