Use WP-CLI search-replace

The WP-CLI search-replace command can be used to replace strings in a selection of database tables. This is useful for updating post content and options, especially when changing URLs for sites on a WordPress multisite.

--dry-run

Always run a search-replace command first with the --dry-run option. This will output the potential results of the command without actually running it, making it possible verify the results prior to making changes in the database.

Adding the  --dry-run option to the end of the command will make it easier to remove it when running the final command.

--report-changed-only

By including the --report-changed-only option in a search-replace command, only the fields and tables affected by the command will be reported. This keeps the reporting output shorter which can be more readable in some cases. The --report-changed-only option is useful and compatible to run with the --dry-run option.

Targeting all tables on a WordPress single site

Target all database tables by adding --all-tables or --all-tables-with-prefix to the search-replace command:

vip @mytestsite.develop -- wp search-replace oldstring newstring --all-tables --dry-run 

The difference between options

The difference between these two options primarily applies to WordPress multisites.

• --all-tables: Enable replacement on all tables in the database, regardless of the prefix or --url flag. This overrides --network, --url and --all-tables-with-prefix.
• --all-tables-with-prefix: Enable replacement only on tables that match the table prefix (even if not registered on $wpdb).

The  --all-tables and --all-tables-with-prefix options will be ignored if a table is specified in the command (e.g., wp_comments, wp_commentmeta).

Targeting specific table(s) on a WordPress single site

Specify the tables in a database targeted by search-replace by including the table name(s) in the command.

An example command targeting only the wp_comments table:

vip @mytestsite.develop -- wp search-replace oldstring newstring wp_comments --all-tables --dry-run

An example command targeting the wp_comments and the wp_commentmeta tables:

vip @mytestsite.develop -- wp search-replace oldstring newstring wp_comments wp_commentmeta --all-tables --dry-run

Targeting all tables for all sites on a multisite

vip @mytestsite.develop -- wp search-replace oldstring newstring --all-tables --dry-run

Targeting network-level tables

An example search-replace that targets the network-level tables shared by all network sites on a multisite:

vip @mytestsite.develop -- wp search-replace oldstring newstring wp_a8c_cron_control_jobs wp_blog_versions wp_blogmeta wp_blogs wp_commentmeta wp_comments wp_links wp_options wp_postmeta wp_posts wp_registration_log wp_signups wp_site wp_sitemeta wp_term_taxonomy wp_termmeta wp_terms wp_usermeta wp_users --all-tables --dry-run

Targeting only the tables of the main site (ID 1)

Performing search and replace for the main site is a bit trickier because its tables do not include the site ID in the table prefix (i.e.,  wp_ rather than wp_<ID>_). To target the main site’s tables, list all of them in the command.

An example search-replace command targeting all tables belonging to the main site on a multisite:

vip @mytestsite.develop -- wp search-replace oldstring newstring wp_a8c_cron_control_jobs wp_commentmeta wp_comments wp_links wp_options wp_postmeta wp_posts wp_term_taxonomy wp_termmeta wp_terms --all-tables --dry-run

Targeting a network site (not ID 1)

All tables belonging to a specific network site can be targeted by passing the site’s URL with the --url option.

Using the --url option still requires the --all-tables-with-prefix option to be included as well.  --all-tables-with-prefix determine the tables to target based on the site value passed by the --url option.

vip @mytestsite.develop -- wp search-replace oldstring newstring --all-tables-with-prefix --url=domain.go-vip.net --dry-run

All tables belonging to a specific network site can also be targeted by including a wildcard table selection based on the network site’s ID (e.g., wp_3_*).

vip @mytestsite.develop -- wp search-replace oldstring newstring --all-tables-with-prefix wp_3_* --dry-run 

The --url method and the wildcard table selection method will only target the tables belonging to that specific network site. Some settings for a network site are stored in the network-level wp_blogs table and must be updated in a separate command.

Targeting specific table(s) on a multisite

Network-level tables shared by all sites on a multisite can be targeted by listing the table(s) in the command, and including the --all-tables option.

vip @mytestsite.develop -- wp search-replace oldstring newstring wp_blogs --all-tables --dry-run

A wildcard table selection method can be used to target a table where it exists on all sites across the network. In this command example, the wp_comments table of every network site is targeted by including wp_*comments:

vip @mytestsite.develop -- wp search-replace oldstring newstring wp_*comments --all-tables --dry-run

When the --all-tables option is included in a search-replace command:

• --network will have no effect and should be omitted.
• --url will be ignored.

Beware of overlapping URLs or partial strings

Before running search-replace commands that will target tables belonging to multiple network sites, ensure that there are no “overlapping URLs” which can cause unexpected issues. If sites on a network have Site Address URLs with subdirectories (e.g., https://example.com/site/), update the subdirectory sites first.

For example, a multisite has a main site (ID 1) assigned to the Site Address URL oldexample.com, and the Site Address URL of network site ID 2 has a subdirectory structure oldexample.com/site2. Because the two sites share the same domain, running the command wp search-replace oldexample.com newexample.com wp_blogs will update both site ID 1 and site ID 2, because oldexample.com was found in both of their URLs.

To prevent the search-replace from overlapping and causing problematic results, perform a search-replace for site ID 2 first:

vip @mytestsite.develop -- wp search-replace "oldexample.com/site2" "newexample.com" --all-tables --dry-run

A separate search-replace can then be run for site ID 1:

vip @mytestsite.develop -- wp search-replace "oldexample.com" "admin.example.com" --all-tables --dry-run

Similarly, if two network sites with subdirectory structures have overlapping values example.com/pen and example.com/pencil, the example.com/pencil site should be targeted first. If a command was run to search for example.com/pen and replace it with example.com/marker, the result would be example.com/pen and example.com/markercil because of the overlap at the beginning of the subdirectory value.

Similarly, running a command to search for example.com and replace it with www.example.com, will result in existing instances of www.example.com updated to www.www.example.com, and email addresses such as @example.com updated to @www.example.com. To prevent these issues, target the value more specifically by including // in the search: //example.com.

Elementor

If a site’s theme is Elementor, some data may be stored as serialized data and will not be targeted by search-replace commands. Elementor provides its own search-replace utility that is effective for updating its serialized data and can be found in a site’s WordPress Admin dashboard: 
/wp-admin/admin.php?page=elementor-tools#tab-replace_url

For more information, review Elementor’s guide to “Search and Replace URLs using Elementor“.

Note that Elementor’s search-replace accepts only full URLs, so searching for multiple variations (http vs https, www vs non-www, et al) is required.

Other plugin-related issues

Plugins that store serialized content in JSON format can also have unexpected search-replace issues.

A search-replace for the JSON encoded URLs needs to account for escape slashes ( \/ ) included in the values:

vip @mytestsite.develop -- wp search-replace "https:\/\/old.site.com\/subfolder\/" "https:\/\/new.site.com\/" --all-tables --report-changed-only --dry-run

Clearing the cache

Before reviewing the results of a successful search-replace command, the object cache must be flushed:

vip @mytestsite.develop -- wp cache flush

On multisite, the --network or --url options may need to be included depending on where updates were made.

For example, to flush the cache for all sites on a multisite:

vip @mytestsite.develop -- wp cache flush --network

To flush the cache for one specific site on a multisite:

vip @mytestsite.develop -- wp cache flush --url=http://example.com

If a command produces unintended results, a database backup can be downloaded, and the database backup can be imported to restore the environment to its previous state.