Bash script for SQL search-replace while respecting serialised data in WordPress

Been really into using AI to write my automated scripts recently – this is one of them. Place it in a folder along with an SQL file (or use an absolute path) – it’ll download a WordPress core into the same directory and will use WP CLI to search-replace old site URL’s while rewriting serialised data.

Tested on macOS only.

#!/bin/bash
# WordPress-safe SQL Search-Replace (macOS)
# Caches WP core locally (./wp-core-cache/)
# Usage: ./wp-sql-replace.sh input.sql oldsite.com newsite.com [table_prefix]
# Example: ./wp-sql-replace.sh backup.sql oldsite.com newsite.com abc_

set -euo pipefail

INPUT_FILE_RAW="${1:-}"
OLD_URL="${2:-}"
NEW_URL="${3:-}"
PREFIX_PARAM="${4:-}"

if [ -z "$INPUT_FILE_RAW" ] || [ -z "$OLD_URL" ] || [ -z "$NEW_URL" ]; then
  echo "Usage: $0 input.sql oldsite.com newsite.com [table_prefix]"
  exit 1
fi

# --- resolve script dir and relative file path ---
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

# If INPUT_FILE_RAW is absolute, use it; otherwise look for it in the same dir as the script
if [[ "$INPUT_FILE_RAW" = /* ]]; then
  INPUT_FILE="$INPUT_FILE_RAW"
else
  INPUT_FILE="$SCRIPT_DIR/$INPUT_FILE_RAW"
fi

if [ ! -f "$INPUT_FILE" ]; then
  echo "❌ SQL file not found: $INPUT_FILE"
  exit 1
fi

# --- determine table prefix (optional argument) ---
if [ -n "${PREFIX_PARAM:-}" ]; then
  PREFIX_BASE="${PREFIX_PARAM%_}"
else
  PREFIX_BASE="wp"
fi
TABLE_PREFIX="${PREFIX_BASE}_"
echo "Using table prefix: '$TABLE_PREFIX'"

# --- paths ---
WP_CACHE_DIR="$SCRIPT_DIR/wp-core-cache"

# --- ensure WP core cached locally ---
if [ ! -d "$WP_CACHE_DIR" ]; then
  echo "🔽 Downloading WordPress core to local cache ($WP_CACHE_DIR)..."
  mkdir -p "$WP_CACHE_DIR"
  cd "$WP_CACHE_DIR"
  wp core download --quiet
else
  echo "✅ Using cached WordPress core at $WP_CACHE_DIR"
fi

# --- prepare temp working copy ---
TMP_DIR=$(mktemp -d)
cp -r "$WP_CACHE_DIR/"* "$TMP_DIR/"
cd "$TMP_DIR"

DB_NAME="wp_tmp_$(date +%s)"
MYSQL_USER="${MYSQL_USER:-root}"
MYSQL_PWD_FLAG=""
if [ -n "${MYSQL_PWD:-}" ]; then
  MYSQL_PWD_FLAG="-p${MYSQL_PWD}"
fi

echo "Creating temporary database: $DB_NAME..."
if ! mysql -u "$MYSQL_USER" $MYSQL_PWD_FLAG -e "CREATE DATABASE \`$DB_NAME\`;" 2>/dev/null; then
  echo "❌ MySQL connection failed. Check credentials (MYSQL_USER, MYSQL_PWD)."
  exit 1
fi

# --- create wp-config.php manually to ensure correct prefix ---
cat > wp-config.php <<PHP
<?php
define('DB_NAME', '$DB_NAME');
define('DB_USER', '$MYSQL_USER');
define('DB_PASSWORD', '${MYSQL_PWD:-}');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8');
define('DB_COLLATE', '');
\$table_prefix = '${TABLE_PREFIX}';
define('WP_DEBUG', false);
if ( !defined('ABSPATH') )
  define('ABSPATH', __DIR__ . '/');
require_once ABSPATH . 'wp-settings.php';
PHP

# --- import SQL dump ---
echo "📥 Importing SQL dump from: $INPUT_FILE"
wp db import "$INPUT_FILE"

# --- verify the table prefix exists in DB (optional warning only) ---
echo "🔍 Checking for tables with prefix '${TABLE_PREFIX}'..."
TABLE_COUNT=$(mysql -u "$MYSQL_USER" $MYSQL_PWD_FLAG -N -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$DB_NAME' AND table_name LIKE '${TABLE_PREFIX}%';")
if [ "$TABLE_COUNT" -eq 0 ]; then
  echo "⚠️  Warning: No tables found with prefix '${TABLE_PREFIX}' — continuing anyway (WP-CLI may detect automatically)."
else
  echo "✅ Found $TABLE_COUNT tables with prefix '${TABLE_PREFIX}'"
fi

# --- run search-replace ---
echo "🔄 Running search-replace (serialisation-safe)..."
wp search-replace "$OLD_URL" "$NEW_URL" --all-tables --precise --quiet

# --- export updated SQL ---
OUTPUT_FILE="$SCRIPT_DIR/$(basename "$INPUT_FILE" .sql)-replaced.sql"
wp db export "$OUTPUT_FILE" --quiet
echo "✅ Done! Updated file saved to: $OUTPUT_FILE"

# --- cleanup ---
echo "🧹 Cleaning up temp DB and files..."
mysql -u "$MYSQL_USER" $MYSQL_PWD_FLAG -e "DROP DATABASE \`$DB_NAME\`;" 2>/dev/null || true
cd - >/dev/null
rm -rf "$TMP_DIR"

echo "✨ All done!"

Usage

chmod +x wp-sql-replace.sh
./wp-sql-replace.sh dump.sql oldsite.com newsite.com abc_