Use WP-CLI search-replace

The WP-CLI search-replace command can be used to search and 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.

Limitations

• Only valid JSON-formatted arguments can be passed as values in a WP-CLI search-replace command that is run on a VIP Platform container.
• There is a known bug in WP-CLI that causes some database commands to not work as expected. The command examples provided here are formatted to prevent the bug from occurring during a search-replace operation.

Best practices for running a search-replace command

WP-CLI search-replace commands make changes directly to a site’s database and should be run with great care and consideration. To more accurately assess the operations of a search-replace command before, during, and after it is 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.
• Include the --report-changed-only option in a search-replace command so that only the fields and tables affected by the command will be reported. This will make the reporting output shorter which is more readable in most cases. The --report-changed-only option is useful and compatible to run with the --dry-run option.
• After successfully running a search-replace command, and before reviewing the results on the front end, the object cache must be flushed with the WP-CLI command: wp cache flush.
  • Use caution when flushing the object cache. The performance of a site’s origin database server is protected by the object cache layer. Flushing the object cache can have performance implications, particularly during high traffic events.

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. Databases for production environments are backed up every hour.

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 --report-changed-only --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 --report-changed-only --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.

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 --report-changed-only --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 --report-changed-only --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 --report-changed-only --dry-run

Targeting all tables for all sites on a multisite

vip @mytestsite.develop -- wp search-replace oldstring newstring --all-tables --report-changed-only --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 --report-changed-only --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  --report-changed-only --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 --report-changed-only --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_* --report-changed-only --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 --report-changed-only --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 --report-changed-only --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.

Replacing a value with an empty string

The search-replace command can be used to remove specific values in the database by replacing a string value with an empty string. When executing this with VIP-CLI, both terms must be passed within double quotes ("), and the double quotes for the empty string value must be escaped with backslashes (\).

In this command example, all occurrences of the string “potato” will be removed from post content:

vip @mytestsite.develop -- wp search-replace "potato" \"\" wp_posts --all-tables --report-changed-only --dry-run

Serialized content in JSON format

Plugins that store serialized content in JSON format can also have unexpected search-replace issues. A search-replace for JSON-encoded URLs needs to account for escape slashes ( \/ ) included in the values, for example:

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

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

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.

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