Update WordPress URL in MySQL & WP-CLI Without Breaking Serialized Data

Migrating a WordPress site to a new domain or switching from http:// to https:// requires updating hardcoded URLs stored across core MySQL tables (wp_options, wp_posts, and wp_postmeta).

Below are both the direct MySQL transaction queries and the safer WP-CLI approach that preserves PHP serialized arrays.

Method 1: Direct MySQL Queries (Transaction-Safe)

For simple sites that do not store domain URLs inside length-prefixed PHP serialized strings in wp_options or wp_postmeta, run the following REPLACE() statements inside a SQL transaction after taking a full mysqldump backup:

START TRANSACTION;

-- Update core siteurl and home options
UPDATE wp_options
SET option_value = REPLACE(option_value, 'https://olddomain.com', 'https://newdomain.com')
WHERE option_name IN ('home', 'siteurl');

-- Update hardcoded links and media URLs inside post content
UPDATE wp_posts
SET post_content = REPLACE(post_content, 'https://olddomain.com', 'https://newdomain.com');

-- Update postmeta values (skip serialized strings if domain lengths differ)
UPDATE wp_postmeta
SET meta_value = REPLACE(meta_value, 'https://olddomain.com', 'https://newdomain.com')
WHERE meta_value NOT LIKE 'a:%' AND meta_value NOT LIKE 'O:%';

-- Only update GUIDs when migrating a staging/development site to production for the first time
UPDATE wp_posts
SET guid = REPLACE(guid, 'https://olddomain.com', 'https://newdomain.com');

COMMIT;

Why Raw SQL REPLACE() Can Corrupt PHP Serialized Data

WordPress themes, widgets, and page builders frequently store settings as serialized PHP strings in wp_options and wp_postmeta, where every string includes its exact byte length:

s:21:"https://olddomain.com";

If https://olddomain.com (21 bytes) is replaced via raw SQL with https://my-new-domain.com (25 bytes), the byte prefix remains s:21:, causing PHP’s unserialize() to fail and resetting widget or theme configurations.

Method 2: Recommended Production Approach with WP-CLI

WP-CLI includes wp search-replace, which automatically deserializes PHP objects, replaces the domain string, recalculates byte lengths, and re-serializes the data safely:

# Preview all replacements first with --dry-run
wp search-replace 'https://olddomain.com' 'https://newdomain.com' --all-tables --dry-run

# Execute the replacement and flush object cache
wp search-replace 'https://olddomain.com' 'https://newdomain.com' --all-tables --precise
wp cache flush