auto rotating MySQL database backup

[brasslover]

Some time ago I wrote the following script to automate my MySQL database backups. I have been using this for years and have successfully tested restoring one of these backups without issues.

This script will produce a dump of your MySQL database and compress the output with bzip2. Additionally, it will automatically rotate previous backups giving you one backup for each of the last 7 successive executions as well as a monthly historical backup for each of the last 12 months. That means you'll get one daily backup for each of the last 7 days as well a monthly backup for each of the last 12 months if run from cron on a daily basis.

You'll need to update the db, path, host, user, & password variables with appropriate values, of course. Also, you'll probably want to take a look at the mysqldump command (and bzip2 command for that matter as well) to be sure you understand what it is doing and make any modifications there to suit your own needs.

None of my databases are really that huge and I am totally running this in a home environment, so bear this in mind. However, I would not anticipate that this script would run into problems were either of those not the case.

#!/bin/bash

month=`date +%b`
db='databasename'
path='/path/to/backup/folder/'
host='databasehostname'
user='databaseuser'
password='password'

rm -f $path$db-7.sql.bz2
rm -f $path$db-$month.sql.bz2

mv -f $path$db-6.sql.bz2 $path$db-7.sql.bz2
mv -f $path$db-5.sql.bz2 $path$db-6.sql.bz2
mv -f $path$db-4.sql.bz2 $path$db-5.sql.bz2
mv -f $path$db-3.sql.bz2 $path$db-4.sql.bz2
mv -f $path$db-2.sql.bz2 $path$db-3.sql.bz2
mv -f $path$db-1.sql.bz2 $path$db-2.sql.bz2

mysqldump --add-drop-table --add-locks --lock-tables=FALSE --all --quick --host=$host --user=$user --password=$password $db | bzip2 -c > $path$db-1.sql.bz2

cp $path$db-1.sql.bz2 $path$db-$month.sql.bz2

Here's a sample directory listing showing what the output looks like after at least 1 years worth of use.

ls -lah /path/to/backup/folder | grep databasename

-rw-r--r-- 1 root root 41K 2009-05-01 03:22 databasename-1.sql.bz2
-rw-r--r-- 1 root root 41K 2009-04-30 03:22 databasename-2.sql.bz2
-rw-r--r-- 1 root root 41K 2009-04-29 03:22 databasename-3.sql.bz2
-rw-r--r-- 1 root root 41K 2009-04-28 03:22 databasename-4.sql.bz2
-rw-r--r-- 1 root root 41K 2009-04-27 03:22 databasename-5.sql.bz2
-rw-r--r-- 1 root root 41K 2009-04-26 03:22 databasename-6.sql.bz2
-rw-r--r-- 1 root root 41K 2009-04-25 03:22 databasename-7.sql.bz2
-rw-r--r-- 1 root root 41K 2009-04-30 03:22 databasename-Apr.sql.bz2
-rw-r--r-- 1 root root 37K 2008-08-31 03:30 databasename-Aug.sql.bz2
-rw-r--r-- 1 root root 39K 2008-12-31 03:22 databasename-Dec.sql.bz2
-rw-r--r-- 1 root root 40K 2009-02-28 03:22 databasename-Feb.sql.bz2
-rw-r--r-- 1 root root 40K 2009-01-31 03:22 databasename-Jan.sql.bz2
-rw-r--r-- 1 root root 35K 2008-07-31 03:30 databasename-Jul.sql.bz2
-rw-r--r-- 1 root root 34K 2008-06-30 03:30 databasename-Jun.sql.bz2
-rw-r--r-- 1 root root 40K 2009-03-31 03:22 databasename-Mar.sql.bz2
-rw-r--r-- 1 root root 41K 2009-05-01 03:22 databasename-May.sql.bz2
-rw-r--r-- 1 root root 39K 2008-11-30 03:30 databasename-Nov.sql.bz2
-rw-r--r-- 1 root root 39K 2008-10-31 03:30 databasename-Oct.sql.bz2
-rw-r--r-- 1 root root 38K 2008-09-30 03:30 databasename-Sep.sql.bz2

If you have better ways to do the same thing please feel free to share.

[flarson]

Re: auto rotating MySQL database backup

That seems like a very handy script for someone who uses mysql. I really just want to say that I love your avatar.