MySQL – Daily database dumps, all nicely sorted.

Posted by acockrell in MySQL

A nice shell script that can be run as a cron job to produce nice MySQL dumps for backup.


I run several boxes with MySQL installed. My backup strategy for MySQL includes the following:

– Each database needs to be dumped nightly to it’s own file.
– I keep all miscellaneous files (eg DB Dumps) under /backup
– Since I retain the files for 4 or 5 days, I need to tag the files
with the date they were dumped.

Under /etc, create a subdirectory “db-backup”:
mkdir /etc/db-backup

Create/edit the file /etc/db-backup/db-list.txt and put each database to be dumped in that file, one per line. The script, as posted here, can’t deal with blank lines.

A quick example would be (without the dashes):

—-
mysql
pdns
mimerdesk
phpbb
—-

Then here’s the backup script. I keep it in /etc/db-backup/db-backup.sh

#!/bin/bash
# Set a value that we can use for a datestamp
DATE=`date +%Y-%m-%d`
# Our Base backup directory
BASEBACKUP="/home/backups/mysql"

for DATABASE in `cat /etc/db-backup/db-list.txt`
do
# This is where we throw our backups.
FILEDIR="$BASEBACKUP/$DATABASE"

# Test to see if our backup directory exists.
# If not, create it.
if [ ! -d $FILEDIR ]
then
mkdir -p $FILEDIR
fi

echo -n "Exporting database: $DATABASE"
/usr/local/bin/mysqldump --user=root --opt $DATABASE | gzip -c -9 > $FILEDIR/$DATABASE-$DATE.sql.gz
echo " ......[ Done ] "
done

# AutoPrune our backups. This will find all files
# that are "MaxFileAge" days old and delete them.
MaxFileAge=4
find $BASEBACKUP -name '*.gz' -type f -mtime +$MaxFileAge -exec rm -f {} \;

The above assumes that you don’t have a password set for the root user. If you do, just add the -p switch into mysqldump. If you supply the password after “-p” you won’t be prompted for it.

Do be sure to chmod u+rwx on the script, and chmod u+rw on db-list.txt. If you’re including the root password in the file, chmod oa-rwx both files (actually, do that anyway).

The Conversation

Follow the reactions below and share your own thoughts.

  • thanks very much for this explanation

  • N GowriSankar

    This looks great. I like the idea of gzipping it.

    For our requirement, we were taking two level backups = Daily backups and Monthly backups. The daily backups are recycled every week and monthly backups are stored for much longer duration [As a matter of fact, we have not yet deleted them for past 3 years]. Of course, our backups are only 200+ MB now.

    So we have ended up with:
    01-2008.dmp, 02-2008.dmp, 03-2008.dmp, … 05-2009, 06-2009.dmp, etc and seven daily dump files = Mon.dmp, Tue.dmp, Wed.dmp, … Sat.dmp, Sun.dmp

    We used `date +%e` to get the day of today. We used `date +%0m` to get month and `date +%0y` to get year.

  • Thank you for the tips!
    Very useful tips indeed.