Search and replace in the WordPress database

WordPress doesn't store your site's address in just one place. It's in the settings, but also written out in your posts, with images and in the settings of themes, plugins and widgets. So if you change the address, you need to replace it throughout the entire database. Here you can read how to do that safely, and why a plain SQL query can break your site.

When do you need this?

• Your site used to run on http and now runs on https, and you're seeing mixed content (a page on https that loads parts over http). See also No padlock or 'not secure': fixing mixed content.
• You're moving your site to a different domain, or from a temporary address to your real domain.
• You've manually made a copy at a different address, for example a test site.

If you clone a site with Installatron or the Website Manager, it adjusts the addresses in the copy for you. See Cloning and creating a test environment (staging). If you're only moving to different hosting with the same domain, nothing needs to change.

Why not a bare SQL REPLACE?

In phpMyAdmin you can replace all text with a single SQL query. Don't do that for addresses. Many settings are stored serialized: PHP adds the length of each piece of text. It looks like this:

s:27:"http://example.com/logo.png";

s:27 means: a text of 27 characters follows. If you change it to https://, the text is 28 characters long, but it still says 27. PHP can then no longer read the value and the setting silently disappears. You'll then see, for example, an empty menu, a missing logo or widgets that are gone. So use a tool that updates those lengths.

Make a backup first

Search and replace takes effect immediately and has no recycle bin. Export your database beforehand, see Exporting a database, or create an extra backup with one click in WebOké Backups, see WebOké Backups: viewing, restoring or downloading a backup.

Method 1: WP-CLI in the Terminal or via SSH

WP-CLI is the official command-line tool for WordPress. With us, you use it as the wp command in the DirectAdmin Terminal or via SSH. You only have it in the packages that include SSH. You can read which packages those are in Terminal in your browser.

1. Open the Terminal and go to your WordPress folder: cd domains/example.com/public_html
2. First check what would change, without modifying anything:

   wp search-replace 'http://example.com' 'https://example.com' --skip-columns=guid --dry-run

   You'll see per table how many replacements there would be.
3. If that looks right, run the same command without --dry-run.

A few options in the command:

• Without any extra option, WP-CLI works on the tables WordPress knows about. If a plugin uses its own tables with the same prefix (for example wp_), add --all-tables-with-prefix. The --all-tables option affects all tables in the database, including those of another site that happens to be in it.
• --skip-columns=guid leaves the guid column, which holds the permanent identifiers of posts, untouched. WordPress recommends never changing it, otherwise feed readers will show old posts again as new.
• If something goes wrong with complex settings, --precise is slower but more thorough.

Method 2: a plugin

If you don't have a Terminal or SSH, use a search and replace plugin that supports serialized data. There are several in the wordpress.org plugin directory. When choosing one, read the description: it should state that it handles serialized data correctly. Here too, use the dry run mode first if the plugin has one, and remove the plugin when you're done. See also Choosing, assessing and cleaning up WordPress plugins.

Only the WordPress address and the site address?

Can't get into the admin area after a change, because WordPress sends you to the wrong address? Then first adjust only the siteurl and home rows in the wp_options table (the wp_ prefix may be different for you). Those are plain text values, so you can change them in phpMyAdmin. You can read how in Opening and using phpMyAdmin. After that, do the rest with WP-CLI or a plugin.

Afterwards

• Clear the cache of your caching plugin and your browser.
• If pages return a 404, go to Settings > Permalinks and click save once. See My website doesn't work after uploading or moving.
• Check a few pages, your menu, your logo and a form. Press F12 and check the console for any remaining mixed content warnings.

Further reading

• wp search-replace on developer.wordpress.org
• Moving WordPress manually: files, database and wp-config.php

Want to learn more? Take the free WordPress course, lesson 3.2 www, redirects and mixed content or lesson 9.5 Moving a WordPress site.