MySQL – Daily database dumps, all nicely sorted.
Contributed by bofh468 
November 11, 2003 
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).