Roots Database Sync Scripts

Overview

Roots-based WordPress projects (Bedrock/Sage/Trellis) require special database synchronization scripts to sync data between environments (production → staging → development).

---

New Sync Script (Recommended)

Note

Recommended: Use the new database-sync script for better performance and reduced server load.

About

The new sync script is available at: wordpress-sync-script repository

Advantages over old script: - Uses database backups instead of live database connections - Much lower load on production server - Faster and more reliable - Automatic search/replace - ElasticSearch integration - Automatic cleanup

Setup

Step 1: Create Scripts Directory

cd site
mkdir -p scripts
cd scripts

Step 2: Create Sync Script

touch database-sync.sh

Step 3: Copy Code

Copy the code from wordpress-sync-script repository into database-sync.sh.

Step 4: Configure Script

Update these variables in the script:

USER="your-project"
PRODSITE="https://example.com"

Step 5: Make Executable

chmod 744 database-sync.sh

Step 6: Run Sync

./database-sync.sh

What It Does

The script automatically:

1. Downloads the most recent database backup from server
2. Empties your local database
3. Imports the production database
4. Removes Gravity Forms entries (to avoid test submissions)
5. Adds a local admin user (admin / admin)
6. Search/replace all URLs from https://production.com to https://production.lndo.site
7. Configures ElasticSearch (if used in project, otherwise skips)
8. Deletes the downloaded database file

Syncing Images

Warning

Images still need to be synced with the old script (for now).

To sync uploads/images, use:

./sync.sh production development --local --skip-db

This syncs only the uploads/ directory without touching the database.

---

Old Sync Script (Legacy)

Warning

Deprecated: Avoid using this script in new projects. It puts significant load on the live database during sync.

Why It's Deprecated

• Creates heavy load on production database
• Slower than backup-based sync
• Can cause production performance issues during sync
• Direct database connection is less reliable

Setup (For Reference)

Step 1: Create Scripts Directory

cd site
mkdir -p scripts
cd scripts

Step 2: Create Sync Script

touch sync.sh

Step 3: Add Script Code

#!/bin/bash

# Syncing Trellis & Bedrock-based WordPress environments with WP-CLI aliases
# Version 1.2.0
# Copyright (c) Ben Word

DEVDIR="web/app/uploads/"
DEVSITE="http://PROJECTNAME.lndo.site"

STAGINGDIR="PROJECTNAME_staging@web01-exonet.lemone.network:/home/PROJECTNAME_staging/public/shared/uploads/"
STAGINGSITE="https://staging.PROJECTNAME.com"

PRODDIR="PROJECTNAME@web01-exonet.lemone.network:/home/PROJECTNAME/public/shared/uploads/"
PRODSITE="https://www.PROJECTNAME.com"

SLACKLINK=""
SLACKCHANNEL=""

LOCAL=false
SKIP_DB=false
SKIP_ASSETS=false
POSITIONAL_ARGS=()

while [[ $# -gt 0 ]]; do
  case $1 in
    --skip-db)
      SKIP_DB=true
      shift
      ;;
    --skip-assets)
      SKIP_ASSETS=true
      shift
      ;;
    --local)
      LOCAL=true
      shift
      ;;
    --*)
      echo "Unknown option $1"
      exit 1
      ;;
    *)
      POSITIONAL_ARGS+=("$1")
      shift
      ;;
  esac
done

set -- "${POSITIONAL_ARGS[@]}"

