Dump tables, triggers and routines to separate files

dump_db.sh

#!/bin/bash

# Source: https://gist.github.com/simonds/9abadf569a2f01707d3158f086e90442/raw
# Pull latest: curl https://gist.github.com/simonds/9abadf569a2f01707d3158f086e90442/raw > dump_db.sh

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

DO_TABLES=true;
DO_TRIGGERS=true;
DO_ROUTINES=true;
SHOW_OUTPUT=false;
MAIN_BACKUP_DIR="";
USERNAME="";
PASSWORD="";
HOST="";
DATABASE="";

usage()
{
    echo ""
    echo "Usage: $0 -f <foldername> -u <username> -p <password> -h <host> [-d <database>] [-b] [-t] [-r] [-v]"
    echo ""
    echo "Optional parameters"
    echo "  -d export only <database>"
    echo "  -b don't export tables"
    echo "  -t don't export triggers"
    echo "  -r don't export routines"
    echo "  -v use verbose mode"
    echo ""
}

while getopts ":f:u:p:h:d:btrv" opt; do
    case ${opt} in
        f) MAIN_BACKUP_DIR=$OPTARG;;
        u) USERNAME=$OPTARG;;
        p) PASSWORD=$OPTARG;;
        h) HOST=$OPTARG;;
        d) DATABASE=$OPTARG;;
        b) DO_TABLES=false;;
        t) DO_TRIGGERS=false;;
        r) DO_ROUTINES=false;;
        v) SHOW_OUTPUT=true;;
        \?) echo "Invalid option: -$OPTARG" >&2;;
        :)
            echo "Option -$OPTARG requires an argument." >&2
            exit 1;;
    esac
done

# Validate arguments
if [ "$#" -lt 4 ]; then
    usage
    exit 1;
fi

if [ "${MAIN_BACKUP_DIR}" = "" ] || [ "${USERNAME}" = "" ] || [ "${PASSWORD}" = "" ] || [ "${HOST}" = "" ]; then
    echo "All required paramaters must be set!"
    usage
    exit 1;
fi

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

MYSQL_CONN="-u${USERNAME} -h${HOST} -p${PASSWORD}"

# Attempt to create dump directory if not present
DUMP_DIR="${MAIN_BACKUP_DIR}"

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|performance_schema|mysql|sys|phpmyadmin$"

if [ "${DATABASE}" = "" ]; then
    # Obtain a list of databases
    DBS="$(${MYSQL} -u${USERNAME} -p${PASSWORD} -h${HOST} -Bse 'show databases')"
else
    DBS=${DATABASE}
fi


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

    if [ "${DO_TABLES}" = true ]; then
        HAS_TABLES=false

        for TABLE in $(${MYSQL} -u${USERNAME} -p${PASSWORD} -h${HOST} ${DB} -e 'show tables' | egrep -v 'Tables_in_' ); do
            if [ ${HAS_TABLES} = false ]; then
                if [ "${TABLE}" = "" ]; then
                    echo "No tables found in db: ${DB}"
                else
                    if [ ! -d "${DUMP_DIR}/${DB}/tables" ]; then
                        mkdir "${DUMP_DIR}/${DB}/tables"
                    fi
                    HAS_TABLES=true
                fi
            fi
            if [ ${HAS_TABLES} = true ]; then
                FILE="${DUMP_DIR}/${DB}/tables/${TABLE}.sql"
                if ${SHOW_OUTPUT}; then echo "Dumping ${DB}.${TABLE} to ${FILE}"; fi;
                ${MYSQLDUMP} --no-data -u${USERNAME} -p${PASSWORD} -h${HOST} --opt -Q ${DB} ${TABLE} > ${FILE}
            fi
        done
    fi;

    # Dump triggers
    if [ "${DO_TRIGGERS}" = true ]; then
        SQLSTMT="SELECT COUNT(1) FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = '${DB}'"
        TRIGCOUNT=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
        if [ ${TRIGCOUNT} -ne 0 ]
        then
            if [ ! -d "${DUMP_DIR}/${DB}/triggers" ]; then
                mkdir "${DUMP_DIR}/${DB}/triggers"
            fi
            SPLIST=""
            for DBTR in `mysql ${MYSQL_CONN} -ANe"SELECT CONCAT(TRIGGER_SCHEMA,'.',TRIGGER_NAME) FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = '${DB}'"`
            do
                DB=`echo "${DBTR}" | sed 's/\./ /' | awk '{print $1}'`
                TR=`echo "${DBTR}" | sed 's/\./ /' | awk '{print $2}'`
                SQLSTMT=`echo "SHOW CREATE TRIGGER ${DBTR}\G"`
                TRFILE=${DUMP_DIR}/${DB}/triggers/${TR}.sql
                TRTEMP=${DUMP_DIR}/${DB}/triggers/${TR}.tmp
                if ${SHOW_OUTPUT}; then echo Dumping ${DBTR} into ${TRFILE}; fi
                mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${TRFILE}

                LINECOUNT=`wc -l < ${TRFILE}`
                (( LINECOUNT -= 3 ))
                tail -${LINECOUNT} < ${TRFILE} > ${TRTEMP}

                LINECOUNT=`wc -l < ${TRTEMP}`
                (( LINECOUNT -= 3 ))
                head -${LINECOUNT} < ${TRTEMP} > ${TRFILE}
                rm -f ${TRTEMP}
            done
        fi;
    fi;

    # Dump routines
    if [ "${DO_ROUTINES}" = true ]; then
        SQLSTMT="SELECT COUNT(1) FROM mysql.proc WHERE db = '${DB}'"
        PROCCOUNT=`mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" | awk '{print $1}'`
        if [ ${PROCCOUNT} -ne 0 ]
        then
            if [ ! -d "${DUMP_DIR}/${DB}/routines" ]; then
                mkdir "${DUMP_DIR}/${DB}/routines"
            fi
            SPLIST=""
            for DBSP in `mysql ${MYSQL_CONN} -ANe"SELECT CONCAT(type,'@',db,'.',name) FROM mysql.proc WHERE db = '${DB}'"`
            do
                SP=`echo "${DBSP}" | sed 's/@/ /' | sed 's/\./ /' | awk '{print $3}'`
                SQLSTMT=`echo "SHOW CREATE ${DBSP}\G" | sed 's/@/ /'`
                SPFILE=${DUMP_DIR}/${DB}/routines/${SP}.sql
                SPTEMP=${DUMP_DIR}/${DB}/routines/${SP}.tmp
                if ${SHOW_OUTPUT}; then echo Dumping ${DB}.${SP} into ${SPFILE}; fi
                mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > ${SPFILE}

                LINECOUNT=`wc -l < ${SPFILE}`
                (( LINECOUNT -= 3 ))
                tail -${LINECOUNT} < ${SPFILE} > ${SPTEMP}

                LINECOUNT=`wc -l < ${SPTEMP}`
                (( LINECOUNT -= 3 ))
                head -${LINECOUNT} < ${SPTEMP} > ${SPFILE}
                rm -f ${SPTEMP}
            done
        fi;
    fi;

done