6 Essential WordPress SQL Search and Replace Commands

6 Essential WordPress SQL Search and Replace Commands

I have a txt file with this list of 6 SQL search and replace commands (the same commands used at Custom WordPress SQL Search and Replace Commands):

1. SQL Search & Replace of Post Content
update wp_posts set post_content = replace(post_content,'http://example.com','https://example.com');

2. SQL Search & Replace of Comment Content
update wp_comments set comment_content = replace(comment_content,’http://example.com’,’https://example.com’);

3. SQL Search & Replace of Comment Author URL
update wp_comments set comment_author_url = replace(comment_author_url,'http://example.com','https://example.com');

4. SQL Search & Replace of Post Meta Data Content
update wp_postmeta set meta_value = replace(meta_value,’http://example.com’,’https://example.com’);

5. SQL Search & Replace of Category Meta Data Content
update wp_termmeta set meta_value = replace(meta_value,’http://example.com’,’https://example.com’);

6. SQL Search & Replace of WordPress GUID URL
update wp_posts set guid = replace(guid,’http://example.com’,’https://example.com’);

First I make a backup the entire website or at least the WordPress database in case something goes wrong.

I log into Virtualmin and go to the edit database screen and check what I set the WordPress table prefix to (the wp_ bit). I change all the instances of wp_ above to whatever the database I’m modifying is using. I change all instances of example.com to whatever the domain I’m converting to https.

I go to the relevant databases SQL execute screen and copy and paste the first SQL command above (the Post Content one) into the SQL execute form and click the execute button. I check a WordPress Posts content I know was using http URLs to confirm it’s updated, if it worked I go back to the SQL execute form and copy and paste the second SQL command above and repeat the process until all 6 SQL commands have been run.

I browse through the WordPress site looking for problems, unsecure webpages and if I got them all I’m done.

In practice it takes around 30 minutes to update all http links this way. Could speed the process up, but better safe than sorry.

Continue Reading Change WordPress Site from HTTP to HTTPS