mysqldumper

mysqldumper.sh

#!/bin/bash
# set -x
set -e
#
# Usage: ./mydump.sh -h 127.0.0.1 -u root -p root mydb --skip-table-data-like "sales_%" --skip-table-data-like "quote%" | gzip > /tmp/databasename.sql.gz
#
POSITIONAL=()
MYSQL_IGNORE_TABLE_DATA_LIKE=()
MYSQL_IGNORE_TABLE_LIKE=()
MYSQL_CLI=$(which mysql)
MYSQLDUMP_CLI=$(which mysqldump)
INCLUDE_SCHEMA=1
INCLUDE_TRIGGERS=1
INCLUDE_DATA=1
DRY_RUN=0
while [[ $# -gt 0 ]]
do
    key="$1"
    case $key in
        -x|--debug)
        set -x
        shift # past argument
        ;;

        --dry|--dry-run)
        DRY_RUN=1
        shift # past argument
        ;;

        --dump-schema)
        INCLUDE_SCHEMA=2
        if [[ $INCLUDE_TRIGGERS == 1 ]]; then
            INCLUDE_TRIGGERS=0
        fi
        if [[ $INCLUDE_DATA == 1 ]]; then
            INCLUDE_DATA=0
        fi
        shift # past argument
        ;;

        --dump-triggers)
        INCLUDE_TRIGGERS=2
        if [[ $INCLUDE_SCHEMA == 1 ]]; then
            INCLUDE_SCHEMA=0
        fi
        if [[ $INCLUDE_DATA == 1 ]]; then
            INCLUDE_DATA=0
        fi
        shift # past argument
        ;;

        --dump-data)
        INCLUDE_DATA=2
        if [[ $INCLUDE_SCHEMA == 1 ]]; then
            INCLUDE_SCHEMA=0
        fi
        if [[ $INCLUDE_TRIGGERS == 1 ]]; then
            INCLUDE_TRIGGERS=0
        fi
        shift # past argument
        ;;

        --skip-table-data-like|--ignore-table-data-like)
        MYSQL_IGNORE_TABLE_DATA_LIKE+=(${2//_/\\_})
        shift # past argument
        shift # past value
        ;;

        --skip-table-like|--ignore-table-like)
        MYSQL_IGNORE_TABLE_LIKE+=(${2//_/\\_})
        shift # past argument
        shift # past value
        ;;

        -u|--user)
        MYSQL_USER="-u$2"
        shift # past argument
        shift # past value
        ;;

        -u*)
        MYSQL_USER="-u${key#*u}"
        shift # past argument
        ;;

        -p*)
        MYSQL_PASSWORD="${key#*p}"
        shift # past argument
        ;;

        -p|--password)
        >&2 echo "MySQL Password: "
        read -s MYSQL_PASSWORD
        shift # past argument
        ;;

        -h|--host)
        MYSQL_HOST="-h$2"
        shift # past argument
        shift # past value
        ;;
        -h*)
        MYSQL_HOST="-h${key#*h}"
        shift # past argument
        ;;

        -P|--port)
        MYSQL_PORT="-P$2"
        shift # past argument
        shift # past value
        ;;
        -P*)
        MYSQL_PORT="-P${key#*P}"
        shift # past argument
        ;;
        *)    # unknown option
        POSITIONAL+=("$1") # save it in an array for later
        shift # past argument
        ;;
    esac
done

