Bulk Find and Replace WordPress Database – The Easy Way / WordPress

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

WordPress.org hosts a lot of plugins, such as WP DBManager and WP Migrate DB, and also has a number of 3rd party tools that let you Import database easy The hard part is right change every URL instance inside the database.

After moving the database, the URLs inside it still pointing to the old sitein this case to acme.com. You can find the old URL in wp_options table, set as the value of trang web and nhà options and it’s probably also embedded in some other rows and tables in the database. These old URLs can eventually prevent your site from running properly, so you need to change them to new URLsin our example to acme.dev.

At this point, visit our developer site at acme.dev will guide to a blank page.

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

Run an SQL query

Usually people will run the following SQL Query replacing the values ​​of trang web and nhà option in wp_options table.

 CẬP NHẬT wp_options SET tùy chọn_value = thay thế (tùy chọn_value, 'http://acme.com', 'http://acme.dev') WHERE tùy chọn_ame ' 

They then use another SQL Query to replace all URL occurrences in wp_posts table, namely in Nội dung bài viết column of each row.

 CẬP NHẬT wp_posts SET post_content = thay thế (post_content, 'http://acme.com', 'http://acme.dev'); 

Running these queries is a possible solution, but at the same time, inconvenient to do. It might even be neural, seeing how the database can turn haywire due to simple oversight in the query.

Leverage WP-CLI

Another thing we can do, and which I find to be a more convenient alternative, is take advantage of the WP-CLI . command line. This means you will need to install WP-CLI.

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

Then run the following command:

tìm kiếm wp thay thế 'http://acme.com "http://acme.dev

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

The command line will search through All tables in the databasenot only Nội dung bài viết column and wp_options table, and replace all cases of the entry via the command’s parameters.

As you can see from the screenshot above, a total of 225 replacements were made. And we did it via a simple command line.

I think it’s worth mentioning that we can use tìm kiếm thay thế wp command, not only to replace the URL, but any part of value store in the database as well. We can also restrict the operation to a certain table by passing the table name as the fourth parameter, like this:

tìm kiếm wp thay thế '.jpg ".webp' wp_posts

Run the above command, it will only search through wp_poststhe table that stores our content – posts, pages, etc. – and replaces the image extension from .jpg arrive .webp.

WP-CLI makes a confusing SQL operation look more intuitive and you can work with it in a more convenient way. If you want to fine-tune your commands, check out WP-CLI’s documentation, which gives you a list of options for doing more advanced operations with tìm kiếm thay thế wpcommand.