if [ $# != 2 ]
then
  echo "Usage: $0 [[--skip-db] [--skip-assets] [--local]] [ENV_FROM] [ENV_TO]"
exit;
fi

FROM=$1
TO=$2

bold=$(tput bold)
normal=$(tput sgr0)

case "$1-$2" in
  staging-development)    DIR="down ⬇️ "          FROMSITE=$STAGINGSITE; FROMDIR=$STAGINGDIR; TOSITE=$DEVSITE;  TODIR=$DEVDIR; ;;
  production-development) DIR="down ⬇️ "          FROMSITE=$PRODSITE; FROMDIR=$PRODDIR; TOSITE=$DEVSITE;  TODIR=$DEVDIR; ;;
  production-staging)     DIR="horizontally ↔️ "; FROMSITE=$PRODSITE; FROMDIR=$PRODDIR; TOSITE=$STAGINGSITE; TODIR=$STAGINGDIR; ;;
  *) echo "usage: $0 [[--skip-db] [--skip-assets] [--local]] staging development | production development | production staging" && exit 1 ;;
esac

if [ "$SKIP_DB" = false ]
then
  DB_MESSAGE=" - ${bold}reset the $TO database${normal} ($TOSITE)"
fi

if [ "$SKIP_ASSETS" = false ]
then
  ASSETS_MESSAGE=" - sync ${bold}$DIR${normal} from $FROM ($FROMSITE)?"
fi

if [ "$SKIP_DB" = true ] && [ "$SKIP_ASSETS" = true ]
then
  echo "Nothing to synchronize."
  exit;
fi

echo
echo "Would you really like to "
echo $DB_MESSAGE
echo $ASSETS_MESSAGE
read -r -p " [y/N] " response

if [[ "$response" =~ ^([yY][eE][sS]|[yY])$ ]]; then
  # Change to site directory
  cd ../ &&
  echo

  # Make sure both environments are available before we continue
  availfrom() {
    local AVAILFROM

    if [[ "$LOCAL" = true && $FROM == "development" ]]; then
      AVAILFROM=$(lando wp option get home 2>&1)
    else
      AVAILFROM=$(wp "@$FROM" option get home 2>&1)
    fi
    if [[ $AVAILFROM == *"Error"* ]]; then
      echo "❌  Unable to connect to $FROM"
      exit 1
    else
      echo "✅  Able to connect to $FROM"
    fi
  };
  availfrom

  availto() {
    local AVAILTO
    if [[ "$LOCAL" = true && $TO == "development" ]]; then
      AVAILTO=$(lando wp option get home 2>&1)
    else
      AVAILTO=$(wp "@$TO" option get home 2>&1)
    fi

    if [[ $AVAILTO == *"Error"* ]]; then
      echo "❌  Unable to connect to $TO $AVAILTO"
      exit 1
    else
      echo "✅  Able to connect to $TO"
    fi
  };
  availto

  if [ "$SKIP_DB" = false ]
  then
  echo "Syncing database..."
    DUMP_FILE="db_dump.sql"

    # Export/import database, run search & replace
    if [[ "$LOCAL" = true && $TO == "development" ]]; then
      lando wp db export --default-character-set=utf8mb4 &&
      lando wp db reset --yes &&
      wp "@$FROM" db export - > $DUMP_FILE &&
      lando wp db import $DUMP_FILE &&
      lando wp search-replace "$FROMSITE" "$TOSITE" --all-tables-with-prefix
    elif [[ "$LOCAL" = true && $FROM == "development" ]]; then
      wp "@$TO" db export --default-character-set=utf8mb4 &&
      wp "@$TO" db reset --yes &&
      lando wp db export --default-character-set=utf8mb4 - | wp "@$TO" db import - &&
      wp "@$TO" search-replace "$FROMSITE" "$TOSITE" --all-tables-with-prefix
    else
      wp "@$TO" db export --default-character-set=utf8mb4 &&
      wp "@$TO" db reset --yes &&
      wp "@$FROM" db export --default-character-set=utf8mb4 - | wp "@$TO" db import - &&
      wp "@$TO" search-replace "$FROMSITE" "$TOSITE" --all-tables-with-prefix
    fi
  fi

  if [ "$SKIP_ASSETS" = false ]
  then
  echo "Syncing assets..."
    # Sync uploads directory
    chmod -R 755 web/app/uploads/ &&
    if [[ $DIR == "horizontally"* ]]; then
      [[ $FROMDIR =~ ^(.*): ]] && FROMHOST=${BASH_REMATCH[1]}
      [[ $FROMDIR =~ ^(.*):(.*)$ ]] && FROMDIR=${BASH_REMATCH[2]}
      [[ $TODIR =~ ^(.*): ]] && TOHOST=${BASH_REMATCH[1]}
      [[ $TODIR =~ ^(.*):(.*)$ ]] && TODIR=${BASH_REMATCH[2]}

      ssh -o ForwardAgent=yes $FROMHOST "rsync -aze 'ssh -o StrictHostKeyChecking=no' --progress $FROMDIR $TOHOST:$TODIR"
    else
      rsync -az --progress "$FROMDIR" "$TODIR"
    fi
  fi

  # Slack notification when sync direction is up or horizontal
  if [[ $DIR != "down"* ]]; then
    USER="$(git config user.name)"
    curl -X POST -H "Content-type: application/json" --data "{\"attachments\":[{\"fallback\": \"\",\"color\":\"#36a64f\",\"text\":\"🔄 Sync from ${FROMSITE} to ${TOSITE} by ${USER} complete \"}],\"channel\":\"${SLACKCHANNEL}\"}" ${SLACKLINK}
  fi

  echo -e "\n🔄  Sync from $FROM to $TO complete.\n\n    ${bold}$TOSITE${normal}\n"
fi

Step 4: Find and Replace

Press Cmd + F and search for PROJECTNAME, replace with your actual project name (e.g., doldersum, hetccv).

Step 5: Verify Configuration

Check that these fields are correct: - DEVSITE - PRODDIR - STAGINGSITE - PRODSITE

Step 6: Configure WP-CLI Aliases

Open wp-cli.yml and add:

@production:
  ssh: PROJECTNAME@web01-exonet.lemone.network:/home/PROJECTNAME/public/current

@staging:
  ssh: PROJECTNAME_staging@web01-exonet.lemone.network:/home/PROJECTNAME_staging/public/current

Replace PROJECTNAME with your actual project name.

Step 7: Make Executable and Run

chmod 744 sync.sh
./sync.sh production development --local

Usage Options

Sync database only:

./sync.sh production development --local --skip-assets

Sync uploads only:

./sync.sh production development --local --skip-db

Sync from staging:

./sync.sh staging development --local

Sync to staging (from production):

./sync.sh production staging

---

Troubleshooting

SSH Key Issues

If you encounter SSH authentication issues:

1. Ensure your SSH key is loaded:

   ssh-add -l
   

2. Add your key if needed:

   ssh-add ~/.ssh/id_ed25519
   

3. Test server connection:

   ssh user@web01-exonet.lemone.network
   

See: SSH Key Management

WP-CLI Not Found

Ensure WP-CLI is accessible:

# Local (via Lando)
lando wp --info

# Remote (via SSH)
ssh user@host "wp --info"

Permission Denied on Uploads

chmod -R 755 web/app/uploads/

Database Import Fails

Check character set:

lando wp db export --default-character-set=utf8mb4

Check database credentials in .env:

DB_NAME=database_name
DB_USER=database_user
DB_PASSWORD=database_password

Search/Replace Not Working

Manually run search/replace:

lando wp search-replace 'https://production.com' 'https://project.lndo.site' --all-tables-with-prefix

---

Best Practices

1. Always test locally first - Sync to development, test, then staging
2. Backup before sync - Create database backup before syncing
3. Use skip flags - Skip database or assets if not needed
4. Schedule regular syncs - Keep development data fresh (weekly recommended)
5. Clean up test data - Remove test submissions, drafts, spam before syncing up
6. Check disk space - Ensure enough space for database dump and uploads

---

See Also

• Exonet Hosting - Exonet deployment and server management
• Kinsta Hosting - Kinsta database migration
• WordPress Roots Stack - Bedrock, Sage, Trellis
• Lando - Local development environment
• SSH Keys - SSH key management