Moving a WordPress Database to a New Server
Posted in Websites on February 27th, 2009 by Jason – Be the first to commentThis past weekend I had the joy of moving two different installations of wordpress to a new server. The first one, which is this blog, moved from my old godaddy.com hosting account to a mediatemple grid server. The second blog (thebaileyblog) which my wife and I use, moved from a subdomain of sidewaysgravity.com to its own domain.
When moving a wordpress blog from one location to another there are a couple steps you will need to take to make sure that everything continues to work. Wether you are moving a blog to a new server with the same domain name or a new one, the steps stay the same, with only a few extra steps if the domain name is changed.
There are a couple things that you will need when you are preparing to move your blog. You will need an ftp program to be able to retrieve files from your current host and to put them onto the new server. It is also extremely useful to have access to phpMyAdmin and since that is what I used, I will use directions for it. If you are not sure how to access phpMyAdmin, you can contact your hosting company and they can help answer any questions you may have. For Mac users, CyberDuck is a great ftp application.
One thing I cant stress enough is to BACKUP you wordpress blog. I have moved my handfull of blogs and everynow and then something happens and I would be in a world of hurt if I didnt have my backup. You will want to backup not only your database, but also all the files for the blog. Fortunately when you are moving the database between servers, you have to back up the files to do the transfer anyways so you dont have to spend any extra time for the backups. Just dont delete your files until you are completely done and fully tested.
The first step is to login to the existing server with your ftp client and download all your files. I like to create a folder on my desktop and load all the blog files into it. Once you have downloaded all your files, depending on the size of your blog and number of photos this could take a while, you will want to login to your database to backup the rest of the blog. You will need to choose your database and select the export option. From there you can export the entire database as an sql file and save it. This is all you should need to do to backup all of your current blog.
Now to loading your blog onto the new server. First you will need to setup your new database. Make sure you write down your connection information such as your database name, user name and password. Some hosting also gives a specific connection address to databases such as godaddy and mediatemple (gs). Once you finish setting it up you will want to login to phpMyAdmin. Here you have two options, once is to copy and paste the entire sql file that you backed up previously into the sql page (there should be a sql tab). The other option is to select the import function and to import the file. I find that copying and pasting the sql file seems to work the best.
The following step should only need to be done ONLY if you are moving your blog to a NEW domain such as www.myoldblog.com to www.mynewblog.com or if you are changing subdomains. If you are only changing your server but maintaining the same domain name, you can skip these steps and procede to upload your files via ftp.
Anytime you add a photo to your blog, wordpress will store the address to the file with an absolute url, which means once you remove the files off your old server, all those images will no longer work in your new blog. There are also a couple other settings where wordpress stores the blog url and it will need to be changed. The first time I transfered a blog, I went through the database and changed out the urls one post at a time. Since then I have learned a better way. While logged into your database, open the sql statement window and use the following code to update find and replace every instance of the old blog url and change it to the new.
This code will update the site url and home page that is set for your blog. This must be set to the new blog domain for your wordpress to work.
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldblogdomain.com', 'http://www.newblogdomain.com');
Next, you will want to update all the image or any file paths that you have added to your blog.
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldblogdomain.com','http://www.newblogdomain.com');
Finally, the following code can update any links within your blog posts that may point to another area of your site. You may or may not want to update this code. Although I dont use many internal links, I still run this just incase I have one or two.
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldblogdomain.com', 'http://www.newblogdomain.com');
Finally as a note, their is one other area where data from your old server is stored. In the wp_postmeta table and meta_value field, the actual server path to images and files is stored. Even though your server path will most likely change when you move to a new server, you do NOT need to update this code. This is one area that can cause all of your images to display at full size and not as a thumbnail as intended. Unfortunately I found this out the hard way. Many wasted hours. I still do not fully understand the reasoning behind this, but I know that your blog images will display properly if you dont mess with these fields.
Finally you are ready to upload the files you downloaded to your destop onto the new server. Before you do this, you will need to open up the wp-config.php file and add your new database information. Once the upload is completed you should be ready to go! If you have troubles along the way or if you know why you cant change the absolute paths held in the meta_value field I would love to know why.
