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 instance inside 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
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
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
home options in the
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.
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,
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
Recommended Reading: Complete guide to using WebP image format
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