Dump tables, triggers and routines to separate files

dump_db.sh

#!/bin/bash

# MySQL executables
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"

# Other executables
GZIP="$(which gzip)"

# Validate arguments
if [ "$#" -ne 4 ]; then
    echo "Usage: $0 <BASE_BACKUP_DIRECTORY> <USERNAME> <PASSWORD> <HOST>"
    exit 1;
fi

MAIN_BACKUP_DIR="$1"
if [ ! -d $MAIN_BACKUP_DIR ]; then
    echo "$MAIN_BACKUP_DIR does not exist"
        exit 1;
fi

USERNAME=$2
PASSWORD=$3
HOST=$4

# Attempt to create dump directory if not present
NOW="$(date +"%Y-%m-%d")"
DUMP_DIR="$MAIN_BACKUP_DIR/$NOW"

if [ ! -d $DUMP_DIR ]; then
        mkdir $DUMP_DIR
        if [ ! -d $DUMP_DIR ]; then
                echo "Failed to create backup dir $DUMP_DIR"
                exit 1;
        fi
fi

# Proceed with dump

# Do not dump config databases
IGNORE_DBS="^test|information_schema|mysql|phpmyadmin$"

# Obtain a list of databases
DBS="$($MYSQL -u$USERNAME -p$PASSWORD -h$HOST -Bse 'show databases')"

for DB in $DBS
        do

        # Do not dump IGNORE_DBS
        [[ "$DB" =~ $IGNORE_DBS ]] && continue

        # Dump each table in DB into a separate folder
        if [ ! -d "$DUMP_DIR/$DB" ]; then
                mkdir "$DUMP_DIR/$DB"
        fi

        for TABLE in $($MYSQL -u$USERNAME -p$PASSWORD -h$HOST $DB -e 'show tables' | egrep -v 'Tables_in_' ); do
                FILE="$DUMP_DIR/$DB/$TABLE.gz"
                echo "Dumping $DB.$TABLE" # to $FILE"
                $MYSQLDUMP -u$USERNAME -p$PASSWORD -h$HOST --opt -Q $DB $TABLE | $GZIP -9 > $FILE
        done

        if [ "$TABLE" = "" ]; then
                echo "No tables found in db: $DB"
        fi

        # Dump triggers
        FILE="$DUMP_DIR/$DB/triggers.gz"
        $MYSQLDUMP -u$USERNAME -h$HOST -p$PASSWORD --triggers \
                --no-create-info --no-data --no-create-db --skip-opt $DB | $GZIP -9 > $FILE

        # Dump routines
        FILE="$DUMP_DIR/$DB/routines.gz"
        $MYSQLDUMP -u$USERNAME -h$HOST -p$PASSWORD --routines \
                --no-create-info --no-data --no-create-db --skip-opt $DB | $GZIP -9 > $FILE

done