Request to change HTTP links to HTTPS for WordPress in MySQL database

Migrating a site from HTTP to HTTPS and the following problems

There are some bad tips for updating URLs in the WordPress database when moving a WordPress site from HTTP to HTTPS. There are many examples of MySQL queries that search and replace URLs in the WordPress database, and they will change URLs from HTTP to HTTPS. The bad advice I’m talking about is when people suggest updating GUID URLs.

The URLs in the GUID column of the wp_posts table should NOT be changed when moving your site from HTTP to HTTPS (see This on WordPress.org).

However, many articles offer a list of queries to update URLs in the WordPress database, and they have this bad command that you MUST NOT use!:

(DON’T USE THIS COMMAND!)

UPDATE wp_posts SET guid = replace(guid, 'http://mysite.com','https://mysite.com');

Also, never run a “find and replace” MySQL query that affects the entire wp_posts table of the WordPress database, as this will update the GUID of the URLs.

Here’s my list of MySQL queries to securely change URLs in the WordPress database from HTTP to HTTPS.

Pre-condition: change all instances of www.yoursite.com to your own. If you didn’t have the www part of your WordPress address (URL) in your WordPress settings, leave the www here. For example, some people just use http://yoursite.com, without www. Also, if you have your own table prefix in your WordPress database, something other than the default wp_ value, you should change all instances of wp_ of your own table prefix.

SQL queries for the WordPress database

My MySQL query list consists of eight steps:

1. Update any embedded attachments/images that use http. This updates src attributes that use double quotes:

   UPDATE `wp_posts` SET post_content = REPLACE(post_content, 'src="http://www.yoursite.com', 'src="https://www.yoursite.com') WHERE post_content LIKE '%src="http://www.yoursite.com%';

   This covers any src attributes that use single quotes:

   UPDATE `wp_posts` SET post_content = REPLACE(post_content, 'src='http://www.yoursite.com', 'src='https://www.yoursite.com') WHERE post_content LIKE '%src='http://www.yoursite.com%';

2. Update any hard-coded URLs for links. This updates the URL for href attributes that use double quotes:

   UPDATE `wp_posts` SET post_content = REPLACE(post_content, 'href="http://www.yoursite.com', 'href="https://www.yoursite.com') WHERE post_content LIKE '%href="http://www.yoursite.com%';

   This updates the URL of href attributes that use single quotes:

   UPDATE `wp_posts` SET post_content = REPLACE(post_content, 'href='http://www.yoursite.com', 'href='https://www.yoursite.com') WHERE post_content LIKE '%href='http://www.yoursite.com%';

3. Update “pinged” links:

   UPDATE `wp_posts` SET pinged = REPLACE(pinged, 'http://www.yoursite.com', 'https://www.yoursite.com') WHERE pinged LIKE '%http://www.yoursite.com%';

4. This step is just a confirmation step to make sure there are no remaining URLs in the wp_posts table for your site, except GUID URLs. You should replace WP_DB_NAME at the beginning of the query with the name of your database. This will confirm that there is no remaining http URL anywhere in the wp_posts table outside of the GUID column. This ignores the URLs in the GUID column. This query only searches; it doesn’t replace anything or make any changes. Thus, it is safe to run. This is a safe and fast way to check the wp_posts table, ignoring the guid column.

   This SQL query should return a blank set. This means that it did not find the http URL for your site. (It’s only 1 query. It’s 1 very, very long string.)

   Remember to replace WP_DB_NAME at the beginning of the query with the name of the database.

   SELECT * FROM `WP_DB_NAME`.`wp_posts` WHERE (CONVERT(`ID` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_author` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_date` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_date_gmt` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_content` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_title` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_excerpt` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_status` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`comment_status` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`ping_status` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_password` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_name` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`to_ping` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`pinged` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_modified` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_modified_gmt` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_content_filtered` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_parent` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`menu_order` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_type` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`post_mime_type` USING utf8) LIKE '%%http://www.yoursite.com%%' OR CONVERT(`comment_count` USING utf8) LIKE '%%http://www.yoursite.com%%');

5. Now we go to the wp_comments table. This changes the URLs of the comment authors pointing to the http version of your site. This is in case you ever responded to a comment while your URL was pointing to http.

   UPDATE `wp_comments` SET comment_author_url = REPLACE(comment_author_url, 'http://www.yoursite.com', 'https://www.yoursite.com') WHERE comment_author_url LIKE '%http://www.yoursite.com%';

6. This updates the content of the comments on your site. If the comments link to the http URL on your site, they will be updated to https.

   UPDATE `wp_comments` SET comment_content = REPLACE(comment_content, 'http://www.yoursite.com', 'https://www.yoursite.com') WHERE comment_content LIKE '%http://www.yoursite.com%';

7. Now we go to the wp_postmeta table. This refers to any personalized post that points to the http version of your site.

   UPDATE `wp_postmeta` SET `meta_value` = REPLACE(meta_value, 'http://www.yoursite.com', 'https://www.yoursite.com') WHERE meta_value LIKE '%http://www.yoursite.com%';

8. Now we move on to the wp_options table. Update the “WordPress address (address)” and “Site address (URL)”. You can simply change them on the “WordPress Control Panel” -> “Settings” page, not with these queries. You can just go to the settings page and change both of those fields to start with https. But here are the requests if you prefer to do it that way. For the WordPress URL, you may need to change www.yoursite.com. If you have WordPress installed in a different directory, change it to match your own WordPress URL. For example, some people install WordPress in a subdirectory named “blog,” so their WordPress address would be https://www.yoursite.com/blog.

   UPDATE `wp_options` SET `option_value` = "https://www.yoursite.com" WHERE `wp_options`.`option_name` = 'siteurl';

   This is an update to the site URL (that’s the home page of your site):

   UPDATE `wp_options` SET `option_value` = "https://www.yoursite.com" WHERE `wp_options`.`option_name` = 'home';