The following bash script is written to automate the process of backing up all your various MySQL databases running on either a local or remote MySQL server, using the useful mysqldump utility to do the actual backups.

What the script does is pretty simple to understand really.

First, you define all your server connections. Then it queries the server to find out which databases are currently running in the MySQL Server instance. Armed with this list, it runs through them all (ignoring the ones you specified on the ignore list) and pulls down a mysqldump of each database, gzipping it to its final backup file name.

Simple eh? So let’s see it then:

#!/bin/bash
MyUSER="mysql_user_account"
MyPASS="mysql_user_account_password"
MyHOST="localhost"

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/var/mysql_backups"

# Main directory where backup will be stored
MBD="$DEST/sql_dumps"

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%Y%m%d-%H%M%S")"

# File to store current backup file
FILE=""

# Store list of databases
DBS=""

# DO NOT BACKUP these databases
IGGY="test"

[ ! -d $MBD ] && mkdir -p $MBD || :
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

echo "Launching backup script at $(date)"

for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];    then
        for i in $IGGY        do
            [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ] ; then
	FILE="$MBD/$db.$MyHOST.$NOW.sql.gz"
        # do all inone job in pipe,
        # connect to mysql using mysqldump for select mysql database
        # and pipe it out to gz file in backup dir
	echo "Starting backup process for $db (espreports.com) [$(date)]"
	$MYSQLDUMP --opt --compress --single-transaction -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
	echo "-- Complete ($FILE) [$(date)] --"
    fi
done

echo "Backup script completed execution at $(date)"

And we’re done. Nifty. (And damn useful to boot!)