Converting a WordPress database to run under a different domain name

Download a zip file containing convert-wordpress.zip.

 

I do all my plugin and theme testing/development plus most of my site development (custom shortcodes, css, templates etc) on a testing server that runs on my laptop. It’s often very helpful to work with a fresh copy of an online database to see how my code interacts with real data, or just to perfectly synchronize my testing server with changes I’ve made to an online wordpress installation.

But an unconverted WordPress database will not run properly on a testing server if the testing server uses a different domain name for viewing the site (almost anything you click on takes you back to the live site, including login).

I could tweak my hosts file (or perhaps my router) to run the site on my testing server using the same domain name as online, but then I would have to make another tweak every time I wanted to look at pages on the live site. Instead, I developed a simple conversion program that changes the domain name in three critical tables, which allows me to alter a database copied from a live site so it will run under a different domain name. That way I can view pages on both the live and test sites simultaneously (plus I can switch from the live server to the same page on my testing server (or vice versa) by just changing .org to .loc in my browser’s url bar).

Convert-WordPress provides a convenient method to define and run WordPress database conversions.  The site definition array allows you to define conversion parameters (domain names and database connection info) for as many sites as you need to convert.  Convert-WordPress displays a clickable list of databases to convert.

To facilitate running Convert-WordPress I defined a domain named debug.loc on my testing server so that I can run Convert-WordPress by going to http://debug.loc/convert-wordpress.php.  I didn’t try to write Convert-WordPress as a plugin because I thought it might be problematic to convert WordPress while running WordPress.  Plus I find it more convenient to synchronize my testing server in bulk (i.e., backup several sites, restore several databases to the testing server, click through each link in the conversion program).

Steps for using this conversion program

Setup (things you only have to do once)

• Setup a testing server if you don’t already have one (I run open source Apache, php, and MySQL on my laptop)
• Make a copy of your site files (including themes, plugins, uploads) accessible on your testing webserver at http://yourdomain.loc
• Create a database and database user on your testing MySQL server using the same credentials as your live site
• Edit the site definition array in convert-wordpress.php to add a conversion definition for each site you may want to convert.

Converting a database (I do this frequently so that I’m always testing with a current database – it’s very quick)

• Make an SQL backup of your live database
• Import the SQL backup to the appropriate database on your testing server
• Go to http://debug.loc/convert-wordpress.php (or wherever you put convert-wordpress.php) with your browser and click on the appropriate link to convert the database

That’s it.  You should be able to view and interact with your site running on the testing server at http://yourdomain.loc (or whatever it is named on your testing server).

Notes on Using convert-wordpress.php

[This article assumes you are familiar with setting up/using a testing server appropriate for running WordPress. There is plenty of information online describing various means and methods for setting up a testing server. I have Apache (with php) and MySQL servers installed on my laptop.]

The site definition array is a collection of arrays defining how to convert the site, and which also includes the connection credentials for the MySQL server.  It is near the top of convert-wordpress.php.  Each site definition array looks like this:

$sites_to_convert[] = array (
‘from’ => ‘yourdomain.org’,
‘to’ => ‘yourdomain.loc’,      [‘to’ name must be the same length as the ‘from’ name]
‘table_prefix’ => ‘wp’,
‘db_server’ => ‘localhost’,
‘db_server_username’ => ‘your_username’,
‘db_server_password’ => ‘your_password’,
‘db_database’ => ‘your_database’);

Edit the sample arrays as appropriate for your setup and domain names. You can include as many definition arrays as you like – a clickable link will be generated for each definition. Note that it’s very important that the ‘to’ domain name be the exact same length as the ‘from’ domain name [if it is a different length the conversion will corrupt some option values because the option encoding for array values is position dependent].

The conversion program doesn’t try to do anything with custom tables created by plugins, though I haven’t yet encountered a plugin where values in a custom table need to be changed. If you have such a plugin installed, you may need to add queries as needed.

While this code was developed primarily to facilitate moving a live database to a testing server, it will also work in the other direction.  Or to convert a WordPress database for online use under a different domain name.  The limitation with the current code is that the new domain name must be the exact same length as the old, else options will be frelled.  When I finish a few other projects I’ll update ConvertWordPress to modify ‘serialized’ options by re-writing them rather than simply replacing vlaues, which will allow the new domain name to be any length.

---

 

Screen Shots