WP Migrate DB Pro CLI Backup Script

wpmdbpro-cli-remote-backup-script.sh

#!/bin/bash
#
# Declare SITES as an associative array
declare -A SITES
# Format: SITES[WPMDB profile number]=backup_filename_base
SITES[1]=remote_backup_com
#SITES[2]=another_site_to_backup
#SITES[3]=my_other_blog

## These variables will be specific to the WordPress
## install that you'll be using to backup your remote sites
# The absolute path to the root of your site
SITE_PATH=/var/www/html/
# The folder to store backup .tar.gz files in
BACKUP_DIR=${SITE_PATH}/_backups

# MySQL connection information, should be the same as what's in your wp_config.
MYSQL_USER=
MYSQL_PASS=
MYSQL_DB=

# These should be the same on most servers
PATH_TO_WP_CMD=/usr/local/bin/wp
UPLOADS_FOLDER_CHMOD=775
APACHE_USER=www-data

###################################### STOP EDITING #####################################

# Suppress warnings from WP CLI about not running in a terminal
export TERM=xterm-color

for i in "${!SITES[@]}"
do

  PROFILE_INDEX=$i
  CURRENT_SITE=${SITES[$i]}
  DATE=$(date +"%Y%m%d%H%M")

  # Print the date/time on a newline
  printf "\n----------------- %s | $CURRENT_SITE | Migrate DB Profile: $PROFILE_INDEX -------------------\n\n" "$(date)"

  # Create backup of pristine db
  echo "Backing up db..."
  mysqldump -u"${MYSQL_USER}" -p"${MYSQL_PASS}" "${MYSQL_DB}" > wpmdb_backup_dump.sql

  # The migration command
  echo "Performing migration via WPMDBPro CLI"
  echo "-------------------------------------------"
  ${PATH_TO_WP_CMD} migratedb profile "${PROFILE_INDEX}" --path="${SITE_PATH}" --allow-root
  echo "-------------------------------------------"

  BACKUP_NAME=$CURRENT_SITE-$DATE
  WORKING_DIR=$BACKUP_DIR/$BACKUP_NAME

  mkdir -p "${WORKING_DIR}"

  echo "Getting tables to save for backup of ${CURRENT_SITE}"
  mysql -u"${MYSQL_USER}" -p"${MYSQL_PASS}" -N information_schema -e "SELECT table_name FROM tables WHERE table_schema = '${MYSQL_DB}';" > tables-to-export.txt

  # Dump tables that aren't for the backup install
  echo "Backing up tables: $(tr '\n' ' ' < tables-to-export.txt)"

  mysqldump -u"${MYSQL_USER}" -p"${MYSQL_PASS}" "${MYSQL_DB}" "$(cat tables-to-export.txt)" > "${WORKING_DIR}"/database.sql

  echo "Moving Uploads Folder"
  rm -rf "$SITE_PATH"/wp-content/uploads/wp-migrate-db/
  mv "$SITE_PATH"/wp-content/uploads/ "$WORKING_DIR"/

  echo "TARing Backup"
  tar -zcf "${BACKUP_DIR}"/"${BACKUP_NAME}".tar.gz -C "${WORKING_DIR}"/ .
  rm -r "${WORKING_DIR}"

  # Drop all tables in db

  echo "Cleaning up..."
  mysqldump -u"${MYSQL_USER}" -p"${MYSQL_PASS}" --add-drop-table --no-data "${MYSQL_DB}" | grep ^DROP | mysql -u"${MYSQL_USER}" -p"${MYSQL_PASS}" "${MYSQL_DB}"

  # Restore original DB
  mysql -u"${MYSQL_USER}" -p"${MYSQL_PASS}" "${MYSQL_DB}" < wpmdb_backup_dump.sql
  rm wpmdb_backup_dump.sql
  rm tables-to-export.txt

  #Restore uploads folder
  mkdir -m ${UPLOADS_FOLDER_CHMOD} "${SITE_PATH}"/wp-content/uploads/
  chown ${APACHE_USER} "${SITE_PATH}"/wp-content/uploads/

done

This forked version updates legacy syntax in the original gist.