WP-CLI search-replace for an HTTPS move

29 June 2016

Putting a certificate on a WordPress site is the easy part. The site then serves every page over HTTPS with images, links and stylesheets still pointing at http://, because WordPress stores absolute URLs in the database: in post content, in widget and theme settings, and in plugin options. The browser shows mixed-content warnings and some assets get blocked outright.

I've done this for a few blogs that sit in a /blog/ folder under a larger site, and WP-CLI's search-replace is the right tool for it.

Why not just run SQL

The obvious fix is an UPDATE ... SET post_content = REPLACE(...). It works on plain text columns and quietly breaks everything else. WordPress stores options and widget settings as PHP serialised arrays, and a serialised string carries its own length:

s:31:"http://www.example.com/blog/foo";

Change http to https and the string is 32 characters long, but the stored length still says 31. PHP fails to unserialise it and the setting silently resets to its default. wp search-replace unserialises each value, replaces inside it and serialises it again, so the lengths stay right.

Back up first

cd /var/www/example.com/blog
wp db export ../blog-before-https.sql

Keep the export somewhere outside the web root.

Dry run

Run as the user that owns the files. If you're logged in as root, WP-CLI refuses to run unless you add --allow-root; it's better to use sudo -u with the site's user, so any files it creates have the right owner.

sudo -u www-data wp search-replace \
  'http://www.example.com/blog/' 'https://www.example.com/blog/' \
  --skip-columns=guid --dry-run

The output is a table of every table and column searched with the number of replacements in each. Read it. If the numbers in wp_posts.post_content and wp_options.option_value look sensible, run it again without --dry-run.

A few details in that command:

I include the path and the trailing slash in both strings. That stops it matching other sites that happen to start the same way, and on a site in a subfolder it leaves the parent site's URLs alone.
--skip-columns=guid leaves each post's GUID untouched. The GUID is an identifier, not a link; feed readers use it to know whether they've already seen a post, and changing it makes every old post show up as new.
By default it only touches tables registered with WordPress (the wp_ prefix). If plugins created tables of their own, add --all-tables-with-prefix.

Afterwards

Check the two settings that control where WordPress thinks it lives:

wp option get home
wp option get siteurl

Both should now be https://. Clear any page cache and the object cache:

wp cache flush

Then redirect plain HTTP so old links and search results end up in the right place. In the site's .htaccess, above the WordPress block:

RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]

If the server sits behind a load balancer or proxy that terminates SSL, %{HTTPS} is always off and that rule loops; test %{HTTP:X-Forwarded-Proto} instead.

What it won't fix

search-replace only sees the database. Hard-coded http:// URLs in theme files, in a child theme's CSS or in third-party embed codes need fixing by hand. Load a few pages with the browser's developer tools open and the console will list every mixed-content request left.

On a multisite install, add --network to run across every site's tables.