Import MySQL dump from a folder containing one file.sql.gz per table

mysql_import_dump.sh

#!/bin/bash

# Import a dump folder to a database
# Will remove the table, view and trigger just before importing each table


# Where is located your dumps.
# The folder must contain one file per table and must use the .sql.gz extension
DUMP_FOLDER="$1"

# MySQL information
MY_HOST="$2"
MY_USER="$3"
MY_DB="$4"
# For security reasons, you should not specify this in the command line (if not specified, the script will prompt for it)
MY_PASS="$5"

usage()
{
    echo $(basename $0) FOLDER MYSQL_HOST MYSQL_USER MYSQL_DATABASE [MYSQL_PASSWORD]
}

if [ ! -d "${DUMP_FOLDER}" ]
then
    usage
    echo >2 "The directory provided does not exists"
    exit 1
fi

if [ -z "${MY_HOST}" ] || [ -z "${MY_USER}" ] || [ -z "${MY_DB}" ]
then
    usage
    echo >2 "One parameter is missing "
    exit 1
fi


if [ -z "${MY_PASS}" ]
then
    read -p "MySQL password: " -s MY_PASS
fi

MYSQL_OPT="-h ${MY_HOST} -u ${MY_USER} -p$MY_PASS ${MY_DB}"

# get max table length (string)
MAX_TABLE_LEN=$(mysql ${MYSQL_OPT} -e "SHOW TABLES" | awk "{ print $1 }" | grep -v '^Tables' | wc -L)

for file in `ls -1 ${DUMP_FOLDER}/*.sql.gz`
do
    table=$(basename $file)
    table=${table%.sql.gz}

    printf "Table %-*s " ${MAX_TABLE_LEN} $table

    printf "DROP ... "
    mysql ${MYSQL_OPT} -e "SET FOREIGN_KEY_CHECKS = 0; \
        DROP TABLE IF EXISTS $table; \
        DROP VIEW IF EXISTS $table; \
        SET FOREIGN_KEY_CHECKS = 1"

    printf "TRIGGERS ... "
    TRIGGERS=$(mysql ${MYSQL_OPT} -e "SHOW TRIGGERS IN ${MY_DB} WHERE \`Table\` = '${table}'\G" | grep "Trigger: " | awk '{ print $2 }')

    if [ -n "${TRIGGERS}" ]
    then
        for trigger in ${TRIGGERS}
        do
            mysql ${MYSQL_OPT} -e "DROP TRIGGER IF EXISTS $trigger"
        done
    fi

    printf "IMPORT ... "
    zcat $file | mysql ${MYSQL_OPT}

    printf "DONE\n"
done