Manual Migration of a WordPress Database

WordPress is one of the most widely used content management systems on the planet. Did you know that WordPress powers 26% of the world wide web? It holds approximately 59.4% of the CMS market, available in 62 languages, and is more visited than Twitter. (Stats from Digital.com)

I spend a lot of time moving customers to 45Squared’s cloud managed hosting. There are a bunch of tools that can help you complete this but sometimes its just good to understand the good old fashion way of doing things with manual MySQL queries.

NOTE: All of the queries referenced in this post are based upon a default installation of WordPress. It is recommended that you change your table prefix in your installation. While some people think that it provides no benefit, it is always best to use something unique to prevent security breaches. When modifying anything in a database it is very important to back everything up first as making changes can cause extensive damage.

The Queries

Changing Your Site URL

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.

Changing the GUID of Posts

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
  • Date
  • Content
  • Comment Count
  • Title

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

Changing Post Content

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

Modifying META Information

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!

There are many plugins that can assist you with your website migration. They often times make your this process much simpler. It is important to understand what they are doing under the hood. For more information about the WordPress Database Scheme visit this site   (https://codex.wordpress.org/Database_Description)

Do you need help migrating your WordPress website? If so contact 45Squared today for assistance. We would be happy to help you move your website to another host (hopefully us!).

Connect with 45Squared

2018-03-04T11:18:37+00:00