Use Search-Replace in WP-CLI
You can use the WP-CLI search-replace
command to replace strings in a selection of database tables. This is often handy for updating post content and options, such as when changing URLs on a VIP Go multisite.
Note
There’s a bug in wp-cli right now that makes certain database commands not work as expected. This guide clarifies the workarounds needed in different environments.
Dry-run is your friend
To avoid trouble, always run each command with the --dry-run
option before running a replacement on a production site. This will allow you to see the results and, if necessary, verify they are the expected results prior to making changes in the database.
Tip: Adding the parameter to the end allows you to easily remove it when running the final command.
Single-site install, entire site
Add --all-tables
or --all-tables-with-prefix
to your command:
wp search-replace oldstring newstring --all-tables --dry-run
The difference between flags
--all-tables-with-prefix
: Enable replacement on any tables that match the table prefix (even if not registered on$wpdb
).--all-tables
: Enable replacement on ALL tables in the database, regardless of the prefix or--url
flag. Overrides--network
,--url
and--all-tables-with-prefix
.- In either case, the flag will not override individual table specification (see examples below).
In single-site mode, you’re not likely to see any real difference between the two. See multisite (child site) exception below.
Single-install and targeting specific table(s)
Same as above, but with table name(s):
wp search-replace oldstring newstring wp_comments --all-tables --dry-run
wp search-replace oldstring newstring wp_comments wp_commentmeta --all-tables --dry-run
Multisite, all sites
wp search-replace oldstring newstring --all-tables --dry-run
Multisite, only main site (ID 1)
This one is a bit trickier since we need to use --all-tables
or --all-tables-with-prefix
, and site ID 1 tables are not prefixed with wp_1_
. We can’t target the main site very easily and instead, have to manually specify the tables to hit.
Just ID 1 tables:
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
Also the network tables (be sure to pick and choose as/if needed):
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
Multisite, child site (not ID 1)
Use --all-tables-with-prefix
without a --url
flag:
wp search-replace oldstring newstring --all-tables-with-prefix wp_3_* --dry-run
Note: The wildcard table selection (e.g. wp_3_*
) is what is needed to restrict, if you choose not to specify --url
(or else the prefix ends up being just wp_
because it’s the main site and ends up being affecting the entire database).
Alternatively, you may specify the URL instead of site prefix:
wp search-replace oldstring newstring --all-tables-with-prefix --url=domain.go-vip.net --dry-run
If specifying --url
, you still must use --all-tables-with-prefix
. Remember, --all-tables-with-prefix
doesn’t take an argument, it determines automatically from context, which is determined by the --url
.
This will run on all tables that belong to the specific sub-site. Once this is run, the last remaining instance of the oldstring
from the wp_blogs
table must also be replaced. See the next section for the exact command.
Multisite, specific table(s)
Updating a network table (--url
will be ignored due to --all-tables
):
wp search-replace oldstring newstring wp_blogs --all-tables --dry-run
Note: As mentioned earlier, if you’re using --all-tables-with-prefix
on a subsite (using --url
), this will not work because you’ll be restricted to wp_<id>_*
prefix.
Using a wildcard to get all comments
tables:
wp search-replace oldstring newstring wp_*comments --all-tables --dry-run
You won’t need the --network
flag, just use --all-tables*
instead. In fact, the --network
flag doesn’t even work without the --all-tables*
flag.
Beware of overlapping URLs or partial strings
Before proceeding with running the commands on multiple blogs inside the multisite, make sure there are no overlapping URLs which would cause some issues during the search replace on shared tables. For instance, if the multisite is a directory multisite, you have to process the blogs with directory in the URL first.
Example:
Multi-site blog has two site IDs 1 (oldexample.com) and 2 (oldexample.com/blog2). Doing wp search-replace oldexample.com newexample.com wp_blogs
would not only override the URL for blog ID 1, but also for blog ID 2, since the oldexample.com
is a part of its URL. It would be better to do the search-replace for blog ID 2 and then, blog ID 1:
# search-replace site ID 2 first
wp search-replace "oldexample.com/blog2" "newexample.com/blog2" --all-tables --dry-run
# ...then site ID 1
wp search-replace "oldexample.com" "newexample.com" --all-tables --dry-run
Similarly, if two directory multisite’s blogs have overlapping slugs, eg: /ham
and /hamburger
, you should make sure to process the /hamburger
blog first, as replacing /ham
first would also replaced the /hamburger
due to the overlap on the beginning of the slug.
Finally, if changing example.com
to http://www.example.com
, this string would also change existing instances of http://www.example.com
to http://www.www.example.com
, and emails to @www.example.com
. Instead, target your search at //example.com
Elementor
If the site’s theme is Elementor, then some data may be stored in a serialized fashion that won’t be hit by WP-CLI’s search-replace command. Fortunately, Elementor provides its own search-replace utility that is effective in these situations. It’s found at /wp-admin/admin.php?page=elementor-tools#tab-replace_url
, and a tutorial for using it can be found here.
Note that Elementor’s search-replace accepts only full URLs, so you may need to search for multiple variations (http
vs https
, www
vs non-www
, et al).
Other plugin-related issues
You may also encounter issues with plugins that store data in JSON format (known examples include Gravity Forms and Advanced Custom Fields).
A typical URL search-replace will not work here so you’ll need to do an additional search-replace for the JSON encoded URLs:
wp search-replace "https:\/\/old.site.com\/subfolder\/" "https:\/\/new.site.com\/" --all-tables --report-changed-only --dry-run
Tips and Tricks
You may wish to use the --report-changed-only
flag to have the command report only the fields that have changed. This keeps the reporting output shorter which can be more readable in some cases.
Finishing Up
Clear the Cache
At the end of a successful search-replace
command, you will be reminded to flush the cache. This is important step is required prior to checking your results:
wp cache flush
On multisite, you may need pass either --network
or --url
flags depending on where you made changes (everywhere or child site, respectively)
wp cache flush --url=http://example.com
If a command produces unintended results, request VIP support to restore one of the hourly backups for the affected site.