Alex Blog

I’ve found in Net script, which only backups databases into separate files and modified it. I’ve added gzip compression and backup by weekday. So, you can cron this job daily and get backups for each weekday. It’s very flexible backup algorithm.

Script is below:

#!/bin/bash

# This script backups every MySQL database to its own file

#Some variables you can set how you like
USER='<possible root>'
PASSWORD='<password>'
DAYOFWEEK=`/bin/date +"%w"`
OUTPUTDIR="/usr/backup/mysql/$DAYOFWEEK" # backup dir
MYSQLDUMP='/usr/local/bin/mysqldump'     # path to mysqldump, may be /usr/bin/mysqldump
MYSQL='/usr/local/bin/mysql'             # path to mysql, may be /usr/bin/mysql  

#Clean up any old backups
rm -f $OUTPUTDIR/*

#Get a list of databases names except the system one
databases=`$MYSQL --user=$USER --password=$PASSWORD -e 'SHOW DATABASES;' | grep -Ev '(Database|information_schema)'`

#Dump each database in turn and compress the output with gzip
for db in $databases; do
$MYSQLDUMP --opt --hex-blob --force --user=$USER --password=$PASSWORD $db | gzip > $OUTPUTDIR/$db.gz
done

Download script mysql_backup.sh (800 kB)