All Linux HowTo's

Backup all MySQL databases into their own backup file

This is an example Bash script that will backup the MySQL databases on the local system. It will backup all databases except those called “Database”, “information_schema” and “mysql”. In other words, it will backup ‘your’ databases. Just remove those from the “egrep” command if you want all databases (including the mysql special database). Each database found on the server is put into its own backup file. This script will keep the last 7 backups. That is, it creates each backup file with the day of the week in its name. Therefore it will replace the previous weeks backups as it goes.

#!/bin/bash

## To backup all found MySQL databases on the local system.
## By AGIX.

# Variables
DBUSER="root"
DBPASS="password"
BACKUPTODIR="/root"
MAILTO="support@agix.local"
THISHOST=`hostname`

# Get the list of databases to backup
DBLIST=`echo "show databases;" | mysql --password=$DBPASS -u $DBUSER | egrep -v '^Database$|^information_schema$|^mysql$'`

for DBLOOP in `echo $DBLIST`
do
        # Start the backup
        mysqldump -u $DBUSER --password=$DBPASS $DBLOOP > $BACKUPTODIR/mysql-$DBLOOP-$THISHOST-$(date +%w).sql
done

# Send a notification email
ls -lh $BACKUPTODIR/*.sql > /tmp/email-notification.txt
mail -s "Backup Notification $THISHOST" $MAILTO < /tmp/email-notification.txt

Similar Posts: