Uses n98-magerun over SSH to dump a database and pull to local environment for import

db-dump.sh

#!/usr/bin/env bash
set -euo pipefail
trap 'error "$(printf "Command \`%s\` on line $LINENO failed with exit code $?" "$BASH_COMMAND")"' ERR

## setup functions for use throughout the script
function error {
  >&2 printf "\033[31mERROR\033[0m: $@\n"
}

function :: {
  echo
  echo "==> [$(date +%H:%M:%S)] $@"
}

## find directory above where this script is located following symlinks if neccessary
readonly BASE_DIR="$(
  cd "$(
    dirname "$(
      (readlink "${BASH_SOURCE[0]}" || echo "${BASH_SOURCE[0]}") \
        | sed -e "s#^../#$(dirname "$(dirname "${BASH_SOURCE[0]}")")/#"
    )"
  )/.." >/dev/null \
  && pwd
)"
cd "${BASE_DIR}"

## declare variables for flags and arguments
OPT_HELP=
OPT_VERBOSE=
OPT_TRADE=

## parse arguments
while (( "$#" )); do
  case "$1" in
    -h|--help)
      OPT_HELP=1
      break
      ;;
    -v|--verbose)
      OPT_VERBOSE=1
      shift
      ;;
    --trade)
      OPT_TRADE=1
      shift
      ;;
    --env-prd)
      ## configure parameters specifying where to pull db dump from
      CONNECTION="www-prod@123.123.123.123"
      MAGENTO_DIR="/var/www/prod/current"
      DUMP_PREFIX="magento_prod-$(date +%Y%m%d)"
      shift
      ;;
    --env-stg)
      ## configure parameters specifying where to pull db dump from
      CONNECTION="www-stage@123.123.123.123"
      MAGENTO_DIR="/var/www/stage/current"
      DUMP_PREFIX="magento_stage-$(date +%Y%m%d)"
      shift
      ;;
    --env-dev)
      ## configure parameters specifying where to pull db dump from
      CONNECTION="www-dev@123.123.123.123"
      MAGENTO_DIR="/var/www/dev/current"
      DUMP_PREFIX="magento_dev-$(date +%Y%m%d)"
      shift
      ;;
    *) # unsupported flags
      >&2 printf "\e[01;31mERROR\033[0m: Unsupported argument $1\n"
      exit 1
      ;;
  esac
done

## display command specific usage info if help flag is set
if [[ ${OPT_HELP} ]]; then
  echo -e "$(cat <<-EOT
		\033[33mUsage:\033[0m $0 [--help] [--trade] --env-prd|--env-stg|--env-dev

		\033[33mOptions:\033[0m
		  -h, --help        Display this help menu
		  -v, --verbose     Increases verbosity of output

		  --env-prd         Db dump will be pulled from the prd environment
		  --env-stg         Db dump will be pulled from the stg environment
		  --env-dev         Db dump will be pulled from the dev environment

		  --trade           Downloaded db will include customer/order (aka 'trade') data
		EOT
  )"
  exit 1
fi

## verify user specified one of the --env-* options to configure required settings
if [[ ! ${CONNECTION:-} ]] || [[ ! ${MAGENTO_DIR:-} ]] || [[ ! ${DUMP_PREFIX:-} ]]; then
  >&2 printf "\e[01;31mERROR\033[0m: Missing required option to specify source. See --help for details.\n"
  exit 1
fi

## enable command tracing
if [[ ${OPT_VERBOSE} ]]; then
  set -x
fi

## configure parameters used for executing the db dump operation
N98MAGERUN="${N98MAGERUN:-"/usr/local/bin/n98-magerun"}"
BACKUPS_DIR="${MAGENTO_DIR}/var/backups"

## configure tables and table-groups to strip from the dumped database
STRIP_FLAG=(
  @log @search
  adminnotification_inbox 
  cordial_sync_log
  magento_logging_event_changes
  persistent_session
  report_event
  report_viewed_product_index
  sales_bestsellers_aggregated_daily
  sales_bestsellers_aggregated_monthly
  tax_order_aggregated_created
  tax_order_aggregated_updated
)

## strip tables and table-groups with trade data removed unless --trade is specified
if [[ ${OPT_TRADE} ]]; then
  DUMP_PREFIX+="-trade"
else
  STRIP_FLAG+=(
    @trade
    amasty_order_attribute_entity
    amasty_order_attribute_values_cl
    amasty_order_attribute_entity_int
    amasty_order_attribute_entity_text
    amasty_order_attribute_entity_varchar
    amasty_order_attribute_entity_decimal
    amasty_order_attribute_entity_datetime
    amasty_order_attribute_grid_flat
    amasty_orderattr_order_attribute_value
  )
fi

## print info about operation about to be performed
:: Dumping from "${CONNECTION}":"${MAGENTO_DIR}" with exclusions
printf " - %s\n" "${STRIP_FLAG[@]}"
echo

## dump database on remote endpoint if has not already been dumped today
cat <<-EOT | ssh -T -p "${SSH_PORT:-22}" "${CONNECTION}"
  if [[ ! -f ${BACKUPS_DIR}/${DUMP_PREFIX}.sql.gz ]]; then
    ${N98MAGERUN} --root-dir="${MAGENTO_DIR}" db:dump \
      -c gz --strip="${STRIP_FLAG[@]}" -- ${BACKUPS_DIR}/${DUMP_PREFIX}.sql.gz
  else
    printf "\033[33mSkipping db:dump\033[0m: %s already exists.\n" "${BACKUPS_DIR}/${DUMP_PREFIX}.sql.gz"
  fi
EOT

:: Downloading "${BACKUPS_DIR}/${DUMP_PREFIX}.sql.gz"
scp -P "${SSH_PORT:-22}" "${CONNECTION}":${BACKUPS_DIR}/${DUMP_PREFIX}.sql.gz ./backfill/

:: Downloading complete
printf "\033[33mTo import locally run the following command:\033[0m\n\n%s\n\n" \
  "warden bootstrap --db-dump ./backfill/${DUMP_PREFIX}.sql.gz"