WordPress
Running a Search and Replace in Your WordPress Database
WordPress stores absolute URLs in dozens of database tables, so a domain change or an SSL move leaves old addresses scattered through posts, options and plugin settings: a search and replace is how you clean them up safely. This guide covers the two supported ways to do it in KPanel, why one common third method corrupts data, and how to verify the result.
When You Need One
- Moving from
http://tohttps://after enabling SSL. - Changing domain, for example
old-brand.co.nztonew-brand.co.nz. - After pushing staging to production, when the staging hostname is still baked into the database.
- Retiring an old asset host and repointing every image URL at once.
- Fixing a bulk typo across many posts, such as an old phone number or a discontinued product name.
A search and replace rewrites rows across every table at once and there is no per-row undo. Take a backup before you start, every time, even for a change that looks trivial. KPanel creates one automatically when you use the built in tools described below, but if you are running the command yourself you are responsible for it. See Taking a Backup.
Why You Cannot Just Run a SQL REPLACE
This is the single most damaging mistake in WordPress database work, so it is worth understanding before you pick a method.
WordPress stores plugin settings, theme options and widget data as PHP serialised strings. A serialised string records the length of every value inside it, like this:
a:1:{s:3:"url";s:26:"http://old-domain.co.nz/x";}
That s:26 says the URL is 26 characters long. Replace http:// with https:// using a plain SQL REPLACE() and the text becomes 27 characters while the stored length still claims 26. PHP then refuses to unserialise the whole option, and the setting silently reverts to empty. Theme customiser settings vanish, sliders lose their slides, plugin licences unregister themselves.
The WP-CLI search-replace that KPanel runs unserialises each value, replaces inside it, and reserialises with corrected lengths. That is why it is the only method documented here.
Never run UPDATE wp_options SET option_value = REPLACE(...) or the equivalent in phpMyAdmin against a WordPress database. It looks like it worked, it reports rows affected, and it quietly destroys every serialised setting it touched. There is no repair short of restoring a backup.
Method 1: The Search and Replace Card
This is the right choice for almost everyone. It is available on every WordPress plan.
- Sign in to KPanel and click Websites in the left sidebar.
- Click the site.
- Open the WordPress tab, then the Quick Actions section.
- Find the Search & Replace card and click Configure.
- Enter the existing text in Find (old value).
- Enter the new text in Replace with.
- Leave Dry run (preview only, no changes) ticked and click Preview.
The dry run reports how many replacements would be made and breaks the count down by table and column, so you can see exactly where the change would land before you commit to it.
When the preview looks right:
- Untick Dry run.
- Click Run.
- Confirm the dialog.
A full backup is taken automatically before the replacement begins, and the run covers all tables including those created by plugins.
Search for the most specific string you can. Replacing old-domain.co.nz also rewrites mail.old-domain.co.nz and staging.old-domain.co.nz, which is rarely what you want. Including the scheme, as in https://old-domain.co.nz, keeps the match tight.
Method 2: WP-CLI From the Console
The console gives you the same engine with more control over flags. It is one of the sections that appear on managed plans; on other plans the tab strip shows a +8 on Managed link instead.
Open the site, then WordPress, then Console. The prompt already begins with wp, so type only the rest of the command.
Preview first:
search-replace 'http://old-domain.co.nz' 'https://old-domain.co.nz' --all-tables --dry-run
Then run it for real:
search-replace 'http://old-domain.co.nz' 'https://old-domain.co.nz' --all-tables
The console does not take a backup for you. The automatic pre-run backup only happens when you use the Search & Replace card in Method 1. If you run the command here, take a backup yourself first from the site's Backups tab.
Useful flags:
| Flag | What it does |
|---|---|
--all-tables | Includes custom tables created by plugins, not just the core WordPress ones |
--dry-run | Reports what would change and writes nothing |
--precise | Uses PHP rather than SQL for the replacement. Slower, but handles awkward serialised structures |
--skip-columns=guid | Leaves post GUIDs alone (see below) |
--report-changed-only | Trims the output to tables that actually changed |
A Note on GUIDs
Every WordPress post has a guid column. Despite looking like a URL it is an identifier, not a link, and feed readers use it to tell whether they have already seen an item. Rewriting it can make every post in your feed reappear as new.
Rewrite GUIDs when you are changing domain permanently and starting fresh. Skip them with --skip-columns=guid when you are only moving from HTTP to HTTPS on the same domain.
Changing Domain: Use the Site URL Card Instead
If the entire point is to move the site to a new domain, do not start with search and replace. The Change Site URL card, on the same Quick Actions section, updates the siteurl and home options and runs the replacement across all tables in one operation, in the correct order. Doing it the other way round can leave WordPress unable to load its own admin.
After the Replacement
Work through this list before you call it done.
- Flush the cache. On the Quick Actions section, run Flush Cache. If the site uses the full page cache, purge it from WordPress, then Caching.
- Flush the rewrite rules. Run Flush Rewrites on the same section, or open Settings, then Permalinks in wp-admin and click Save Changes without changing anything.
- Purge the CDN if the site is on it, from Performance, then Kapsule CDN. See Purging the CDN Cache.
- Load the site in a private window so your browser cache cannot mislead you.
- Check the padlock. A missing or warning padlock after an SSL move means URLs were left behind: Fixing Mixed Content Warnings.
- Click through the fiddly pages. Home page sliders, the header logo, any page built with a page builder, and the checkout on a store. These hold the URLs that live in serialised options.
- Clear any caching plugin from its own settings screen.
Troubleshooting
The dry run reports zero replacements. The string is not in the database in that exact form. Check for a trailing slash, a www. prefix, or the scheme. Try searching for just the bare hostname first to confirm it is there at all.
Images are broken after a domain change. Media URLs live in wp_posts and wp_postmeta and are picked up by --all-tables, but a CDN or image optimisation plugin may cache its own rewritten copies. Purge the CDN and the plugin's cache, then reload.
Settings disappeared after the replacement. That is the serialisation problem, and it means the change was made with raw SQL rather than through the tools here. Restore the backup taken before the run: Restoring From a Backup.
Staging URLs keep coming back. Something is repopulating them, usually a scheduled push or a cached option. Check the workflow in Using Staging: Pushing and Pulling and make sure Rewrite URLs is ticked when you push.