Replace HTTP:// to HTTPS:// in WordPress

Question

With Google getting picky about HTTPS on sites, I was hoping to be able to do a quick and easy SQL Query to search & replace anything http:// with https://

I normally do something like the below for moving hosts:

UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');

So I tried to do something like

UPDATE `wp_commentmeta` SET 'meta_value' = REPLACE(`meta_value`, 'http://', 'https://');

But it didn't seem to work. Anyway to do the ENTIRE database at once?

---

• I have tried a few options on S.O.F., but nothing worked well.
• I prefer not to have to install a plugin as a "fix"

---

If there is mySQL or htaccess script, I am more interested in those solutions.

Solution 1

If you have access to edit your .htaccess file, you can add the following into it:

RewriteEngine on

# force SSL
RewriteCond     %{SERVER_PORT} ^80$
RewriteRule     ^(.*)$ https://%{SERVER_NAME}%{REQUEST_URI} [L,R=301]

The code above will redirect with 301 (permanent), if don't want to use a 301 redirect, you can simply change the last section on the last line from [L,R=301] to [L,R].

If you want to be a little more thorough with your SQL replacement, you can usually find all the necessary links inside the posts table inside the guid column (featured images) and the post_content column (backlinks etc). And then ofcourse also inside the post_meta table - meta_value column and home/siteurl inside your options table. Here is the SQL Query that I normally use:

UPDATE wp_options SET option_value = replace(option_value, 'http://example.com', 'https://example.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = REPLACE (guid, 'http://example.com', 'https://example.com');
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://example.com', 'https://example.com');
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://example.com','https://example.com');

Solution 2

You can either try WP CLI or the Search Replace DB from Interconnect/it.

If you use the later, you can delete folder after replacing the URL.

Comments

[George Birbilis]

the second part of that is that the GUID must never change. Even if you shift domains around, the post is still the same post, even in a new location. Feed readers being shifted to your new feeds when you change URLs should still know that they’ve read some of your posts before, and thus the GUID must remain unchanged. wordpress.org/support/article/changing-the-site-url/…

[Frits]

@GeorgeBirbilis You're not wrong, however you haven't considered pre-production. As an example, most of the websites I work with / build are hosted on a pre-production URL. As such, readers have been block and nothing has been read yet. Moving to a live URL you should absolutely change the GUID. GUID should remain unchanged if your site structure has changed, but if your FQDN has changed it is critical that the GUID change as well.

[Frits]

And I am specifically referring to "mysubdomain.mypreproductionserver.com" changing to "mylivedomain.com" URL's.

[George Birbilis]

Actually if the site is template based it wouldn't hurt to change them, but according to the doc when moving the site to other domain (or just moving to https) one shouldn't touch those. According to that doc feed readers use those as keys and probably aren't clever enough to adapt when users switch the feed url and show all older/read posts again as unread