06.13.08

Quick backup solution for mysql with daily and monthly rotated backups

Posted in programming at 16:10 by dalore

Using a combination of logrotate, cron and mysqldump one can easily create a backup solution that does daily and monthly backups.

First I’d recommend setting up a mysql user that has mysqldump privileges only, in my case I created one called backup and it was only accessiable by localhost. I didn’t put a password on it. Also create a directory for your backups to go in, in my case I used /var/backups/mysql/

Create a shell script in /etc/cron.daily to be run every day. I called mine mysqldump.daily and don’t forget to chmod +x it. Here is the contents:

#!/bin/bash
DEST=/var/backups/mysql/daily.dump
mysqldump --all-database -u backup > $DEST

For the monthly, create a similar file in /etc/cron.monthly/mysqldump.monthly

#!/bin/bash
DEST=/var/backups/mysql/monthly.dump
mysqldump --all-database -u backup > $DEST

Test it out by executing them at the command line. It should create the daily.dump and monthly.dump file, verify they exist and filled with your data. You can fine tune the dump command to be just the databases your interested in, but I wanted to do all of them.

To make it rotate so you get to keep the last 7 backups for daily and 6 backups for monthly, create a logrotate configuration file in /etc/logrotate.d/mysqldump

/var/backups/mysql/daily.dump {
    daily
    rotate 8
    compress
    missingok
}
/var/backups/mysql/monthly.dump {
    monthly
    rotate 7
    compress
    missingok
}

This will rotate the daily.dump file every day keeping 8 days worth, and the monthly.dump every month keeping 7 months worth.

Test it out by running logrotate in verbose and force mode:
logrotate -fv /etc/logrotate.d/mysqldump

You should see the backups being gzipped and rotated as per your schedule. Now if that works there is nothing else to do as putting the files in the specified directories will cause them to be executed at the correct times. Good luck!

Permalink Comments