wp search-replace: why find-and-replace in a SQL dump breaks your site

You move a WordPress site to a new domain, open the SQL dump in an editor, replace old-domain.nl with new-domain.nl everywhere, import it, and the site comes up. Then the widgets are gone, the theme options have reset, and a plugin has forgotten its licence key. Nothing errored. This post is about why, and about the one tool that gets it right.

WordPress stores settings as serialized PHP

Most of what lives in wp_options and wp_postmeta isn’t a plain string. It’s a PHP array or object, turned into text with serialize(). This is what a small theme option looks like in the database:

a:2:{s:4:"logo";s:38:"https://old-domain.nl/img/logo-2024.png";s:5:"color";s:7:"#15803D";}

Read it slowly: a:2 — an array with two entries. s:4:"logo" — a string of four characters. s:38:"https://old-domain.nl/img/logo-2024.png" — a string of 38 characters. The number in front of every string is its length in bytes, and PHP’s unserialize() reads exactly that many bytes, no more, no fewer.

What a text replacement does to it

Replace old-domain.nl (13 characters) with new-domain.nl (13 characters) and you get away with it: same length, the numbers still add up. That’s how people learn the habit — it worked once.

Replace it with new-bakery-domain.nl (20 characters) and the record now reads:

a:2:{s:4:"logo";s:38:"https://new-bakery-domain.nl/img/logo-2024.png";s:5:"color";s:7:"#15803D";}

The string is 45 characters long and the header still says 38. unserialize() reads 38 bytes, finds itself in the middle of logo-2024.png instead of at a closing quote, and gives up. It returns false. WordPress treats a false option as “not set”, the theme falls back to defaults, and the setting is gone — not corrupted, not erroring, just gone. Same for the widget layout, same for the plugin’s licence, same for every Elementor page, whose entire layout is one serialized blob in _elementor_data.

Going the other way — a shorter domain — fails just as quietly: 38 bytes now runs past the closing quote into the next field.

What wp search-replace does instead

WP-CLI’s search-replace command doesn’t treat the database as text. For every cell it checks whether the value is serialized; if it is, it unserializes it, replaces the string inside the real PHP data structure, and serializes it again — so every length header is recomputed. The 38 becomes a 45 and nothing breaks.

wp search-replace 'https://old-domain.nl' 'https://new-bakery-domain.nl' 
  --all-tables-with-prefix --precise --skip-columns=guid --dry-run

Four flags worth knowing:

• --dry-run first. It prints a table of how many replacements it would make per table and column, which is also the fastest way to discover a plugin storing URLs somewhere unexpected.
• --all-tables-with-prefix covers tables that aren’t core WordPress but share the prefix — WooCommerce, form plugins, page builders.
• --precise uses PHP’s own unserialize for every value instead of the faster regex heuristic. Slower, and the right choice when the site matters.
• --skip-columns=guid. The post GUID is an identifier, not a link; WordPress’s own guidance is to leave it alone so feed readers don’t see every post as new.

Run it twice if the scheme changes: once for https://old → https://new, once for http://old → https://new, because both forms will be in the database.

And the “just edit the dump” workflow?

There’s a version of it that’s safe: wp search-replace has an --export flag that writes the rewritten database to a file instead of changing the live tables. That’s what SiteShift does on every push — the local database is exported with the local URL replaced by the production URL, serialized values recomputed, and the file is what gets imported on the server. On a pull it runs the other way round, production URL to local, straight into the local tables.

wp search-replace 'http://acme-bakery.localhost:20001' 'https://acme-bakery.nl' 
  --all-tables-with-prefix --export=export.sql

If you can’t run WP-CLI on the server, the plugin route works too: Better Search Replace and WP Migrate both do the same unserialize-replace-serialize dance from inside wp-admin. What they have in common with WP-CLI, and what a text editor doesn’t, is that they understand what they’re editing.

The rule

Never change the length of a string inside a serialized value with a tool that doesn’t know it’s serialized. That includes your editor, sed, and the “find and replace” in phpMyAdmin’s SQL tab. The site will come up, and it will have forgotten something.