Simple MySQL backup script

Post by **^rooker** » Mon Aug 13, 2007 10:23 am

I know, I know... There are already thousands and thousands of mysql backup scripts out there. So why do I post my own?

Because most of the other ready-available scripts are too complex and thus error prone if you only need a straightforward mysqldump.

You just supply it with "username:password@dbname" strings and it dumps and zips the database contents into a file.

Code: Select all

#!/bin/bash
# @author: ^Rooker
# @date: 13.Aug.2007
# @license: GPL
# @description:
#   This script should backup our local MySQL databases.

# @usage:
#   $WIKI_DBS must contain login credentials for each database to be backed up.
#        The syntax is: username:password@dbname


WIKI_DBS="user1:pass1@dbname1 user2:pass2@dbname2";

DEST_DIR="/mount/storage/backup";        # Destination directory for backup files
TIMESTAMP=`date +%Y%m%d_%H%M`   # Current timestamp. Will be used in the filename.
SUFFIX="sql.bz2"                                     # File suffix of backup dumps

for WIKI_DB in $WIKI_DBS; do
        DB_NAME=`echo $WIKI_DB | sed -e "s/.*@//g"`
        echo "Backing up \"$DB_NAME\"...   "

        LOGIN=${WIKI_DB/"@"/" "}             # the database name has no prefix and only needs a space.
        LOGIN=${LOGIN/":"/" --password="}  # : marks the beginning of the password
        LOGIN="--user="$LOGIN                  # The first word is the username, so prefix it with --user.
        #echo "$LOGIN"

        FILENAME=$DEST_DIR"/"$TIMESTAMP"-"$DB_NAME"."$SUFFIX
        mysqldump $LOGIN | bzip2 -c > $FILENAME
done