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=
# Remember that your backup site should use a unique table prefix that won't
# conflict with any of the sites you're backing up
TABLE_PREFIX=wpmdb_backup_

# 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----------------- $(date) | $CURRENT_SITE | Migrate DB Profile: $PROFILE_INDEX -------------------\n\n"

  # 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}' AND table_name NOT LIKE '${TABLE_PREFIX}_%';" > 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