WordPress Database Migration: 3 Real Traps and Fixes

WP-CLI's search-replace command handles serialized data length fields automatically. This is the correct approach:

# Dry-run first to see the impact
wp search-replace 'old.example.com' 'new.example.com' --all-tables --dry-run --report-changed-only

# Execute after confirming
wp search-replace 'old.example.com' 'new.example.com' --all-tables

Don't skip these two flags:

• `--all-tables`: By default, only tables registered in `$wpdb` are processed. Custom plugin tables get skipped (WooCommerce's `wp_woocommerce_sessions`, Elementor's `wp_elementor_library` are common victims)
• `--dry-run`: Run once to see how many rows will change, confirm no false positives before executing

If your fields contain JSON (WordPress 5.7+ stores some wp_usermeta fields in JSON), add --precise:

wp search-replace 'old.example.com' 'new.example.com' --all-tables --precise

--precise uses PHP's preg_replace per row instead of MySQL's REPLACE(). It's 3-5x slower but safer for serialized and JSON data.

Collation Mismatch: The Silent Bomb When Moving from MySQL 5.7 to 8.0

MySQL 8.0 changed the default collation from utf8mb4_unicode_ci to utf8mb4_0900_ai_ci. When you import a 5.7 database dump into 8.0, the SQL dump's COLLATE=utf8mb4_unicode_ci mixes with 8.0's default utf8mb4_0900_ai_ci, and JOIN queries fail:

Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

This error sits quietly in the background. The front-end symptom: some pages fail to load, WooCommerce order queries break, search stops working.

Fix: Unify collation before importing—not after. Fixing post-import requires ALTER on every table and column, too easy to miss one.

# Replace collation in the dump file before importing
mysqldump -u root -p wordpress_db > dump.sql
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql
# Or the other direction, unifying to 0900
# sed -i 's/utf8mb4_unicode_ci/utf8mb4_0900_ai_ci/g' dump.sql

Which direction? **Follow the target server's MySQL version**. Target runs 8.0? Unify to utf8mb4_0900_ai_ci. Target runs 5.7? Unify to utf8mb4_unicode_ci.

If you already imported and are hitting errors, batch-fix tables:

# Generate ALTER statements for all mismatched tables
mysql -u root -p -e "
SELECT CONCAT('ALTER TABLE \`', TABLE_NAME, '\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'wordpress_db'
AND TABLE_COLLATION != 'utf8mb4_unicode_ci';" > fix_collation.sql

# Execute
mysql -u root -p wordpress_db < fix_collation.sql

Three Hidden WP-CLI search-replace Omissions

Omission 1: Skipping tables with custom prefixes

If your wp-config.php has a non-default $table_prefix (e.g., wp_42_), wp search-replace only processes tables registered in $wpdb. Third-party plugin tables aren't automatically included.

# Wrong: only replaces default registered tables
wp search-replace 'old.example.com' 'new.example.com'

# Correct: covers all tables
wp search-replace 'old.example.com' 'new.example.com' --all-tables

# Multisite
wp search-replace 'old.example.com' 'new.example.com' --network --all-tables

Omission 2: You shouldn't replace the GUID field

The guid column in wp_posts stores each post's permanent identifier. WordPress documentation explicitly states that guids should not change after publication. Replacing guids causes RSS readers to re-push all old articles to subscribers.

# Exclude guid column
wp search-replace 'old.example.com' 'new.example.com' --all-tables --skip-columns=guid

Omission 3: Memory exhaustion on large databases

When the database exceeds 1GB, wp search-replace can hit PHP memory limits and abort. The correct approach is to process tables individually:

# Process table by table, avoid full-scan OOM
wp search-replace 'old.example.com' 'new.example.com' wp_options --dry-run
wp search-replace 'old.example.com' 'new.example.com' wp_postmeta --dry-run
wp search-replace 'old.example.com' 'new.example.com' wp_posts --dry-run
# Confirm each table, then remove --dry-run to execute

Or raise the PHP memory limit directly:

wp search-replace 'old.example.com' 'new.example.com' --all-tables --skip-columns=guid --memory-limit=512M

Pre- and Post-Migration Checklist

Before migration:

1. Backup the target database (mysqldump full backup)

2. Check MySQL version and collation on both source and target

3. Run wp search-replace --dry-run to estimate affected rows

After migration:

# 1. Verify homepage responds
curl -sI https://new.example.com | head -5

# 2. Check for residual old domain references
wp db query "SELECT COUNT(*) FROM wp_options WHERE option_value LIKE '%old.example.com%'"

# 3. Flush cache and rewrite rules
wp cache flush
wp rewrite flush

# 4. Check for collation mixing
wp db query "SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_COLLATION != 'utf8mb4_unicode_ci'" 2>/dev/null

For more WordPress operations guidance, see my earlier posts on WordPress Security Hardening 2026 and Cloudflare Tunnel Zero-Port Setup. Database migration is a core operations skill that goes hand-in-hand with security and performance work.

This post contains affiliate links. If you purchase through these links, I may earn a commission at no extra cost to you.

📌 This article was AI-assisted generated and human-reviewed | TechPassive — An AI-driven content testing site focused on real tool reviews