How To Automate Safe Replication of a Production WordPress Site to a Local Node

by

A precise, scriptable workflow to mirror a live WordPress instance to a local network node for testing and development without leaking secrets or damaging production.

Quick start

  1. Enable SSH key access from the workstation to the production host. No passwords.
  2. Configure local MySQL authentication via ~/.my.cnf or a MySQL login-path.
  3. Install WP-CLI and verify it can operate the local WordPress docroot as www-data.
  4. Set the environment variables in the script header to match your environment.
  5. Run the script. It will pull a production DB dump, rsync files into a staging area, sync into the local docroot with correct ownership and permissions, reset tables, import the dump, and rewrite URLs to your local domain.

Why this design

  • No root SSH over the network. All network operations are unprivileged. Elevated actions are local only.
  • Local first. Database dump is streamed to the workstation and compressed locally. No lingering dumps on the server.
  • Two phase file sync. Remote to user staging, then to local docroot. Prevents accidental overwrite of local configuration and enforces clean permissions.
  • Idempotent. Safe to re run. The script preserves wp-config.php, resets only tables, and re applies URL localisation deterministically.
  • Minimal secrets surface. No credentials embedded. Local MySQL credentials are loaded from client config. Remote dump uses server side defaults or a login path.

Architecture at a glance

# Production host
mysqldump | gzip  ──>  Workstation: $HOME/sql/<name>_YYYY-MM-DD_HHMM.sql.gz

# Production docroot
rsync over SSH  ──>  $HOME/.staging/wordpress  ──>  /var/www/wordpress (local docroot, www-data owned)

# WP-CLI on workstation (as www-data)
db reset → db import → set siteurl/home → search-replace prod→local → flush caches and rewrites

Prerequisites

  • Production: SSH key access for a non root user. Ability to run mysqldump via server side defaults or a login path.
  • Local: Linux, ssh, rsync, gzip, mysql, wp, and flock. Local WordPress docroot is owned by www-data.
  • Version alignment: Keep PHP and MySQL close between production and local to avoid serialisation or plugin surprises.

Setup and configuration

/var/www/wordpress        # Local WordPress docroot (www-data owned)
$HOME/.staging/wordpress  # User owned staging area
$HOME/sql                  # Compressed DB dumps (.sql.gz)
$HOME/logs                 # Script logs
$HOME/locks                # Lock files

Verify WP-CLI can manage the docroot as www-data:

sudo -u www-data wp --path=/var/www/wordpress core version

Replication workflow

  1. Pre checks: verify commands, SSH connectivity, client auth, paths, and acquire a lock.
  2. DB dump: run mysqldump remotely, stream over SSH, compress to $HOME/sql/<db>_timestamp.sql.gz.
  3. File sync: rsync remote docroot to staging with safe excludes. Then rsync staging to local docroot with ownership and mode fixes. Preserve local wp-config.php.
  4. Reset and import: reset tables via WP-CLI, import fresh dump, set siteurl and home to local URL.
  5. Localise and harden: optional theme install, disable all plugins for first boot, flush rewrites and caches, perform broad URL search replace of production hosts to local.

Sanitised automation script (public version)

Secrets removed by design. Replace placeholders with your values via environment variables. Do not publish internal hostnames or user names.

#!/usr/bin/env bash
# File: ~/.local/bin/pull_kevwells
# Purpose: Pull WordPress (files + DB) from production → local workstation.
# Policy: No root SSH. Network ops as user; sudo only for LOCAL stage→docroot copy.
# Guarantees: preserves local wp-config.php; runs WP-CLI as www-data; auto URL rewrite to LOCAL_URL.

set -Eeuo pipefail
export LANG=C.UTF-8
export LC_ALL=C.UTF-8
export HOME="${HOME:-/home/you}"
export PATH="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/bin:$HOME/.local/bin"

# ----- Remote selection (primary with fallback; override via REMOTE_SSH) -----
if [ -z "${REMOTE_SSH:-}" ]; then
  SSH_PROBE_OPTS="-o BatchMode=yes -o ConnectTimeout=5"
  if ssh $SSH_PROBE_OPTS "${REMOTE_SSH_PRIMARY:-user@prod-host}" true 2>/dev/null; then
    REMOTE_SSH="${REMOTE_SSH_PRIMARY:-user@prod-host}"
  else
    REMOTE_SSH="${REMOTE_SSH_FALLBACK:-user@prod-host-backup}"
  fi
