Skip to content

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

Screenshot of Interconnect IT's Search & Replace Tool for WordPress

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-runScreenshot of the results from a WP-CLI Search and Replace

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!

5 Comments

  1. I love WP_CLI for this ability!

    I use it in a bash script that pulls a .sql file from a server, backs up my local database, replaces it with the live data and then runs the search and replace to change the urls. Without WP_CLI, there would be no (non-manual) way to do that – such a timesaver.

    • Thanks for the comments Ryan. WP-CLI is great for moving data between production and development environments. I do the same thing you do, but in addition to swapping URLs, I also use it to:

      * disable google analytics plugin (development environment shouldn’t skew results)
      * swap admin e-mail address (which is some cases belong to my clients) with mine (so dev environment notifications don’t go to the client)

      Shawn

  2. I’ve been using Interconnect IT’s script successfully for years, but have yet to play around with the CLI command — looks promising.

    • I still use the Interconnect IT script when working in Shared Hosting environments (due to no SSH) but have been using the CLI method in my development environment, especially when moving production databases inside.
      Saves moving the script into the project, accidentally deploying it to the Git repo, or to prod server. 🙂

  3. Rob P. Rob P.

    Hi Shawn,

    Just found this old but still relevant, article.

    I just got access to this tool. Unfortunately (Inexperience) you don’t seem to be able to do a plain Search.
    Is that correct?

    I’ve searched through command lists and can see wp search-replace, but not just a plain find this string.

    I know I could perhaps do a find replace -dry-run but that is too scary to contemplate on a live site.

    Does anyone know if there Is there a way of finding something without making changes (potentially)?
    Mc P

Leave a Reply

Your email address will not be published. Required fields are marked *