if [[ ${#POSITIONAL[@]} -gt 0 ]]; then
  MYSQL_DB_NAME=${POSITIONAL[${#POSITIONAL[@]}-1]}
fi

if [[ -z "${MYSQL_DB_NAME}" ]]; then
    echo "Database was not specified!"
    exit 1
fi

export MYSQL_PWD=${MYSQL_PASSWORD-${MYSQL_PWD}}
MYSQL_CMD="${MYSQL_CLI} -N ${MYSQL_HOST} ${MYSQL_PORT} ${MYSQL_USER} ${MYSQL_DB_NAME}"
MYSQLDUMP_CMD="${MYSQLDUMP_CLI} -N ${MYSQL_HOST} ${MYSQL_PORT} ${MYSQL_USER}"
MYSQLDUMP_CMD_EXTRA_ARGS="--no-tablespaces"

if ${MYSQLDUMP_CMD} --help | grep set-gtid-purged > /dev/null; then
    MYSQLDUMP_CMD_EXTRA_ARGS="${MYSQLDUMP_CMD_EXTRA_ARGS} --set-gtid-purged=OFF"
fi

if ${MYSQLDUMP_CMD} --help | grep column-statistics > /dev/null; then
    MYSQLDUMP_CMD_EXTRA_ARGS="${MYSQLDUMP_CMD_EXTRA_ARGS} --column-statistics=0"
fi

IGNORE_TABLES=""
for condition in "${MYSQL_IGNORE_TABLE_LIKE[@]}"
do
    QUERY="show tables like '"${condition}"';"
    LCMD=${MYSQL_CMD}' -e "'${QUERY}'" | grep -v "^\(sales_order_status\|sales_order_status_label\|sales_order_status_state\)$"'
    LCMD_RES=$(bash -c "${LCMD}")
    IGNORE_TABLES=$(printf "${IGNORE_TABLES}\n${LCMD_RES}")
done

IGNORE_TABLE_ARGS=$(echo "${IGNORE_TABLES}" | xargs -I{} printf '\-\-ignore-table '${MYSQL_DB_NAME}'.{} ')

IGNORE_TABLES_DATA=""
for condition in "${MYSQL_IGNORE_TABLE_DATA_LIKE[@]}"
do
    QUERY="show tables like '"${condition}"';"
    LCMD=${MYSQL_CMD}' -e "'${QUERY}'" | grep -v "^\(sales_order_status\|sales_order_status_label\|sales_order_status_state\)$"'
    LCMD_RES=$(bash -c "${LCMD}")
    IGNORE_TABLES_DATA=$(printf "${IGNORE_TABLES_DATA}\n${LCMD_RES}")
done

IGNORE_TABLE_DATA_ARGS=$(echo "${IGNORE_TABLES_DATA}" | xargs -I{} printf '\-\-ignore-table '${MYSQL_DB_NAME}'.{} ')

if [[ $INCLUDE_SCHEMA > 0 ]]; then
    >&2 echo "Dumping schemas..."

    printf "\n--\n-- Dumping schemas\n--\n\n"

    MYSQLCMD="${MYSQLDUMP_CMD} ${MYSQLDUMP_CMD_EXTRA_ARGS} --add-drop-table --no-data --skip-lock-tables --skip-triggers ${IGNORE_TABLE_ARGS} ${MYSQL_DB_NAME}"

    printf "\n--\n-- Executing: ${MYSQLCMD}\n--\n\n"

    if [[ $DRY_RUN == 1 ]]; then
      >&2 echo ${MYSQLCMD}
    else
      bash -c "${MYSQLCMD}" | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/DEFINER=CURRENT_USER \*/' || {
          exit 1
      }
    fi
fi

if [[ $INCLUDE_DATA > 0 ]]; then
    >&2 echo "Dumping data..."

    printf "\n--\n-- Dumping data\n--\n\n"

    MYSQLCMD="${MYSQLDUMP_CMD} ${MYSQLDUMP_CMD_EXTRA_ARGS} --quick --max-allowed-packet=16M --disable-keys --hex-blob --skip-triggers --no-autocommit --no-create-info --insert-ignore --skip-lock-tables --single-transaction ${IGNORE_TABLE_ARGS} ${IGNORE_TABLE_DATA_ARGS} ${MYSQL_DB_NAME}"

    printf "\n--\n-- Execuring: ${MYSQLCMD}\n--\n\n"

    if [[ $DRY_RUN == 1 ]]; then
      >&2 echo ${MYSQLCMD}
    else
      bash -c "${MYSQLCMD}" | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/DEFINER=CURRENT_USER \*/' || {
          exit 1
      }
    fi
fi

if [[ $INCLUDE_TRIGGERS > 0 ]]; then
    >&2 echo "Dumping triggers and routines..."

    printf "\n--\n-- Dumping triggers and routines\n--\n\n"

    MYSQLCMD="${MYSQLDUMP_CMD} ${MYSQLDUMP_CMD_EXTRA_ARGS} --no-data --skip-lock-tables --no-create-info --routines --triggers ${MYSQL_DB_NAME}"

    printf "\n--\n-- Executing: ${MYSQLCMD}\n--\n\n"

    if [[ $DRY_RUN == 1 ]]; then
      >&2 echo ${MYSQLCMD}
    else
      bash -c "${MYSQLCMD}" | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/DEFINER=CURRENT_USER \*/' || {
          exit 1
      }
    fi
fi