Monday, November 17, 2008

MySQL backup script using mysqldump

This script is to backup MySQL Databases using mysqldump. Its designed for dialy backups and will dump every database into a separate file for easy restore.


#!/bin/sh

######### SETTINGS #########
# Backup Directory
BACKUPDIR="/backups"

# Database Connection
HOST="localhost"
USER="root"
PASS=""
####### END SETTINGS #######

# Get current Date from System
DATE=`date +%y%m%d`

# Create daily backup directory
mkdir $BACKUPDIR/$DATE > /dev/null 2>&1

# Support for empty passwords
if [ "$PASS" != "" ]; then
USEPASS="-p$PASS"
fi

# Get Database List
databases=`mysql -h $HOST -u $USER $USEPASS -Bse "SHOW DATABASES;"`

# Dump each database into a separate file for easy restore
for db in $databases; do
echo "Processing Database: $db"
mysqldump -h $HOST -u $USER $USEPASS --force --opt $db > "$BACKUPDIR/$DATE/$db.dump"
done

# gzip dump files to save space
gzip -9 $BACKUPDIR/$DATE/*