Skip to content

Backup all mysql databases to separate dump files

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
#

Published inBash Scripts

2 Comments

  1. Excellent. Exactly what I wanted.

    I had to to change the –password to --password and it worked wonderfully.

    Also, I removed the / in the “> /$bakdir/$dbname-$date.sql”
    to allow relative paths to be used.
    bakdir=./backups

  2. @Prad, fixed the code.

Leave a Reply

Your email address will not be published. Required fields are marked *