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:


# 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

# Main directory where backup will be stored

# Get hostname

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

# File to store current backup file

# Store list of databases

# DO NOT BACKUP these databases

[ ! -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
    if [ "$IGGY" != "" ];    then
        for i in $IGGY        do
            [ "$db" == "$i" ] && skipdb=1 || :

    if [ "$skipdb" == "-1" ] ; then
        # 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)] --"

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

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

Related Posts:

About Craig Lotter

South African software architect and developer at Touchwork. Husband to a cupcake baker and father to two little girls. 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?


  • joseph


    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