When designing and developing WordPress websites for clients, the most common approach is to set up a local version of the site on WAMP/XAMP, then quite often you would create an exact copy of this local site onto a test or development server somewhere online where the client can see it “in action”. Once the built site is all approved by the client (after numerous tweaks to the already approved design no doubt!!) the finished site is moved from the development/test server to the live web server – where it will sit out the rest of its days.
Working with WordPress, it is quite simple to export the database from your localhost and import it into a new empty database on another server (using whatever tool you use – such as phpMyAdmin etc). The single main job you must do when moving the WordPress database from one server to another is to update two field values within the wp_options table. These are “siteurl” and “home”. Once you set these values to your new domain address – you should be cooking. Failure to set these to the correct domain may mess up your navigation, and any reference to your base URL for including images, scripts etc.
To do this manually, read on; otherwise skip down to the next section of this tutorial to learn how to do this, along with another couple of tweaks using a simple SQL statement…
Manually update siteurl and home using phpMyAdmin
Log into phpMyAdmin and open up your WordPress database. Click on the table “wp_options” and then click edit on row with option_name title siteurl (usually it will be row 1).
Change the value to your new domain URL, e.g. from http://localhost/testsite to http://www.newdomain.com.
Save this value and then repeat on row “home” (usually on page 2 in phpMyAdmin).
There are two more tasks which I like to do when moving any WordPress database from server to server, such as from localhost to live domain. These tasks are to replace any record of the old domain URL with the new one within guid and any mention of it within post/page content. To do this, along with replacing the siteurl and home field value – read on!
Three steps to update your WordPress database after transferring it to a new domain
I do this from phpMyAdmin, but you could also run the commands when logged into your MySQL server. To do this from phpMyAdmin, make sure you are within your WordPress database (and not within any table of that database), and then click on SQL tab.
From here enter the following SQL commands, one by one, remembering to replace the two URL strings with your own old domain and new domain. To run each query, click on Go.
UPDATE wp_options SET option_value = replace(option_value, 'http://www.olddomain.com', 'http://www.newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl';
This will set the siteurl and home values, which is the same as doing it manually as per above. Next I like to clean up the guid structure with the following command:
UPDATE wp_posts SET guid = replace(guid, 'http://www.olddomain.com','http://www.newdomain.com');
And finally, to clean up the post and page content which included static links to internal pages, or links to images, run the following command which will replace any reference of your old domain with your new one:
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.olddomain.com', 'http://www.newdomain.com');
These commands take seconds to complete and really speed up the transfer of a WordPress site from one domain to another – freeing up time to concentrate on more pressing matters!