sync-db.sh

#!/usr/bin/env bash

shopt -s nullglob
set -e

FROM=$1
TO=$2
SEARCH=$3
REPLACE=$4

NUM_ARGS=4

show_usage() {
  echo "Usage: sync <fromserver> <toserver> <replacefrom> <replaceto>

Examples:
  sync site.prod site.dev 'dev.site.com' 'site.dev'
  sync site.stage site.dev 'dev.site.com' 'site.dev'

  NEVER push to Production !!
"
}
[[ $# -ne $NUM_ARGS || $1 = -h ]] && { show_usage; exit 0; }

DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
BACKUP_DIR=$(realpath "$DIR/../../db_backups/")/
DATE=$(date +%F_%T)
REMOTE_DIR="/srv/www/site/current"
DUMP_CMD="wp db export --skip-extended-insert --skip-lock-tables --skip-dump-date"
REPLACE_CMD="wp search-replace --precise --recurse-objects"

read -p "CONFIRM:
    BACKUP_DIR: $BACKUP_DIR
    - Delete transients on $TO
    - Backup $TO to $DATE-$TO.sql and nuke
    - Delete transients on $FROM
    - Backup $FROM to $DATE-$FROM.sql and pipe to import on $TO
    - Replace '$SEARCH' with '$REPLACE' on $TO
"

echo "# Deleting transients from $TO#"
ssh "$TO" bash -c "'
cd $REMOTE_DIR
wp transient delete-all
'"

echo "# Backup $TO to $DATE-$TO #"
ssh "$TO" bash -c "'
cd $REMOTE_DIR
$DUMP_CMD -
'" > "$BACKUP_DIR$DATE-$TO.sql"

echo "# Drop all tables in $TO #"
ssh "$TO" bash -c "'
cd $REMOTE_DIR
wp db reset --yes
'"

echo
echo "---"
echo

echo "# Deleting transients from $FROM #"
ssh "$FROM" bash -c "'
cd $REMOTE_DIR
wp transient delete-all
'"

echo "# Backup from "$FROM" and pipe directly to import on $TO #"
ssh "$FROM" bash -c "'
set -e
cd $REMOTE_DIR
$DUMP_CMD -
'" | tee "$BACKUP_DIR$DATE-$FROM.sql" | ssh $TO bash -c "'
cd $REMOTE_DIR
wp db import -
'"

echo
echo "---"
echo

echo "# Replacing $SEARCH with $REPLACE on $TO #"
ssh "$TO" bash -c "'
cd $REMOTE_DIR
$REPLACE_CMD $SEARCH $REPLACE
'"

echo
echo "---"
echo

echo "# Backup $TO to $DATE-$TO-processed (for comparison)#"
ssh "$TO" bash -c "'
cd $REMOTE_DIR
$DUMP_CMD -
'" > "$BACKUP_DIR$DATE-$TO-processed.sql"

read -p "Open Comparison?"

bcompare "$BACKUP_DIR$DATE-$TO-processed.sql" "$BACKUP_DIR$DATE-$FROM.sql"