Ubuntu: A Bash Script to Backup All MySQL Databases Running on a Server CodeUnit 28 JUL 2010

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!)

Related Posts:

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.

  • This algorithm is really helpful for MYSQL server. It is written to automate process of backing up all mysql databases, tables and data.

  • C

    Seems that the iggy array doesn’t work on my machine. It only skips the first item in the array and the rest are still backed up.

    Any thoughts on how to have it skip the whole array?

    Thanks

  • joseph

    Hello,

    I get this error:

    ./mysql_dump_script.sh: line 45: syntax error near unexpected token `[‘
    ./mysql_dump_script.sh: line 45: ` [ “$db” == “$i” ] && skipdb=1 || :’

    would you please have a look. Thanks

  • There is a missing line break before the “do” in line 44