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.
Note
There is a known bug in WP-CLI that causes some database commands to not work as expected. The command examples below are formatted to prevent the bug from occurring during a search-replace
operation.
--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.
VIP-CLI command examples
For demonstration purposes, the <app-alias>
value mytestsite
and the <env>
value develop
are used in the VIP-CLI command examples below. Read more about how to target environments in VIP-CLI commands.
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
). To target the main site’s tables, list all of them in the command.wp_<ID>_
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
site should be targeted first. If a command was run to search for example.com
/pencilexample.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.
Last updated: November 22, 2022