May 5th, 2010 by Rudolf

Backup all mysql databases to separate dump files

With this script I backup my whole MySQL database into separate files. Restoring a broken DB is easier this way.

I included the possibility to keep 3 days worth of snapshots plus any backup older than  3 days will be removed automatically.

#!/bin/bash
# (c) 2007-2010 rmaduro backup all mysql databases
bakdir=/home/backup/backups
user=backup
password=xxxx
#
date=`date -I`
for i in /var/lib/mysql/*/; do
dbname=`basename $i`
mysqldump -u $user --password=$password $dbname > /$bakdir/$dbname-$date.sql
done
#cd to backupdir
cd $bakdir
#give to correct readonlyrights
chmod 600 $bakdir/*.sql
# Remove files older than 3 day from backupdir - don't touch code below!
#
find $bakdir -type f -mtime +3|xargs -i rm -f {}
#
#done
#

all databases, all databases at once, Backup Scripts, full database backup, full MySQL backup, MySQL, separate dump files