fi

# ----- Core config (override with env as needed) -----
: "${REMOTE_WP:=/var/www/wordpress}"         # Production docroot
: "${REMOTE_DB_NAME:=wordpress}"             # Production DB name (server-side auth/login-path expected)

: "${LOCAL_WP_ROOT:=/var/www/wordpress}"     # Local docroot (must match local wp-config.php)
: "${LOCAL_DB_NAME:=wordpress}"              # For logging only; WP-CLI reads local wp-config.php

: "${SSH_KEY:=$HOME/.ssh/id_ed25519}"        # Or id_rsa if you insist
: "${SSH_OPTS:="-o BatchMode=yes -i $SSH_KEY -o ConnectTimeout=20 -o ServerAliveInterval=15 -o ServerAliveCountMax=3"}"

: "${STAGE_ROOT:=$HOME/.staging/wordpress}"  # User-owned staging area
: "${LOCAL_SQL_DIR:=$HOME/sql}"
: "${LOG_DIR:=$HOME/logs}"
: "${LOCK_DIR:=$HOME/locks}"
: "${LOG_FILE:=$LOG_DIR/pull_wordpress_local_clone.log}"

# Local URL and production domain (used for search-replace)
: "${LOCAL_URL:=http://wp.local.test}"
: "${PROD_DOMAIN:=example.com}"              # Your public domain (no scheme, no path)
: "${FALLBACK_THEME:=generatepress}"         # Optional: ensure a sane theme exists

# rsync options
RSYNC_BASE_OPTS=( -aHAXx --delete --numeric-ids --inplace --info=stats1,progress2 )
RSYNC_REMOTE_EXCLUDES=(
  --exclude 'wp-config.php'
  --exclude 'wp-content/cache/'
  --exclude 'wp-content/backups/'
  --exclude 'wp-content/mu-plugins/replica-host-rewrite.php'
  --exclude 'info.php'
)
RSYNC_LOCAL_FIXUPS=(
  --no-perms --no-owner --no-group
  --chown=www-data:www-data
  --chmod=Dug=rwx,Do=,Fug=rw,Fo=
  --exclude 'wp-config.php'
)

# ----- Helpers -----
ts()  { date +"%Y-%m-%d %H:%M:%S"; }
log() { echo "[$(ts)] $*" | tee -a "$LOG_FILE"; }
die() { log "ERROR: $*"; exit "${2:-1}"; }
need(){ command -v "$1" >/dev/null 2>&1 || die "Missing command: $1" 10; }
wpw(){ sudo -u www-data wp --path="$LOCAL_WP_ROOT" "$@"; }  # WP-CLI as www-data

prechecks(){
  mkdir -p "$STAGE_ROOT" "$LOCAL_WP_ROOT" "$LOCAL_SQL_DIR" "$LOG_DIR" "$LOCK_DIR"
  : > "$LOG_FILE" || true
  for cmd in ssh rsync gzip gunzip zcat mysql wp flock; do need "$cmd"; done
  ssh $SSH_OPTS "$REMOTE_SSH" true 2>/dev/null || die "SSH to $REMOTE_SSH failed (keys/BatchMode)."
  mysql -e "SELECT 1" >/dev/null 2>&1 || die "Local MySQL cannot auth via ~/.my.cnf or login-path."
}

