UPDATE wp_options SET option_value = replace(option_value, 'http://OLDDOMAIN.com', 'https://NEWDOMAIN.com') WHERE option_name = 'home' OR option_name = 'siteurl';
If we read this query as if we are reading a book it is pretty easy to understand what is going to happen. In our WP_OPTIONS table we are going to replace any value that matches “http://OLDDOMAIN.com” with “https://NEWDOMAIN.com” when the option is either “home” or “siteurl”. This query is the most important query when manually doing a migration. These two options that are going to be changed are the what tell your site what domain it resides on.
UPDATE wp_posts SET guid = replace(guid, 'https://OLDDOMAIN.com','https://NEWDOMAIN.com');
WP_POSTS contains all the information about your posts including:
- Post Author
- Comment Count
And much more. When we run this query we are going to set the default URL to be our NEWDOMAIN.com. If you didn’t run this query and you went live with your new site all of your posts would redirect to your OLDDOMAIN.com
UPDATE wp_posts SET post_content = replace(post_content, 'https://OLDDOMAIN.com', 'https://NEWDOMAIN.com');
This query looks very similar to the previous query in WP_POSTS. The biggest difference here is that we are changing the “post_content” option. This will change any reference to OLDDOMAIN.com to NEWDOMAIN.com
UPDATE wp_postmeta SET meta_value = replace(meta_value,'https://OLDDOMAIN.com','https://NEWDOMAIN.com');
Last but not least. WP_POSTMETA. Post meta contains all the meta tag information for your posts. If you are familiar with WordPress, you know that everything is considered a post, most importantly, images. Without running this query all of your images would still be referenced to OLDDOMAIN.com. It is very important that you change this to match your NEWDOMAIN.com.
I often use this same query when migrating static images to Amazon S3 Buckets for use with Amazon CloudFront. More information on this later!