How to Search and Replace in your WordPress Database

Search and Replace. Find and Replace.  Whatever you call it, we’ve all had to do it at some point or another.

When it comes to swapping out one string for another in WordPress, you need to be careful.  Some values are stored in the database as serialized arrays.  They look like this:

a:1:{s:13:"administrator";b:1;}

(This example is the wp_capabilities field in wp_usermeta)  If I were to do a search and replace, or an UPDATE in SQL, to change ‘administrator’ to ‘subscriber’, I would break things.  This particular array expects the string where ‘administrator’ is written to be 13 characters long.  To properly update this field, I would also have to change the s:13: before administrator to s:10: (the word subscriber is 10 characters long).  What a pain, right?

The good thing is, there’s a few solutions out there to help you do a safe search & replace in WordPress.   I’ll show you my two favourites below.

Why you’d need to do this?

The most common search and replace I do in WordPress is when moving my database from a production website to my local development environment.   The site’s domain name is scattered throughout posts, widgets, plugin settings, etc. It’s not as simple as just updating the Site URL in the WordPress settings screen.  example.com has to be replaced with localhost in a million different spots (ok, maybe I’m exaggerating a little bit).

Solution #1: Interconnect IT’s Search & Replace Tool

Interconnect IT has made a great web-based tool for safely replacing values in WordPress’ database without breaking serialized arrays.

Once you download the search/replace tool from their website, place it (or upload it) in a new folder at the root of your WordPress site, the name of the folder does not matter.  Navigate to the folder, and the tool will run, automatically capturing the database connection details from your wp-config.php fie.

A few of the great feature about this tool are:

1. It supports regex, so if you’re that kind of ninja, you can do some pretty powerful searching.
2. You can do a “dry run” – where you will get to see how many changes would be made in each table.  It’s a great way of catching mistakes before doing seriously damage.

Don’t say you weren’t warned:  More than once, this utility reminds you that once you’ve completed your search & replace, make sure to delete to tool!  If someone else were to stumble upon it, they could destroy your site. (They’d also have your database credentials, since they’re right there on screen)

If you want to leave the utility installed, I’d suggest protecting it’s folder with an .htaccess file.

Solution #2: Search & Replace using WP-CLI

My preferred method of doing a search and replace is to use WP-CLI.  If you’ve never used WP-CLI, it is a set of command line tools that allow you to manage your WordPress site(s).   Not everyone will be able to use this method, you need to be hosted somewhere that supports WP-CLI.  Most shared hosts don’t, as they normally block SSH access to their servers for security reasons.  I do know that SiteGround and GoDaddy both support it now.  Of course, if you have a dedicated server or a virtual server with someone like Digital Ocean or Linode you can install it easily.

Once you have WP-CLI running, doing a search & replace is as simple as typing this:

wp search-replace old-value new-value

As with Interconnect IT’s utility, you can also do a dry-run, to make sure you’re only changing what you need to.  Simply add the –dry-run parameter to your command, like so:

wp search-replace old-value new-value --dry-run

Of course, with WP-CLI, you can (and should) backup your database before doing such an operation:

wp db export mydbbackup.sql
wp search-replace old-value new-value --dry-run

You can also restrict the search and replace to specific table names, ignore specific fields, or replace throughout all sites in a WordPress Multisite Network.  If you need more help with this command, just type:

wp search-replace --help

That’s it!

Both of the methods shown there are much safer than doing an UPDATE in MySQL, or a Find & Replace in a .SQL dump of your database.   Good luck, and happy replacing!