Skip to content

Instantly share code, notes, and snippets.

@JRGould
Last active October 29, 2022 00:09
Show Gist options
  • Save JRGould/91c832abb78a3241ae4d to your computer and use it in GitHub Desktop.
Save JRGould/91c832abb78a3241ae4d to your computer and use it in GitHub Desktop.
WP Migrate DB Pro CLI Backup Script
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment