Skip to main content

Database Search & Replace

Overview

wp search-replace is one of WP-CLI's most powerful commands. It safely finds and replaces text across your entire database—handling serialized data correctly, which manual SQL cannot do. Essential for migrations, URL changes, and bulk content updates.

Understanding Search & Replace​

WordPress stores data in serialized PHP format, which breaks if you use simple SQL REPLACE() functions. WP-CLI's search-replace:

  • Handles serialized data correctly (arrays, objects)
  • Updates string lengths in serialized data
  • Prevents database corruption
  • Works across all tables or specific ones
  • Supports dry-run for safe previews

Why WP-CLI Search-Replace is Essential​

The Serialization Problem​

WordPress stores settings, widgets, and options as serialized PHP:

a:3:{s:4:"name";s:8:"John Doe";s:3:"url";s:19:"http://example.com";s:5:"email";s:15:"john@example.com";}

If you change http://example.com to https://example.com with SQL:

  • The string length s:19 becomes incorrect (should be s:20)
  • WordPress can't unserialize the data
  • Settings and widgets break

WP-CLI search-replace fixes this automatically.

Basic Search & Replace​

Simple Replacement​

# Replace across all WordPress tables
wp search-replace 'old-text' 'new-text'

# Replace URLs
wp search-replace 'http://oldsite.com' 'https://newsite.com'

What it does:

  • Searches all WordPress tables (with your table prefix)
  • Finds all instances of the old text
  • Replaces with new text
  • Updates serialized data correctly
  • Shows summary of changes

Dry Run (Preview Changes)​

# Preview changes without applying them
wp search-replace 'http://oldsite.com' 'https://newsite.com' --dry-run

Output example:

+---------------------+----------------------+---------------+
| Table               | Column               | Replacements  |
+---------------------+----------------------+---------------+
| wp_options          | option_value         | 15            |
| wp_posts            | post_content         | 42            |
| wp_posts            | guid                 | 38            |
| wp_postmeta         | meta_value           | 8             |
+---------------------+----------------------+---------------+
Success: 103 replacements to be made.
Always Dry Run First

Run with --dry-run first to verify what will change before applying.

Common Use Cases​

Domain Migration​

# HTTP to HTTPS
wp search-replace 'http://example.com' 'https://example.com' --dry-run
wp search-replace 'http://example.com' 'https://example.com'

# Change domain
wp search-replace 'oldsite.com' 'newsite.com' --dry-run
wp search-replace 'oldsite.com' 'newsite.com'

# Complete migration (both protocol and domain)
wp search-replace 'http://staging.example.com' 'https://example.com' --dry-run
wp search-replace 'http://staging.example.com' 'https://example.com'

Local to Production Migration​

# Local development to production
wp search-replace 'http://localhost:8080' 'https://example.com' --dry-run
wp search-replace 'http://localhost:8080' 'https://example.com'

# Update file paths if needed
wp search-replace '/Users/dev/sites/mysite' '/var/www/html' --dry-run
wp search-replace '/Users/dev/sites/mysite' '/var/www/html'

Staging to Production​

# Update URLs
wp search-replace 'https://staging.example.com' 'https://example.com' --dry-run
wp search-replace 'https://staging.example.com' 'https://example.com'

# Update email addresses (if using staging-specific emails)
wp search-replace 'staging@example.com' 'admin@example.com' --dry-run
wp search-replace 'staging@example.com' 'admin@example.com'

Advanced Options​

Precise Mode​

# Use precise mode for complex serialized data
wp search-replace 'old' 'new' --precise

What it does:

  • More thorough scanning
  • Slower but more accurate
  • Recommended for critical migrations

When to use:

  • Complex serialized data
  • When standard mode misses replacements
  • Production migrations

Specific Tables​

# Replace only in specific tables
wp search-replace 'old' 'new' wp_posts wp_postmeta

# Replace in posts content only
wp search-replace 'old-shortcode' 'new-shortcode' wp_posts

# Replace in options table
wp search-replace 'old-setting' 'new-setting' wp_options

Include/Exclude Columns​

# Replace only in specific columns
wp search-replace 'CompanyX' 'CompanyY' --include-columns=post_content,post_title

# Skip specific columns
wp search-replace 'text' 'newtext' --skip-columns=guid

# Skip GUID column (recommended - don't change post GUIDs)
wp search-replace 'http://old.com' 'https://new.com' --skip-columns=guid
GUID Column

Generally, avoid changing the guid column in wp_posts. Use --skip-columns=guid to preserve it.

All Tables (Including Non-WordPress)​

# Search all tables in database (not just WordPress tables)
wp search-replace 'old' 'new' --all-tables

# Useful for plugin-specific tables
wp search-replace 'dev' 'prod' --all-tables --dry-run

Export Changes​

# Export SQL of changes
wp search-replace 'old' 'new' --export=changes.sql

# Review changes before applying
cat changes.sql

# Apply changes
wp db import changes.sql

Multisite Search & Replace​

Network-Wide Replacement​

# Replace across all sites in network
wp search-replace 'http://oldnetwork.com' 'https://newnetwork.com' --network

# Dry run for network
wp search-replace 'old' 'new' --network --dry-run

Specific Subsite​

# Replace on specific subsite
wp search-replace 'old' 'new' --url=subsite.example.com

# Migrate specific subsite
wp search-replace 'http://old.subsite.com' 'https://new.subsite.com' --url=subsite.example.com

Real-World Scenarios​

Scenario 1: Complete Site Migration​

#!/bin/bash
# complete-migration.sh

# Configuration
OLD_URL="http://staging.example.com"
NEW_URL="https://example.com"

echo "Starting site migration..."

# 1. Backup database
echo "Creating backup..."
wp db export pre-migration-$(date +%Y%m%d-%H%M%S).sql

# 2. Dry run to preview changes
echo "Preview of changes:"
wp search-replace "$OLD_URL" "$NEW_URL" --dry-run --precise

# 3. Confirm before proceeding
read -p "Proceed with migration? (y/n) " -n 1 -r
echo
if [[ ! $REPLY =~ ^[Yy]$ ]]; then
    echo "Migration cancelled."
    exit 1
fi

# 4. Perform replacement
echo "Performing replacement..."
wp search-replace "$OLD_URL" "$NEW_URL" --precise --skip-columns=guid

# 5. Update home and siteurl options (double-check)
wp option update home "$NEW_URL"
wp option update siteurl "$NEW_URL"

# 6. Flush cache
wp cache flush
wp rewrite flush

# 7. Verify
echo "Verifying migration..."
wp option get home
wp option get siteurl

echo "Migration complete!"

Scenario 2: Bulk Content Update​

#!/bin/bash
# update-branding.sh

# Replace company name across all content
OLD_BRAND="ACME Corporation"
NEW_BRAND="ACME Industries"

echo "Updating branding from '$OLD_BRAND' to '$NEW_BRAND'..."

# Dry run
wp search-replace "$OLD_BRAND" "$NEW_BRAND" --dry-run --include-columns=post_content,post_title

# Confirm
read -p "Apply changes? (y/n) " -n 1 -r
echo
if [[ $REPLY =~ ^[Yy]$ ]]; then
    wp search-replace "$OLD_BRAND" "$NEW_BRAND" --include-columns=post_content,post_title
    echo "Branding updated successfully!"
fi

Scenario 3: Fix Broken Serialized Data​

#!/bin/bash
# fix-serialized-data.sh

# After manual database edits, serialized data may be broken
# Use search-replace to fix it

echo "Fixing serialized data..."

# Replace with itself using precise mode to fix serialization
wp search-replace 'http://example.com' 'http://example.com' --precise

echo "Serialized data repaired!"

Scenario 4: Update Shortcodes​

# Replace old shortcode with new one
wp search-replace '[old_shortcode]' '[new_shortcode]' --include-columns=post_content --dry-run
wp search-replace '[old_shortcode]' '[new_shortcode]' --include-columns=post_content

# Replace shortcode with parameters
wp search-replace '[gallery id="old"]' '[gallery id="new"]' --include-columns=post_content

Scenario 5: Clean Up After Plugin Removal​

# Remove plugin-specific shortcodes after uninstalling
wp search-replace '[plugin_shortcode' '' --include-columns=post_content --dry-run
wp search-replace '[plugin_shortcode' '' --include-columns=post_content

# Remove plugin settings from options
wp search-replace 'old_plugin_setting' '' wp_options --dry-run

Advanced Techniques​

Case-Sensitive Replacement​

# Case-sensitive search (default)
wp search-replace 'WordPress' 'WordPress CMS'

# Case-insensitive search
wp search-replace 'wordpress' 'WordPress CMS' --precise

Regular Expressions​

# WP-CLI search-replace doesn't support regex directly
# Use wp db query for regex replacements

wp db query "
  UPDATE wp_posts 
  SET post_content = REGEXP_REPLACE(post_content, 'pattern', 'replacement')
  WHERE post_content REGEXP 'pattern';
"
Regex with Serialized Data

Direct SQL regex won't handle serialized data correctly. Use WP-CLI search-replace when possible.

Multiple Replacements​

#!/bin/bash
# multiple-replacements.sh

# Array of replacements
declare -A replacements=(
    ["http://oldsite.com"]="https://newsite.com"
    ["old-email@example.com"]="new-email@example.com"
    ["Old Company"]="New Company"
)

# Backup first
wp db export before-replacements.sql

# Perform each replacement
for old in "${!replacements[@]}"; do
    new="${replacements[$old]}"
    echo "Replacing '$old' with '$new'..."
    wp search-replace "$old" "$new" --precise
done

echo "All replacements complete!"

Best Practices​

1. Always Backup First​

# Create timestamped backup
wp db export backup-$(date +%Y%m%d-%H%M%S).sql

# Then perform search-replace
wp search-replace 'old' 'new'

2. Use Dry Run​

# Always preview changes first
wp search-replace 'old' 'new' --dry-run

# Review output, then apply
wp search-replace 'old' 'new'

3. Skip GUID Column​

# Don't change post GUIDs (WordPress uses them for identification)
wp search-replace 'http://old.com' 'https://new.com' --skip-columns=guid

4. Use Precise Mode for Production​

# More accurate for critical migrations
wp search-replace 'old' 'new' --precise

5. Clear Caches After Replacement​

# After search-replace, clear all caches
wp cache flush
wp rewrite flush

# Clear object cache if using Redis/Memcached
wp cache flush --redis

6. Verify Critical Settings​

# After migration, verify URLs
wp option get home
wp option get siteurl

# Check sample post
wp post get 1 --field=guid

Troubleshooting​

No Replacements Found​

# Check if text exists
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_content LIKE '%searchtext%';"

# Try without quotes
wp search-replace searchtext newtext

# Check table prefix
wp db prefix

Replacements Not Working​

# Use precise mode
wp search-replace 'old' 'new' --precise

# Check specific table
wp search-replace 'old' 'new' wp_options --dry-run

# Verify database connection
wp db check

Site Breaks After Replacement​

# Restore from backup immediately
wp db import backup-before-replacement.sql

# Clear caches
wp cache flush

# Check what changed
wp search-replace 'new' 'old' --dry-run

Quick Reference​

Essential Commands​

# Basic replacement
wp search-replace 'old' 'new'                          # Replace across all tables
wp search-replace 'old' 'new' --dry-run                # Preview only
wp search-replace 'old' 'new' --precise                # More accurate

# Specific tables
wp search-replace 'old' 'new' wp_posts wp_postmeta     # Specific tables only

# Column control
wp search-replace 'old' 'new' --include-columns=post_content  # Specific columns
wp search-replace 'old' 'new' --skip-columns=guid      # Skip columns

# All tables
wp search-replace 'old' 'new' --all-tables             # Include non-WP tables

# Multisite
wp search-replace 'old' 'new' --network                # All sites
wp search-replace 'old' 'new' --url=site.com           # Specific subsite

# Export
wp search-replace 'old' 'new' --export=changes.sql     # Export as SQL

Common Workflows​

# Safe migration workflow
wp db export backup.sql
wp search-replace 'http://old.com' 'https://new.com' --dry-run
wp search-replace 'http://old.com' 'https://new.com' --precise --skip-columns=guid
wp cache flush

# Quick URL update
wp search-replace 'http://' 'https://' --dry-run
wp search-replace 'http://' 'https://'

Comparison: WP-CLI vs Plugins​

AspectWP-CLI search-replaceBetter Search Replace Plugin
SpeedVery fastSlower (web interface)
Large Databases✅ No limits⚠️ May timeout
Automation✅ Scriptable❌ Manual
Dry Run✅ Built-in✅ Available
Serialized Data✅ Handles correctly✅ Handles correctly
Visual Interface❌ Command-line✅ GUI
Works When Site Broken✅ Yes❌ Requires wp-admin
Key Takeaway

WP-CLI search-replace is faster, more reliable, and scriptable. Use it for migrations, automation, and large databases. Use plugins when you need a visual interface or are uncomfortable with command line.

Next Steps​