main(){
  prechecks
  exec {lockfd}>"$LOCK_DIR/pull_kevwells.lock"
  flock -n "$lockfd" || die "Another pull is already running (lock active)."

  TS="$(date +%F_%H%M)"
  DUMP_PATH="${LOCAL_SQL_DIR}/${REMOTE_DB_NAME}_${TS}.sql.gz"

  log "START pull (remote=${REMOTE_SSH}, remote_db=${REMOTE_DB_NAME}, local_url=${LOCAL_URL})"

  # [1/7] Remote DB dump → local compressed file
  log "[1/7] Dumping remote DB → $DUMP_PATH"
  ssh $SSH_OPTS "$REMOTE_SSH" \
    "mysqldump --single-transaction --hex-blob --default-character-set=utf8mb4 '$REMOTE_DB_NAME'" \
    | gzip -c > "$DUMP_PATH"

  # [2/7] Rsync remote → staging (user-space)
  log "[2/7] Rsync remote → staging: '$REMOTE_SSH:$REMOTE_WP/' → '$STAGE_ROOT/'"
  rsync "${RSYNC_BASE_OPTS[@]}" "${RSYNC_REMOTE_EXCLUDES[@]}" \
        -e "ssh $SSH_OPTS" \
        "$REMOTE_SSH:$REMOTE_WP/" \
        "$STAGE_ROOT/"

  # [3/7] Sanity check
  [ -d "$STAGE_ROOT/wp-content" ] || die "Staging incomplete: '$STAGE_ROOT/wp-content' missing."

  # [4/7] Safety backup of local wp-config.php (if present)
  LOCAL_WPCFG="$LOCAL_WP_ROOT/wp-config.php"
  LOCAL_WPCFG_BAK="$LOCAL_WP_ROOT/.wp-config.php.localbak"
  if [ -f "$LOCAL_WPCFG" ]; then
    sudo cp -a "$LOCAL_WPCFG" "$LOCAL_WPCFG_BAK"
  fi

  # [5/7] Stage → local docroot (preserve local wp-config.php; fix ownership/perms)
  log "[5/7] Rsync staging → local docroot: '$STAGE_ROOT/' → '$LOCAL_WP_ROOT/'"
  sudo rsync "${RSYNC_BASE_OPTS[@]}" "${RSYNC_LOCAL_FIXUPS[@]}" \
        "$STAGE_ROOT/" \
        "$LOCAL_WP_ROOT/"

  # Post-rsync guard: ensure wp-config.php exists; restore backup if needed
  if [ ! -f "$LOCAL_WPCFG" ] && [ -f "$LOCAL_WPCFG_BAK" ]; then
    log "WARNING: wp-config.php missing after rsync; restoring local backup."
    sudo mv -f "$LOCAL_WPCFG_BAK" "$LOCAL_WPCFG"
    sudo chown www-data:www-data "$LOCAL_WPCFG"
    sudo chmod 640 "$LOCAL_WPCFG"
  fi
  [ -f "$LOCAL_WPCFG" ] || die "Local '$LOCAL_WP_ROOT/wp-config.php' missing and no backup available."

  # [6/7] Reset tables only (no DROP/CREATE DATABASE)
  log "[6/7] Resetting tables via WP-CLI"
  wpw db reset --yes

  # [7/7] Import dump and localise
  log "[7/7] Importing DB and localising site"
  TMP_SQL="$(mktemp)"
  gunzip -c "$DUMP_PATH" > "$TMP_SQL"

  IMPORT_SQL="$LOCAL_WP_ROOT/_import.sql"
  sudo install -o www-data -g www-data -m 0640 "$TMP_SQL" "$IMPORT_SQL"
  rm -f "$TMP_SQL"

  wpw db import "$IMPORT_SQL"
  sudo rm -f "$IMPORT_SQL"

  # Core URL settings
  wpw option update home    "$LOCAL_URL"
  wpw option update siteurl "$LOCAL_URL"

  # Theme & rewrites hardening for first boot
  wpw theme is-installed "$FALLBACK_THEME" >/dev/null 2>&1 || wpw theme install "$FALLBACK_THEME"
  wpw option update active_plugins "" >/dev/null 2>&1 || true
  wpw rewrite flush --hard >/dev/null 2>&1 || true

  # Replace production URLs broadly
  LOCAL_BASE="${LOCAL_URL%/}"
  PROD_HOSTS=(
    "https://${PROD_DOMAIN}"
    "http://${PROD_DOMAIN}"
    "//${PROD_DOMAIN}"
    "https://www.${PROD_DOMAIN}"
    "http://www.${PROD_DOMAIN}"
    "//www.${PROD_DOMAIN}"
  )
  for OLD in "${PROD_HOSTS[@]}"; do
    wpw search-replace "$OLD" "$LOCAL_BASE" \
      --all-tables --precise --report-changed-only --skip-columns=guid >/dev/null
  done

  # Clear caches and flush rewrites again
  wpw transient delete --all >/dev/null 2>&1 || true
  wpw cache     flush   >/dev/null 2>&1 || true
  wpw rewrite   flush --hard >/dev/null 2>&1 || true

  log "Post-localise URLs:"
  wpw --skip-plugins --skip-themes option get siteurl | sed "s/^/  siteurl = /"
  wpw --skip-plugins --skip-themes option get home    | sed "s/^/  home    = /"

  log "COMPLETE. Files: $LOCAL_WP_ROOT | DB: $LOCAL_DB_NAME | Visit: $LOCAL_URL"
}

