You’ve developed a client site locally, or on a “dev” domain and now you want to move it to the production domain. Obviously, you’re going to copy the WordPress directory, with all the plugins and themes and uploads and then you’re going to also transfer the database.

But your database is full of references to the development site. When you attempt to run it, all sorts of things will break. You’ll first notice problems when WordPress just insists on redirecting you to the development domain. Then, once you’ve fixed that, you’ll notice the really nasty problems, when things almost work, but not quite right.

To avoid problems like this, you have two options: use a third-party plugin for site and database migration, or do the whole process manually.

Using third-party plugins to migrate your site

There are a lot of WordPress plugins that can help you to succesfully migrate your site to a new domain or location. Here are two that we have used with good results:

  • WP Migrate DB – this plugin helps you to migrate your database
  • Duplicator – this plugin helps you to migrate your entire site, including the files and the database

Manual migration of the WordPress site

If you decide to migrate your site manually, you need to make sure to do it properly, so there will be no issues after the move. Let’s go through these issues one by one and understand what needs update and how to do it.

1. Telling WordPress that the site’s location has changed

WordPress stores the site location in the options table. You can see it when you go to Settings -> General.

Site URL settings
Site URL settings

If the URL there is not the URL of the production site, WordPress will always redirect you to the development URL. This data comes from the options table, but I find that the easiest (and safest) way to “fix” it is by entering the correct URL in the wp-config.php file.

define('WP_HOME', 'https://toolset.com');
define('WP_SITEURL', 'https://toolset.com');

These two statements tell WordPress to disregard the values in the database and use the home and site-URL values that you specify in the PHP. It is especially convenient if you frequently move from production to development. By forcing it in the PHP, you avoid the need to constantly edit the database.

2. Updating URLs in posts and custom fields

The larger part of the migration task is to update all the posts and custom fields to the URL of the production site.

If you remember, when you insert a link or an image, WordPress will store it with its full path. That’s great when you’re developing the site, but all these absolute references have to update when you’re moving to the new site. Otherwise, all inline lines and images still go to the development server.

It’s going to be a nasty problem because, in first sight, things appear working. Only later, you’ll discover that you’re actually linking to the wrong places and loading images from a site that might go offline at any time.

Warning: We’re going to bulk-edit the database next. Don’t do this before you’ve made a backup of the entire database.

Use a database editing tool such as PHPMyAdmin. Navigate to the database right database, if you have several. You can back up the database using PHPMyAdmin, using the ‘export’ tool.

These MySQL statements will replace URLs from development to production. Obviously, you need to replace the sample URLs with your actual ones. Also, when you copy/paste from here, be sure that quotes are all regular, single-quote characters. In English keyboards, they are located next to the ENTER key, together with the double-quote chars. The quotes that you copy from this page will most likely be the wrong character.

  • UPDATE wp_options SET option_value = REPLACE(option_value, 'http://devdomain.local', 'http://livedomain.com') WHERE option_value NOT LIKE '%{%';
  • UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, 'http://devdomain.local', 'http://livedomain.com') WHERE meta_value NOT LIKE '%{%';

Hint: After you run each of these queries, PHPMyAdmin will tell you how many entries were updated. Check that this number makes sense.

I’ve excluded keys that have the character { in them as they would be part of serialized arrays. If you replace parts of serialized data, the entire entry would become corrupt. These queries will replace only URLs outside of serialized arrays.

3. Updating file paths from development to production sites

We’re almost there. The last thing is to change file directories. Many time, when you upload images, audio or video, WordPress will save the full path of these items in the database – normally in custom field values. You need to replace them.

  • UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '/home/dev/wordpress ', '/home/prod/wordpress ') WHERE meta_value NOT LIKE '%{%';

Again, we’re making sure that only ‘regular’ entries are replaced and not serialized data.

4. So, what about serialized arrays?

There’s no way to update serialized arrays using just MySQL queries. You need to use PHP, load the arrays, find the values, replace them and serialize back.

This is why we encourage developers to avoid using serialized arrays to hold user-data. Types doesn’t use serialized arrays for any data that might need updating when sites move. For repeating fields, we use multiple keys instead of one key with serialized data.