How to Replace Text or URL in a MySQL Database (Serialized-Safe,)
<p>Raw SQL REPLACE corrupts serialized WordPress data. Three correct approaches that survive migration: WP-CLI, the standalone PHP script, and the admin plugin.</p>
Migrating a WordPress or any MySQL-backed site to a new domain leaves URLs hardcoded in serialized data inside the database. A naive UPDATE... REPLACE query corrupts those serialized rows. The correct approach uses tools that walk the data structures.
- Top pick: WP-CLI’s
wp search-replacefor any WordPress site. Built-in, serialized-safe, free. - Outside WordPress: the Search Replace DB standalone PHP script for non-CLI environments.
- Skip: raw SQL
UPDATE table SET col = REPLACE(col, 'old', 'new'). It silently corrupts serialized arrayswp_optionsand post meta. The site appears to work until something tries to read the broken data. - What people miss: a “search and replace” that runs against the live site but does not flush object cache leaves stale URLs in Redis. Always flush the cache after.
- Always: back up first, run with
--dry-runfirst, run on staging before production.
The WordPress database stores some content as serialized PHP arrays. The string length is part of the encoding. A naive SQL REPLACE() changes the string but not the length prefix, leaving s:10:"old-domain" updated to s:10:"new-domain.example". A length of 10 in front of an 18-character string. PHP fails to unserialize. The widget setting goes blank. The plugin’s saved options vanish. We have watched this break sites three times in the past year alone, every time after a migration where someone wrote SQL by hand.
01Why raw SQL replace breaks WordPress sites
Open a WordPress database and look at a rowwp_options where option_name = 'widget_text'. The value is a PHP-serialized string that looks like:
a:1:{i:2;a:5:{s:5:"title";s:10:"My Website";s:4:"text";s:42:"Visit https://oldsite.com for more info";...}}The number after each s: is the byte length of the string that follows. PHP’s serialize format requires those lengths to match exactly. Replace oldsite.com with newsite.org in raw SQL and the length prefix still says 42, but the new string is shorter. unserialize() returns false and WordPress treats the option as if it does not exist. Themes lose their settings. Widgets disappear. Plugin configurations reset to defaults.
02Method 1: WP-CLI’s search-replace (the right answer)
For any WordPress site with shell access, this is the only correct approach. It walks serialized data, handles JSON, supports dry-run, and is maintained by the WP-CLI team.
Buy if: not applicable, this is free with WP-CLI. Skip if: the host blocks SSH access; use Method 2 instead.
WP-CLI ships with a wp search-replace command. It walks every table, identifies serialized PHP arrays and JSON payloads, and replaces strings without breaking the encoding. It is the canonical tool.
- 1Back up the database with
wp db export backup.sql - 2Run the dry-run first with
--dry-run - 3Review the count, then run for real
- 4Flush the object cache with
wp cache flush - 5Spot-check the front-end and admin
The basic command:
# Dry run. See what would change, change nothing
wp search-replace 'https://oldsite.com' 'https://newsite.com' --dry-run
# Run for real
wp search-replace 'https://oldsite.com' 'https://newsite.com' --skip-columns=guid
# Flush object cache so Redis does not serve stale URLs
wp cache flushTwo flags worth knowing. --skip-columns=guid excludes the wp_posts.guid column, which the WordPress codex specifically warns should never be changed because feed readers use it as a unique identifier. --all-tables-with-prefix searches every table prefixed with wp_ (or whatever your prefix is), useful when plugins have created their own tables.
--network to apply the replace across every subsite in the network. Without it, only the current site’s tables are touched.
03Method 2: Search Replace DB (when you cannot use WP-CLI)
A standalone PHP script from interconnect/it that runs in a browser. Serialized-safe like WP-CLI, no shell access needed. The classic answer for shared hosting that locks down SSH.
Buy if: the site is on shared hosting with no SSH. Skip if: WP-CLI works in your environment. Always delete the script after use.
interconnect/it’s Search Replace DB is the longstanding non-CLI tool. Download the script, sFTP it to a temporary folder inside the site root, open it in the browser, fill in the search and replace strings, run a dry-run preview, then run the live update. Critically, delete the script and the folder afterward. Leaving it in place is a serious security exposure.
04Method 3: Better Search Replace plugin (the WordPress-admin path)
Free plugin from Delicious Brains. Runs from inside WordPress admin. Adequate for one-off replaces on smaller sites; can time out on large databases.
Buy if: you have admin access but no SSH and no patience for sFTP. Skip if: the database is over 500MB or you are migrating between domains; the timeout risk increases.
Better Search Replace from Delicious Brains exposes the search-replace operation as an admin page. Install, navigate to Tools → Better Search Replace, fill in the strings, choose tables, dry-run first, then run. Deactivate and uninstall after the migration completes. Keeping the plugin around invites accidental misuse later.
05For non-WordPress MySQL: handling serialized PHP elsewhere
If the database is not WordPress (Drupal, Magento, custom PHP application), serialized data still appears in many tables. The principles are the same: never use raw SQL REPLACE() against a column that may hold serialized PHP, JSON, or any length-prefixed encoding.
- Drupal: Drush exposes
drush sql-sanitizeanddrush php-evalfor scripted replaces that handle serialized blocks. - Magento: use
n98-magerun‘sdb:dump+ manual export/import path; serialized config is rare but exists. - Custom PHP apps: write a PHP script that selects rows, unserializes, replaces in-place, re-serializes, updates. Test on staging.
- Plain text-only columns: raw SQL
UPDATE col = REPLACE(col, 'old', 'new')is safe, but only when you can verify the column never holds serialized data.
06Which method should you use?
Pick the right tool
- WordPress with SSH access? → WP-CLI
wp search-replace - WordPress without SSH but with sFTP? → interconnect/it’s Search Replace DB script
- WordPress with only admin access? → Better Search Replace plugin
- Drupal? → Drush with PHP scripted replace
- Custom PHP app or text-only column? → Write a small script that unserializes, replaces, re-serializes
- Plain text columns only with no serialized data? → Raw SQL
REPLACE()is fine, but only if you are certain
07Common mistakes to skip
wp_posts.guid. Feed readers use it as a unique post identifier. Changing it makes existing subscribers re-fetch every post as new. Always use --skip-columns=guid with WP-CLI.
wp cache flush is the one-liner; without it, the front-end keeps serving stale URLs from cache for 12 to 24 hours.
08FAQ
Why does a raw SQL REPLACE break WordPress?
WordPress stores some content as PHP-serialized arrays where the string length is part of the encoding. A raw REPLACE() changes the string but not the length prefix, breaking unserialize(). WordPress then treats the option as missing and shows defaults instead of saved configuration.
What is the safest way to change URLs in a WordPress database?
WP-CLI’s wp search-replace. It walks serialized PHP and JSON, supports dry-run mode, and is the canonical WordPress tool. Always run with --skip-columns=guid to preserve post identifiers.
Do I need to back up before running search and replace?
Yes. Always. wp db export backup.sql is one command. Even with serialized-safe tools, edge cases exist. The backup is your fastest rollback path if something goes wrong.
What does --skip-columns=guid do?
Skips replacing in the wp_posts.guid column. The GUID is meant to be a permanent unique identifier, used by feed readers to avoid re-fetching posts. Changing it causes RSS subscribers to receive every post as new on the next sync.
Can I run search-replace on a live production site?
You can, but for migrations always run on staging first, verify the result, take a fresh production backup, and only then run on production. The dry-run flag is mandatory for the first pass.
09WikiWalls verdict
WikiWalls verdict. WP-CLI
wp search-replaceif you have a shell. Search Replace DB script if you do not. Better Search Replace as the last resort. Never raw SQL on serialized columns. Back up first. Dry-run first. Flush the cache after. The pattern is boring on purpose. Boring is the price of not silently corrupting customer data.
This guide was last reviewed and updated by WikiWalls recently to reflect WP-CLI’s current command syntax, modern WordPress migration practices, and the security considerations for the standalone search-replace script.
