Replace local Wordpress database (running inside Homestead) with production database

config.yaml

prod:
  # ssh authentication details
  ssh: user@example.com
  # path to wordpress on the server
  wordpress: path/to/wordpress
  # path to backups folder on the server (must exist)
  backups: backups
  # production url
  url: www.example.com
  
local:
  # path to wordpress
  # Note: must be absolute because when using a tilda ~ the 'cd' command doesn't work.
  # I'm not sure why
  wordpress: /path/to/local/wordpress
  # dev url
  url: example.test

homestead:
  # path to Homestead installation
  path: /path/to/local/homestead
  # path to wordpress inside Homestead i.e. accessible with `vagrant ssh`
  wordpress: code/website

parse_yaml.sh

#!/bin/sh
parse_yaml() {
   local prefix=$2
   local s='[[:space:]]*' w='[a-zA-Z0-9_]*' fs=$(echo @|tr @ '\034')
   sed -ne "s|^\($s\)\($w\)$s:$s\"\(.*\)\"$s\$|\1$fs\2$fs\3|p" \
        -e "s|^\($s\)\($w\)$s:$s\(.*\)$s\$|\1$fs\2$fs\3|p"  $1 |
   awk -F$fs '{
      indent = length($1)/2;
      vname[indent] = $2;
      for (i in vname) {if (i > indent) {delete vname[i]}}
      if (length($3) > 0) {
         vn=""; for (i=0; i<indent; i++) {vn=(vn)(vname[i])("_")}
         printf("%s%s%s=\"%s\"\n", "'$prefix'",vn, $2, $3);
      }
   }'
}

replace-local-db-with-production-db.sh

#!/bin/sh

# Usage:
# bash replace-local-db-with-production-db.sh

# Things to have in place:
# - Can ssh into prod server
# - Prod must have wp-cli installed and aliased to 'wp'
# - Running Homestead locally
# - wp-cli installed inside the Homestead VM

# include parse_yaml function
. parse_yaml.sh

# read yaml file
eval $(parse_yaml config.yaml "config_")

echo $config_prod_ssh
echo $config_prod_wordpress
echo $config_prod_backups
echo $config_prod_url

echo $config_local_wordpress
echo $config_local_url

echo $config_homestead_path
echo $config_homestead_wordpress

# Start in wordpress folder
cd $config_local_wordpress

# ssh into server
ssh $config_prod_ssh
cd $prod-wordpress-path

# trigger a server-side script which runs the db export on the server
mkdir $prod-backups-path
wp db export --add-drop-table $prod-backups-path/db.sql
exit
scp -C sip:$prod-backups-path/db.sql .

# backup local db just in case
cd $config_homestead_path
vagrant ssh
cd $config_homestead_wordpress
wp db export --add-drop-table

# drop database
wp db drop --yes

# create database
wp db create

# import database
wp db query < db.sql

# change site urls
wp search-replace "$config_prod_url" "$config_local_url"

# TODO: anonymize all user details (with a PHP script?)

# Remove prod db
rm db.sql