Software Developer

MySQL Queries To Change WordPress From HTTP to HTTPS In The Database

There is some bad advice going around regarding updating URLs in the WordPress database when migrating your WordPress site from HTTP to HTTPS. There are tons of examples of MySQL queries that do a “find and replace” of the URLs in your WordPress database, and they’ll change the URLs from HTTP to HTTPS. The bad advice that I’m referring to is when people suggest that you update the 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 from WordPress.org).

Yet, many articles out there are offering a list of queries to update the URLs in your WordPress database, and they have this bad line:

(DO NOT RUN THIS LINE:)

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

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

Here is my list of MySQL queries to safely change the URLs in your WordPress database from HTTP to HTTPS.

Prerequisite: Change all instances of www.yoursite.com to your own. If you didn’t have the www as part of your WordPress Address (URL) in the WordPress Settings, then leave out the www here. For example, some people just use http://yoursite.com, without the www. Also, if you have a custom table prefix in the WordPress database, something other than the default wp_, then you must change all the instances of wp_ to your own table prefix.

My list of MySQL queries is organized into eight steps:

  1. Update any embedded attachments/images that use http.

    This one updates the 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 one takes care of 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 one 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 one updates the URL for 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 any “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 that there are no remaining http URLs for your site in the wp_posts table, except the GUID URLs.

    You must replace WP_DB_NAME, near the beginning of the query, with the name of your database.

    This will confirm that nowhere in the wp_posts table is there a remaining http URL, outside of the GUID column. This ignores URLs in the GUID column.

    This query only searches; it does not replace anything, nor make any changes. So, this is safe to run. It’s a safe and quick way to check the wp_posts table while ignoring the guid column.

    This SQL query should return an empty set. That would mean that it found no http URLs for your site. (This is all just 1 query. It’s 1 very, very long line.)

    Remember to replace WP_DB_NAME, near the beginning of the query, with the name of your 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 move to the wp_comments table. This changes any comment author URLs that point to the http version of your site. This is in case you’ve ever replied 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 there are any links in the comments that are linking to an 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 move to the wp_postmeta table. This takes care of any custom post meta 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 to the wp_options table. Update the “WordPress Address (URL)” and “Site Address (URL).” You can simply change these in the WordPress dashboard –> Settings page, rather than with these queries. You can just go to the Settings page and change both of these fields so that they start with https.

    But, here are the queries if you prefer to do it this way.

    For the WordPress Address URL, you may have to modify www.yoursite.com. If you have WordPress installed in some other directory, then modify this according to your own WordPress URL. For example, some people have WordPress installed in a subdirectory named “blog” and 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 one will update the Site Address URL (this is the home page of your site):

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

By

We've 25 Responses

  1. April 20th, 2017 at 5:42 am

    Great post Isabel and thanks for the advice on the GUID field.

    One other table I updated was the wp_term_taxonomy -> description column. This can contain category descriptions and html can be used so they can also contain hardcoded urls. I’ve added some sample code.

    UPDATE `wp_term_taxonomy` SET `description` = REPLACE(description, 'http://www.yoursite.com', 'https://www.yoursite.com') WHERE meta_value LIKE '%http://www.yoursite.com%';
    
    avatar
  2. February 13th, 2018 at 11:34 am

    Thank you, this was a real life saver. I wish WordPress wouldn’t hard code all the URL’s in the database, makes it a right pain to switch from http to https but your article came to the rescue 🙂

    avatar
    • March 12th, 2018 at 4:31 pm

      Hi,

      there’s currently a plugin for doing that:

      https://wordpress.org/plugins/wp-migrate-db/

      You can untick “Replace GUIDs” under “Advanced options” and that’s it.

      Anyway as far as I understand GUIDs doesn’t have to be changed to avoid feedreaders to display the same content to user’s reader again as it is fresh content. So it is not a big deal with small sites or sites that has never been live. Am I right?

      Cheers,

      avatar
  3. May 10th, 2018 at 11:41 am

    This was exactly what I was looking for! I tried it on my website and it worked perfect. No SQL errors 🙂
    Thank you Isabel!!!

    avatar
  4. August 26th, 2018 at 10:20 pm

    Hi, thanks for this. However, I was unable to run 1.2 and 2.2 (regarding the single quotes). I got the following error each time..

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘http://www.yoursite.com%’ AND (post_content REPLACE(post_content, ‘href=” at line 1

    avatar
  5. September 25th, 2018 at 11:32 am

    wouldn’t the proper way be to replace ‘http://’ with ‘//’? – this lets the browser decide what to do

    i’m getting ready to do this on my site and would appreciate any advice regarding ‘//’ and if the tutorial above would still be valid

    avatar
  6. October 17th, 2018 at 2:03 pm

    Isabel, thank you for making this information available. It is much appreciated.
    I have a multisite that has to do the change to https. And in point 8 you say that the change can be done in the WordPress dashboard –> Settings page, rather than with the queries. Which is true for an ordinary WP-site.
    However, I notice that it is impossible to do any changes of the main address in the settings on the main web-page for the multisite network. Which I have been told to change.

    What might lock this? Are there even other parameters in a multisite to consider in order to make the change in the settings of the main site? Or maybe in the DB?

    avatar
  7. December 4th, 2018 at 10:27 am

    Hello, thank you for your help. But I would like to know that I can’t update any “Pinged link”. It has the error when I just copy and paste your code.

    avatar
  8. December 14th, 2018 at 7:50 pm

    Hey Isabel.
    Really appreciate the details and explanation on the GUID, as you said everyone is saying “just replace the GUID entries” too.

    I am on WP 4.9.8 and the commands all run great to step 4, step 1 changed 124 rows but when i run step 4 it shows that the post_content table data still has over 100 items that cannot be altered by the SQL commands it seems. There is a plugin that sees the changes but i prefer to do this manually so i can track changes.

    The row field data that the script is not finding looks like this (the data domain has been altered for the example):
    [et_pb_section bb_built=”1″ admin_label=”Header” bg_img_1=”http://mydomain.space/wp-content/uploads/2018/11/political-candidate-25.png” background_size_1…

    I am using the Divi theme which is the [et] stuff you see you see. I cannot trace why the stuff inside the Divi blocks is being ignored as it seems to change the http to https without issue.

    The above issue is only hanging around in the post_content table field.

    Hope that makes sense. I will continue to look but thanks for any feedback you can offer.

    avatar
    • December 18th, 2018 at 10:26 am

      My queries only change the URLs for images that are embedded with an HTML image tag, so it looks for the src attribute:

      src="http://mydomain..."

      It doesn’t find your image URL because your shortcode sets the image with a bg_img_1 attribute, like this:

      bg_img_1="http://mydomain..."

      This SQL query should work for you:

      UPDATE `wp_posts` SET post_content = REPLACE(post_content, 'bg_img_1="http://www.yoursite.com', 'bg_img_1="https://www.yoursite.com')
      WHERE post_content LIKE '%bg_img_1="http://www.yoursite.com%';
      
      avatar
      • December 18th, 2018 at 4:25 pm

        Hey Isabel
        Thanks so much for sharing more of your expertise, this i did find a workaround but i will use this on my next migration for sure…it makes total sense.

        Appreciate so much your help.
        Thanks
        Mark

        avatar

Questions and Comments are Welcome

Your email address will not be published. All comments will be moderated.

Please wrap code in "code" bracket tags like this:

[code]

YOUR CODE HERE 

[/code]