When migrating your WordPress site to https you can have leftover http references causing mixed content warnings and missing green secure locks in browsers! Despite using a migration tool or some plugin to do a simple search and replace, 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 --all-tablesWe 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 --skip-columns=guidYou 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' --skip-columns=guidBy 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' ---skip-columns=guid -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' --skip-columns=guid --export=/tmp/staging.sqlURL 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 --skip-columns=guidEscaped Slashes
Some plugins escape slashes with \/ and can easily be miswp search-replace
wp search-replace 'http:\/\/domain.com' 'http:\/\/newdomain.com' --dry-run --skip-columns=guidURL Encoded
Replacing URL encoded strings
wp search-replace 'http%3A%2F%2Fdomain.com' 'http%3A%2F%2Fnewdomain.com' --skip-columns=guidThis 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'--skip-columns=guidEscaped Slashes
If you have escaped slashes this command will do the trick
wp search-replace 'http:\/\/domain.com' 'http:\/\/newdomain.com'--skip-columns=guidWordPress 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' --skip-columns=guidThat should take care of most WordPress database http and https idosyncracies, let me know if you find any other unconventional patterns in the comments!
11 thoughts on “Advanced WordPress Database HTTPS Search + Replace with WP-CLI”
Comments are closed.