MySQL - Daily database dumps, all nicely sorted.

Contributor Icon Contributed by bofh468 Date Icon November 11, 2003  
Tag Icon Tagged: 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).

Previous recipe | Next recipe |
 

Viewing 8 Comments

 
close Reblog this comment
Powered by Disqus · Learn more
blog comments powered by Disqus