Moving A WordPress Database Between Live Development & Staging Environments

An efficient and automated deployment setup of your Wordpress development can make your life easier and updates quicker if you know how to streamline it with a few code snippets.


Change Your Site URL Automatically

WordPress already provides a bit of info on how to change the url of your site. Unless your development environment is set to fake the live domain, then everyone can use an automated version of this.

It’s a means to update the hard coded database URL (in two places) without having to go into your database. Put this inside your /wp-config.php file near the top. You’ll find the file in your root directory.

if ($_SERVER['HTTP_HOST'] == 'localhost:7000') {
	update_option('siteurl', 'http://localhost:7000' );
	update_option('home', 'http://localhost:7000' );
} else {
	//update_option('siteurl', 'http://domain.com' );
	//update_option('home', 'http://domain.com' );

Notice the last set is commented because I only want it to update itself once in the live environment. I did that because I’m normally exporting the live database into the development ones the most. If you do the reverse most of the time then you may want to have that a bit more automated. Not having the update_option command run on the live site everytime is preferred because its safer and quicker to do a domain check first.

if ($_SERVER['HTTP_HOST'] == 'localhost:7000') {
	update_option('siteurl', 'http://localhost:7000' );
	update_option('home', 'http://localhost:7000' );
} elseif (get_bloginfo('url') !== 'http://domain.com') {
	update_option('siteurl', 'http://domain.com' );
	update_option('home', 'http://domain.com' );

Check that you’re using the version of your URL that is used in Administration -> Settings -> General in the code above or the matching wont work as expected.

Changing Your URL With A Query

If you feel like that adds an inefficence to your site (assuming you’re getting a few thousand hits a day). Then you probably also have access to your database via PHPMyAdmin. Here is an example of a query for switching between a development enviorment and public version of this site.

UPDATE wp_options SET option_value = replace(option_value, 'http://domain.com', 'http://localhost:7000') WHERE option_name = 'home' OR option_name = 'siteurl';

If I ever deploy the database from development to live I run the reverse of that query:

UPDATE wp_options SET option_value = replace(option_value, 'http://localhost:7000', 'http://domain.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Automatic Database Switching

Again inside of your wp-config.php file in your root directory, modify the definitions (DB_NAME, DB_USER, DB_PASSWORD) as they differ between enviornments.

if ($_SERVER['HTTP_HOST'] == 'domain.com') {
	define('DB_NAME', 'dbnamelive');
	define('DB_USER', 'loginlive');
	define('DB_PASSWORD', 'passlive');
} elseif ($_SERVER['HTTP_HOST'] == 'staging.domain.com') {
	define('DB_NAME', 'dbnamestaging');
	define('DB_USER', 'loginstaging');
	define('DB_PASSWORD', 'passstaging');
} else { //Catch all assumes you're using DEV
	define('DB_NAME', 'dbnamelive');
	define('DB_USER', 'loginlive');
	define('DB_PASSWORD', 'passlive');

Include / disclude the www if thats what you use, and note for SEO you should only be using one or the other. If you are not then temporairly change your IF statement to something like this till it’s fixed or you’ll have problems.

if ($_SERVER['HTTP_HOST'] == 'domain.com' || $_SERVER['HTTP_HOST'] == 'www.domain.com') {

You can use aditional OR opperators “||” if any of your enviornments have matched information. This is what I normally prefer to do. If you don’t have a staging enviornment take out the elseif.

Staging Enviornments

In staging environments you might want to do a few extra things. Turning your blogs “public mode” to off could help reduce the risk of duplicate content found or accidentally submitted to Google. You can do that under the following path: /wp-admin/options-privacy.php. This puts a nofollow on all links and probably applies to quickly configured and non-secured public staging enviornments rather than a local development environment. Unless you’re accessing your staging box from within a single network that’s already protected via firewall. Like your domain name you can toggle it with a query too.

UPDATE wp_options SET option_value = 0 WHERE option_name = 'blog_public';

Then to turn it back on:

UPDATE wp_options SET option_value = 1 WHERE option_name = 'blog_public';

Ideally you’d have a robots.txt file sitting in the root which has dissalow settings on it already.

User-agent: *
Disallow: /

This is equivalant to setting your site on the “I would like to block search engines, but allow normal visitors” setting.

XML Sitemaps

Most sites are running XML Sitemaps. The data for that is stored in wp_options as well however its serialized. Therefore you should be going into your admin area to switch off all the update notifications for the search engines after you sync the database to staging. You’ll find those options here:


Firewall & Host Access For Staging Enviornments

Since I have a lot of networking experience I use firewall and server host configuration files in my staging setup. That allows me control over the servers directory and IP ranges to block access to everyone except certain computers in and outside of the network. This is an ideal because you can skip the XML Sitemaps step and not worry much about unauthorized access. Its worth mentioning but involved enough that I’ll discuss it another day.

Respond: Leave A Comment | Trackback URL

Entrupeners, Subscribe for the lastest tools, tips, and tutorials.

2 Responses to Moving A WordPress Database Between Live Development & Staging Environments

  1. Pauli

    UPDATE wp_options SET option_value = 1 WHERE option_name = ‘blog_public’;

    to turn it back on — you have zero both times.

Leave a Reply

Custom Theme by Rob Malon | Content & Design © 2010 - RobMalon.Com - Chicago, Illinois