WordPress

Migrating WordPress Database To A New Server

When you are developing any website you will always have different environments for your website, the number of environments you need will depend on the size of the project and how many people are involved in the project.

The main environments you will find in any website project are:

  • Development Server - The developers local machine to development the website.
  • Testing Server - Once development is finished this will be where all testing takes place.
  • User Acceptance Testing Server - Where the client will review the changes to the website.
  • Production Server - The live website.

The two environments you will always need to have is a development environment and a production environment. You will normally only have these two environments if the client doesn't need to view the site before it goes live. This means that once the developer has finished working on the site they can simply put this onto the production server. These are normally for the very basic of projects where the developer can simply test on their local development server before going live.

Database Config

In a previous article we discussed about how you can change your wp-config.php file to handle multiple environments and change the database settings of the content. The way we do this is by adding a switch in the wp-config.php to change the database config depending on the server name.

switch( $_SERVER['SERVER_NAME'] )
{
    case 'dev':
 	// Dev Environment
	define( 'DB_NAME', 'project_dev' );
	define( 'DB_USER', 'project_dev_user' );
	define( 'DB_PASSWORD', 'password' );
	define( 'DB_HOST', 'localhost' );
	
	define( 'WP_HOME', 'http://project.dev');
	define( 'WP_SITEURL', WP_HOME);
    break;

    case 'uat':
	// UAT Environment
	define( 'DB_NAME', 'project_uat' );
	define( 'DB_USER', 'project_uat_user' );
	define( 'DB_PASSWORD', 'password' );
	define( 'DB_HOST', 'localhost' );
	
	define( 'WP_HOME', 'http://project.uat'); 
	define( 'WP_SITEURL', WP_HOME);
    break;

    case 'live':
	// Production Environment
	define( 'DB_NAME', 'project_live' );
	define( 'DB_USER', 'project_live_user' );
	define( 'DB_PASSWORD', 'password' );
	define( 'DB_HOST', 'localhost' );
	
	define( 'WP_HOME', 'http://project.com'); 
	define( 'WP_SITEURL', WP_HOME); 
    break;
}

Export Database

To export the database you can either use the command line and export a file from there by using the following command

mysqldump -p -u username -h hostname database_name > dbname.sql

Or you can use PHPMyAdmin GUI and export the database from here, if you have PHPMyAdmin installed on your server then you'll most likely be able to get to it by going to http://example.com/phpmyadmin, where you will be presented with a login screen to get access to your database.

phpmyadmin

Once you have logged in you can export the database by clicking on the export button in the top menu.

db-export

This screen will download a sql file of all the data and table structure in the database. You can now use this database file to import into the new database on the other server with all the content and settings from the test environment.

Import Database On New Server

To import your database file into the new server you again will have two options either the command line import or through the PHPMyAdmin GUI. The problem that you might face with using PHPMyAdmin is the upload limit on your application, by default this is most likely set at 2MB therefore if your database file is more than this you will have to use the command line to import your database file.

To import using the command line you need to upload your SQL data file to your server you can do this by using FTP and place your data file in a non-public folder.

Then you can use the following command to import the file into your database.

mysql -p -u username -h hostname database_name < /var/data/backup/dbname.sql

Change Domain In Database

As we were working on our local machines the domain we use in the database will be different than the one used on the other servers. Therefore when you were testing on one environment you will have your development domain in the database for things such as links in the posts, post guids, HOME_URL, SITE_URL, user meta, comments.

This means you will need to replace the old domain and with your new domain, the quickest way of doing this is by running a MySQL search and replace on the old domain to the new domain. The following SQL queries can be ran on your database to change the domain URL in the database all you have to replace the olddomain.com with your test environment domain and replace the newdomain.com with your name server domain.

UPDATE `wp_commentmeta` SET `meta_value` = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_comments` SET `comment_content` = replace(comment_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_options` SET `option_value` = replace(option_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_posts` SET `post_content` = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_posts` SET `guid` = replace(guid, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_postmeta` SET `meta_value` = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE `wp_usermeta` SET `meta_value` = replace(meta_value, 'olddomain.com', 'newdomain.com');

Multisite Tables

Within a multisite install you will have additional tables that you need to change the domain one, therefore if you have this setup you can use the following queries.

UPDATE `wp_blogs` SET `domain` = replace(domain, 'olddomain.com', 'newdomain.com');
UPDATE `wp_site` SET `domain` = replace(domain, 'olddomain.com', 'newdomain.com');
UPDATE `wp_sitemeta` SET `meta_value` = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

All-in-One WordPress Migration Plugin

banner-772x250 You can use the plugin All-in_one WordPress Migration to help move your theme files, media files, plugins and export your database.

The plugin allows you to export your database, media files, plugins, and themes. You can apply unlimited find and replace operations on your database and the plugin will also fix any serialization problems that occur during find/replace operations.

This plugin does not yet support multisite exports but it will do in up-coming version.

Download All-in-One WP Migration Plugin

Back to top ^

Join Paulund

  • Access 100s of premium tutorials and downloadable content
  • Members content consists of premium WordPress plugins, CSS packages, jQuery packages, tutorial demo files and templates for 100s of web development tutorials
  • In-depth development tutorials
  • Priority tutorial requests
  • No ads

Join Paulund