Sooner or later site owners or developers come to need migrating site from usual HTTP to secured HTTPS connection. In most CMS or frameworks it could be done quite simply, but for WordPress CMS it could become a challenge.
The issue is that WordPress keeps site URL in it’s database, moreover most links in Posts are also kept in database not in relative type like
/blog/archive/post123 but in absolute with specified protocol type, e.g.
http://www.domain.com/blog/archive/post123. So that it is not enough just to change site URL in WP Admin, but there is need to change all URLs in database. As an option it could be done in WP Admin by fixing all URL entries in Posts, Widgets, Themes, Plugins manually but it could take a while depending on how much content site has.
So in most cases during migration WordPress to HTTPS there is need to edit database directly(though there are plugins like Force HTTPS, but real cases show that this plugin fixes not all links). You can download database dump, edit the urls and import the dump back to database, and of course you also can execute needed SQL-queries directly on server/hosting via console or with a tool like PhpMyAdmin.
Isabel Castillo did a great job explaining which SQL-queries should be executed and why in this articel https://isabelcastillo.com/mysql-wordpress-http-to-https. I am not going to copy/paste this article, I just recommend using it as a step-by-step guide. I only want to add that when you’ll be replacing you site URL in SQL-queries then do not forget to also duplicate queries with/without “www” depending on your main domain for site. It’s because your database may have urls with “www” as well as without “www”, it just could happend when you were editing content and forgot to add “www” to url or vice versa.
So this is my final list of SQL-queries:
1) UPDATE ‘wp_posts’ SET post_content = REPLACE(post_content, ‘src=”http://www.domain.com’, ‘src=”https://www.domain.com’) WHERE post_content LIKE ‘%src=”http://www.domain.com%’;
2) UPDATE ‘wp_posts’ SET post_content = REPLACE(post_content, ‘src=”http://domain.com’, ‘src=”https://domain.com’) WHERE post_content LIKE ‘%src=”http://domain.com%’;
3) UPDATE ‘wp_posts’ SET post_content = REPLACE(post_content, “src=’http://www.domain.com”, “src=’https://www.domain.com”) WHERE post_content LIKE “%src=’http://www.domain.com%”;
4) UPDATE ‘wp_posts’ SET post_content = REPLACE(post_content, “src=’http://domain.com”, “src=’https://domain.com”) WHERE post_content LIKE “%src=’http://domain.com%”;
5) UPDATE ‘wp_posts’ SET post_content = REPLACE(post_content, ‘href=”http://www.domain.com’, ‘href=”https://www.domain.com’) WHERE post_content LIKE ‘%href=”http://www.domain.com%’;
6) UPDATE ‘wp_posts’ SET post_content = REPLACE(post_content, ‘href=”http://domain.com’, ‘href=”https://domain.com’) WHERE post_content LIKE ‘%href=”http://domain.com%’;
7) UPDATE ‘wp_posts’ SET post_content = REPLACE(post_content, “href=’http://www.domain.com”, “href=’https://www.domain.com”) WHERE post_content LIKE “%href=’http://www.domain.com%”;
8) UPDATE ‘wp_posts’ SET post_content = REPLACE(post_content, “href=’http://domain.com”, “href=’https://domain.com”) WHERE post_content LIKE “%href=’http://domain.com%”;
9) UPDATE ‘wp_posts’ SET pinged = REPLACE(pinged, ‘http://www.domain.com’, ‘https://www.domain.com’) WHERE pinged LIKE ‘%http://www.domain.com%’;
10) UPDATE ‘wp_posts’ SET pinged = REPLACE(pinged, ‘http://domain.com’, ‘https://domain.com’) WHERE pinged LIKE ‘%http://domain.com%’;
11) UPDATE ‘wp_comments’ SET comment_author_url = REPLACE(comment_author_url, ‘http://www.domain.com’, ‘https://www.domain.com’) WHERE comment_author_url LIKE ‘%http://www.domain.com%’;
12) UPDATE ‘wp_comments’ SET comment_author_url = REPLACE(comment_author_url, ‘http://domain.com’, ‘https://domain.com’) WHERE comment_author_url LIKE ‘%http://domain.com%’;
13) UPDATE ‘wp_comments’ SET comment_content = REPLACE(comment_content, ‘http://www.domain.com’, ‘https://www.domain.com’) WHERE comment_content LIKE ‘%http://www.domain.com%’;
14) UPDATE ‘wp_comments’ SET comment_content = REPLACE(comment_content, ‘http://domain.com’, ‘https://domain.com’) WHERE comment_content LIKE ‘%http://domain.com%’;
15) UPDATE ‘wp_postmeta’ SET ‘meta_value’ = REPLACE(meta_value, ‘http://www.domain.com’, ‘https://www.domain.com’) WHERE meta_value LIKE ‘%http://www.domain.com%’;
16) UPDATE ‘wp_postmeta’ SET ‘meta_value’ = REPLACE(meta_value, ‘http://domain.com’, ‘https://domain.com’) WHERE meta_value LIKE ‘%http://domain.com%’;
17) UPDATE ‘wp_options’ SET ‘option_value’ = “https://www.domain.com” WHERE ‘wp_options’.’option_name’ = ‘siteurl’;
18) UPDATE ‘wp_options’ SET ‘option_value’ = “https://www.domain.com” WHERE ‘wp_options’.’option_name’ = ‘home’;
Replace “domain.com” to your site’s domain and “site_db_name” to the name of your database. By the way last 2 queries(17th and 18th) should be edited according to www or non-www address of your site, or you can skip this 2 queries and just visit WordPress Admin and change site URL there.
Yet another important thing. Above list is a minimum for switching your WordPress to HTTPS and in most cases sufficient. Though some urls still could be untouched and you may want to change them too. Just remember not to perform simple find/replace in database’s dump or you could break some things on your site. The reason is that in wp_options table there data from WordPress itself and it’s plugins which is saved in serialized view. For example “option_value” field could have below data:
a:3:{i:0;s:8:”post_url”;i:2;s:42:”http://www.domain.com/blog/archive/post123″;i:3;s:0:””;}
, and if you’ll just replace “http” with “https” then something could stop working on your site. Someone who knows about data serialization will understand the issue, I’ll explain it for others – above string contains data along with data length, so string “s:42” says that next string(which is
“http://www.domain.com/blog/archive/post123” ) has 42 characters and if you’ll replace “http” to “https” in that string then it will have 43 characters instead of specified 42, so you’ll need also to edit
“s:42” to be “s:43” or some functionality could be broken. So just be accurate with such data or even try find and edit such data in WordPress Admin where data will be shown already unserialized and saved back in serialized view by WordPress.
Cheers!