Advanced WordPress Database HTTPS Search + Replace with WP-CLI

When migrating your WordPress site to https you can have leftover http references. Despite using a migration tool or some plugin, they don’t catch all the http references which can cause mixed content errors errors and insecure content warnings.

I do migrations often via Codeable and try to use Kinsta or Cloudways whenever possible since they offer WP-CLI.

Be sure to check out the in-depth checklist for migrating from HTTP to HTTPS on Kinsta.

Finding Occurences

Take a backup first, it is super easy, this way if you mess anything up you can re-import and start over.

wp db export backup-before-search-replace.sql

We can simulate a dry run with using the --dry-run flag it will count the amount of times it finds the domain.com string

wp search-replace 'wp-bullet.com' 'wp-bulletnew.com' --dry-run

You will get a summary showing how many database replacements will be made

+------------------+-----------------------+--------------+------+
| Table            | Column                | Replacements | Type |
+------------------+-----------------------+--------------+------+
| wp_commentmeta   | meta_key              | 0            | SQL  |
| wp_commentmeta   | meta_value            | 0            | SQL  |
| wp_comments      | comment_author        | 0            | SQL  |
| wp_comments      | comment_author_email  | 0            | SQL  |
| wp_comments      | comment_author_url    | 0            | SQL  |
| wp_comments      | comment_author_IP     | 0            | SQL  |
| wp_comments      | comment_content       | 0            | SQL  |
| wp_comments      | comment_approved      | 0            | SQL  |
| wp_comments      | comment_agent         | 0            | SQL  |
| wp_comments      | comment_type          | 0            | SQL  |
| wp_links         | link_url              | 0            | SQL  |
| wp_links         | link_name             | 0            | SQL  |
| wp_links         | link_image            | 0            | SQL  |
| wp_links         | link_target           | 0            | SQL  |
| wp_links         | link_description      | 0            | SQL  |
| wp_links         | link_visible          | 0            | SQL  |
| wp_links         | link_rel              | 0            | SQL  |
| wp_links         | link_notes            | 0            | SQL  |
| wp_links         | link_rss              | 0            | SQL  |
| wp_options       | option_name           | 0            | SQL  |
| wp_options       | option_value          | 13           | PHP  |
| wp_options       | autoload              | 0            | SQL  |
| wp_postmeta      | meta_key              | 0            | SQL  |
| wp_postmeta      | meta_value            | 21           | PHP  |
| wp_posts         | post_content          | 36           | SQL  |
| wp_posts         | post_title            | 0            | SQL  |
| wp_posts         | post_excerpt          | 0            | SQL  |
| wp_posts         | post_status           | 0            | SQL  |
| wp_posts         | comment_status        | 0            | SQL  |
| wp_posts         | ping_status           | 0            | SQL  |
| wp_posts         | post_password         | 0            | SQL  |
| wp_posts         | post_name             | 0            | SQL  |
| wp_posts         | to_ping               | 0            | SQL  |
| wp_posts         | pinged                | 0            | SQL  |
| wp_posts         | post_content_filtered | 0            | SQL  |
| wp_posts         | guid                  | 78           | SQL  |
| wp_posts         | post_type             | 0            | SQL  |
| wp_posts         | post_mime_type        | 0            | SQL  |
| wp_term_taxonomy | taxonomy              | 0            | SQL  |
| wp_term_taxonomy | description           | 0            | SQL  |
| wp_termmeta      | meta_key              | 0            | SQL  |
| wp_termmeta      | meta_value            | 0            | SQL  |
| wp_terms         | name                  | 0            | SQL  |
| wp_terms         | slug                  | 0            | SQL  |
| wp_usermeta      | meta_key              | 0            | SQL  |
| wp_usermeta      | meta_value            | 1            | PHP  |
| wp_users         | user_login            | 0            | SQL  |
| wp_users         | user_nicename         | 0            | SQL  |
| wp_users         | user_email            | 1            | SQL  |
| wp_users         | user_url              | 0            | SQL  |
| wp_users         | user_activation_key   | 0            | SQL  |
| wp_users         | display_name          | 0            | SQL  |
+------------------+-----------------------+--------------+------+
Success: 150 replacements to be made.

To perform the actual search and replace in the WordPress database

wp search-replace 'wp-bullet.com' 'wp-bulletnew.com'

By default the search-replace command does the tables registered with $wpdb. You may have some custom tables that also need to be updated and then the --all-tables flag is very useful.

wp search-replace 'wp-bullet.com' 'wp-bulletnew.com' --all-tables --dry-run

Another handy tool is to create a new database dump with the changes you are making for testing

You can also export the modified database with this parameter --export

wp search-replace 'domain.com' 'newdomain.com' --export=/tmp/staging.sql

URL Encoded

Some WordPress plugins use URL encoding for storing information in the database which uses this format for http and https migration %3A = : and %2F = /.

To verify whether you have any of these in your database use this command

wp search-replace 'http%3A%2F%2F' 'https%3A%2F%2F' --dry-run

Escaped Slashes

Some plugins escape slashes with \/ and can easily be miswp search-replace

wp search-replace 'http:\/\/domain.com' 'http:\/\/newdomain.com' --dry-run

URL Encoded

Replacing URL encoded strings

wp search-replace 'http%3A%2F%2Fdomain.com' 'http%3A%2F%2Fnewdomain.com' 

This is how to replace domains with subfolders and URL encoded strings

wp search-replace 'http%3A%2F%2Fdomain.com%2Fsubfolder' 'http%3A%2F%2Fnewdomain.com%2Fsubfolder'

Escaped Slashes

If you have escaped slashes this command will do the trick

wp search-replace 'http:\/\/domain.com' 'http:\/\/newdomain.com'

WordPress Path Search and Replace

Sometimes plugins store information about the storage path on your host, find out by using this command

wp search-replace '/public_html/wp-bullet.com' '/public_html/newfolder'

That should take care of most WordPress database http and https idosyncracies, let me know if you find any other unconventional patterns in the comments!

Sources

wp search-replace command