Part of my plans for this year is to cut back the number of websites I run. At the time of writing this post I have over thirty. For me, this is way too many.
Ideally I would like to run a small number of good quality sites and have a few more I can play around with. Sites that don’t really matter if they never get traffic, make money or get banned for any reason.
As part of the streamlining process I’ve converged two sites to make this one. I’ve done this for a few reasons, but the main reason is to bring all the content I’ve written over the past few years into a central hub.
The problem with running multiple websites is everything has to be done over and over again; multiple links to build, multiple plugins to update, multiple identities to manage.
It all gets a bit much.
Anyways, everything is now here. All the posts I’ve written during the past three years now live (or in draft) on this site, with the exception of some crappy or out of date articles that didn’t quite make the grade.
Getting everything here wasn’t quite as difficult as you might think. It took a bit of time, a bit of planning and a few cups of coffee, but in the end everything went without too many hiccups.
How to move and merge WordPress databases from one server to another
Time to cut the waffle and move onto merging the two WordPress databases into one and moving them to a new server.
Step 1 – Back up the web site files
The first thing I did was download all the files from the old domains. The Uploads folder is the most important as this is the folder where all the images are stored. As I’m using WordPress on this site with the same file structure (images stored in chronological order) I knew I’d be able to upload the Uploads folder and everything would be in exactly the same place.
However, there is something you need to do inside phpMyAdmin to make sure the images on the new server are referencing it, and not the old one, but I’ll come to that later.
Step 2 – Back up the database(s)
Whist the files were downloading I logged into cPanel on both sites and made a back-up of each database. This is actually really easy to do. It’s something I’ve now done on numerous occasions, but the first few times were quite scary.
Login to cPanel and look for the “Databases” section, click on the phpMyAdmin icon:
Once inside phpMyAdmin you have direct access to the database. It’s a fascinating place if you like this kind of thing, but very frightening for the vast majority of people. If you’re curious about what a database looks like it’s worth logging-in for a quick nosey, just don’t delete anything.
In the top left hand corner, underneath the phpMyAdmin header (above), there is a list of the databases running on the server. If there’s only one instance of WordPress running there will be only one database listed, and if it’s been installed with Fantastico’s default setting it’s called dbuser_wrdp1.
Click on the database name to access it and look across the top of the screen for the Export tab. Click on it.
In the next window look for the Export section on the left, make sure all the tables to export are highlighted.
The data in these tables relate to everything within a WordPress site; posts, plugins, users – everything. If there is a plugin you’ve been using that you won’t be using on the new server then it should be okay to not export that table (press CTRL and click the table name to remove it from the list (it won’t be highlighted)).
In the image above there are several tables highlighted that I didn’t move across. They are:
- wp_blc_filters
- wp_blc_instances
- wp_blc_links
- wp_blc_synch
- wp_mbp_ping_optimizer
The first four are from one of my favourite WordPress plugins; Broken Link Checker. And the last is from a plugin I use to stop excessive pinging.
I am still using the Broken Link Checker plugin but there was no point bringing the data across to the new server. I just set up the plugin again and run it afresh. MBP Ping Optimizer is the Max Blog Press plugin. I’ve stopped using that and started using cbnet Ping Optimizer instead.
When you have been through the database tables and highlighted the ones you want to transfer, move over to the Structure section and tick the Add DROP TABLE / VIEW / PROCEDURE / FUNCTION box.
Now move down to the bottom section and choose the option for saving the file. I always opt for zipped.
That’s it. Now click Go (bottom right) and save the database back up to your hard drive. The file name is in this format – dbuser_dbname.sql.zip
Step 3 – How to import the database backup
Importing the database back-up file(s) is really simple (the instructions that follow assume you have already created a database, set up WordPress on the new server and unzipped the database back-up file(s)).
Go to the new server and navigate through to the phpMyAdmin section, find the database you want to use and click on the Import tab. Click on Choose File and locate the exported database on your computer and upload it. As I was merging two databases I did this twice and MySQL successfully merged both databases.
If everything has gone according to plan you should now see the content from the old server on the new one.
Step 4 – Upload the old files
The next step is to upload all the backed up files to the new server. As WordPress was already installed I only had to upload the wp-content folder as it contains all the image files, themes and plugins.
Earlier in the post I said there is something you need to do in phpMyAdmin to make sure all the images reference the new server. You will also need to do this to maintain your internal linking structure.
When you add an image to a post in WordPress the image references the absolute URL (eg http://mydomain.com/wp-content/uploads/2011/03/filename.jpg). When the database is transferred this remains in place because the database on the new server is an exact copy of the database on the old server. So, although the content on the new server is working and you can see the images, the images are actually located on the OLD server and not the new one. However, there is a really easy way to change it, all you have to do is run a simple command through MySQL.
Before you do this please make sure you have a back-up copy of your database.
Navigate to the new database and click on the SQL tab. In the large box underneath Run SQL query/queries on database dbuser_wrdp1 (or whatever it says in your instance) paste in this command (but before you hit the Go button you need to make some changes).
UPDATE wp_posts SET post_content = REPLACE (post_content, 'old words', 'new words');
What this query does is it searches through all the data in the wp_posts table and changes the wording according to your specifications. In my case I wanted to change everything that said thebloggingacademy.com and prowebsitestraffic.com to mydigitalinternet.com. As that should make sure all the images are properly referenced and the internal links I’ve created (which also use absolute URLs) are maintained.
UPDATE wp_posts SET post_content = REPLACE (post_content, 'thebloggingacademy', 'mydigitalinternet.com');
UPDATE wp_posts SET post_content = REPLACE (post_content, 'prowebsitetraffic.com', 'mydigitalinternet.com');
It took me a while to find this command and make it work. The reason was the quote marks around the text I wanted to change. If you get it wrong the query will fail.
I think it would be possible to run multiple queries at once, but I ran it twice. Once for each URL.
Next I ran the Broken Link Checker plugin through the site to see if it could find any. It didn’t. Which I was really happy about.
And finally, for this part of the transfer process, I put the site into Maintenance Mode using the Maintenance Mode plugin so I could install the plugins and themes and set up WordPress the way I like it.
And finally…
If all this sounds like too much trouble you could contact your hosting company to ask if they can do it for you. I’m pretty sure they could as long as you are going from one cPanel installation to another cPanel installation.
A final word of warning; this is the process I used to move two WordPress databases from two different servers and merge them onto another. I made plenty of backups along the way and if you try this yourself I highly recommend you do the same.
I hope you find this information useful.
About Stephen
