Mass Find & Replace WordPress Databases – The Easy Way

There are several occasions where we have to change URLs in our WordPress website database. For instance, we need to do this after migrating a WordPress database from one site, e.g. from the http://acme.com remote production site, to the http://acme.dev local development website on our computer.

WordPress.org hosts a great number of plugins, such as WP DBManager and WP Migrate DB, and there are also a number of 3rd party tools that allow you to import database easily. The tricky part is having to change every URL instanceinside the database.

After migrating the database, URLs inside of it still point to the old site, in this case to acme.com. You may find the old URL in the wp_options table, set as the value of the siteurl and home options, and it’s probably also embedded throughout several other rows and tables in the database. These old URLs may eventually prevent your site from running properly, so you need to change them to the new URL, in our example to acme.dev.

At this point, accessing our development site at acme.dev will simply lead to a blank page.

So how do we change all these URLs in the database?

Running an SQL Query

Usually, people would run the following SQL Query which replaces the values of the siteurl and home options in the wp_options table.

UPDATE wp_options SET option_value =
replace(option_value, 'http://acme.com', 'http://acme.dev')
WHERE option_name = 'home' OR option_name = 'siteurl';

Subsequently, they use another SQL Query to replace all URL occurrences in the wp_posts table, namely in the post_content column of each row.

UPDATE wp_posts SET post_content =
replace( post_content, 'http://acme.com', 'http://acme.dev' );

Running these queries is a workable solution, yet at the same time, is not convenient to do. It could be even nerve-wracking, seeing how the database may turn haywire due to a simple oversight within the query.

Leveraging WP-CLI

Another route we can do, and which I’ve found to be a more handy alternative, is by leveraging the WP-CLI command line. This means you will need to have WP-CLI installed.

Assuming you have installed WP-CLI and have the wp command accessible as the alias, navigate to the directory where your WordPress site files reside.

Then, run the following command:

wp search-replace 'http://acme.com' 'http://acme.dev

The first parameter, 'http://acme.com', is the old entry to be replaced with the second one, 'http://acme.dev'.

The command line will search through all the tables within the database, not only the post_content column and wp_options table, and replace every instance of the entry passed through the parameters of the command.

As you can see from the above screenshot, a total of 225 replacements have been made. And we have done it through a simple line of command.

I think it is worth mentioning that we can use the wp search-replace command, not only for replacing URLs, but any piece of value stored in the database as well. We can also limit the operation into a certain table by passing the table name as the fourth parameter, as follows:

wp search-replace '.jpg' '.webp' wp_posts

Running the above command, it will only search through wp_posts, the table that stores our content – posts, pages, etc. – and replace the image extension from .jpg to .webp.

WP-CLI makes a tangled SQL operation look more intuitive, and you can work with it in a more convenient way. If you want to fine-tune your commands, have a look at WP-CLI’s documentation, that provides you with a list of options to perform a more advanced operation with the wp search-replacecommand.