trap 'die "Trapped error on line $LINENO." 99' ERR
main

Environment variables to set (examples):

export REMOTE_SSH_PRIMARY="deploy@example.com"
export REMOTE_SSH_FALLBACK="deploy@bastion.example.net"
export PROD_DOMAIN="example.com"
export LOCAL_URL="http://wp.local.test"
# Optional overrides:
# export LOCAL_WP_ROOT="/var/www/wordpress"
# export REMOTE_WP="/var/www/wordpress"
# export REMOTE_DB_NAME="wordpress"

Operational playbook

  • First run: execute interactively and watch for permission prompts and WP-CLI output.
  • Regular use: schedule before development sessions. Prefer a lock guarded cron entry.
# /etc/cron.d/wp_local_refresh  - runs at 06:30 on weekdays
30 6 * * 1-5 youruser PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/bin:$HOME/.local/bin \
  PROD_DOMAIN=example.com LOCAL_URL=http://wp.local.test \
  REMOTE_SSH_PRIMARY=deploy@example.com REMOTE_SSH_FALLBACK=deploy@bastion.example.net \
  /home/youruser/.local/bin/pull_kevwells >> /home/youruser/logs/pull_cron.log 2>&1

Rollback to a previous dump:

gunzip -c ~/sql/wordpress_2025-09-19_0630.sql.gz | \
  sudo -u www-data wp --path=/var/www/wordpress db import -
sudo -u www-data wp --path=/var/www/wordpress option update home    http://wp.local.test
sudo -u www-data wp --path=/var/www/wordpress option update siteurl http://wp.local.test

Housekeeping for old dumps:

find "$HOME/sql" -type f -name '*.sql.gz' -mtime +14 -delete

Security notes and threat model

  • No secrets in the script. Local DB auth via ~/.my.cnf or login path. Remote dump uses server side defaults or a read only DB user.
  • SSH keys only. Disable password authentication. Consider forced command on the server side key if you want to be strict.
  • Least privilege. Network phase is unprivileged. sudo is used only to write to the local docroot and run WP-CLI as www-data.
  • Pull only. This is designed for a developer workstation, not production.
  • Preserve local configuration. The script never overwrites local wp-config.php. It backs up and restores if needed.
  • Do not publish internals. Replace example.com, SSH targets, and paths with site neutral values in public material.

Troubleshooting

Symptom Likely cause Remedy
SSH to host failed (keys or BatchMode) No key auth or wrong key path Fix SSH_KEY, ensure the public key is installed on the server, validate sshd_config.
Local MySQL cannot auth via ~/.my.cnf Missing client credentials Create ~/.my.cnf or set a login path via mysql_config_editor.
Error establishing database connection Local wp-config.php points at wrong DB or socket Correct DB settings, confirm local MySQL is running, and check socket paths.
Broken CSS or media URLs not fully replaced or stale caches Re run search replace, clear transients and plugin caches, flush rewrites.
Permission denied on upload Docroot not owned by www-data Re run the script to enforce ownership and modes or fix manually.
Plugin specific fatal errors Plugin incompatible with local PHP or MySQL Align versions or keep plugins disabled for the first boot and enable selectively.

Extensions and variants

  • Staging server: use the same script to refresh a staging host by adjusting LOCAL_URL and LOCAL_WP_ROOT.
  • Selective sync: add further rsync excludes for large media if you want a smaller, faster clone for daily work.
  • Systemd timers: prefer systemd timers over cron on modern distros for stronger logging and reliability.
  • Read only DB user: constrain the production dump account to SELECT, SHOW VIEW, and routine read privileges on the WordPress schema only.
  • Integrity checks: add wp core verify-checksums post import to catch corrupted core files quickly.

This article is part of the Lab Projects series and is intended for public technical readership. Implementation details that could compromise security have been removed or generalised.