You are here: GeekEstate Blog » Blogging, Websites » DIY: How To Move Your WordPress Database To a New Host

DIY: How To Move Your WordPress Database To a New Host

I recently had the pleasure of transferring my real estate site, which runs on WordPress, over to a new host. Moving your site from one host to another can be a tedious process but the process is amplified by 10 if you have to transfer a database of content with it as you do with WordPress. Even so, once you know what you’re doing it can be done pretty painlessly.

A few notes before we get started: This tutorial assumes you have an SQL database of less than 7.1MB, access to a one-click install application for WordPress on the new host and access to PHPMyAdmin.

Follow the simple eight step guide below to learn how to move your WordPress database like a pro:

1. Identify your WordPress database. - Since MySQL databases are used for everything from WordPress to forums you can wind up with a lot of options when you need to download your database. First, download the wp-config.php file from your previous host FTP server and open it with a text editing program.

You should find the following under your MySQL settings:

// ** MySQL settings ** //
define('DB_NAME', 'sample_db'); // The name of the database
define('DB_USER', 'sample_user'); // Your MySQL username
define('DB_PASSWORD', 'sample_pass'); // ...and password

This will give you the info you need to look up your database via PHPMyAdmin.

2. Log into PHPMyAdmin on the server you are moving from. – Once you log into PHPMyAdmin (accessible through cPanel on most hosts) you will find the database names on the left side. Select the database that you want to back up.

3. Export your WordPress database. - Now that you have selected your database you need to go to the top menu bar (below) and select ‘Export’.

1-mysqloptions

Select 'Export' to bring up the export options.

You’ll have a multitude of export options to choose from. Skip past all of this and go straight to the bottom where you’ll see this:

Remember to check 'Save as File'.

Remember to check 'Save as File'.

Check off ‘Save as File’ and click ‘Go’. This will bring up a save window and will save the database on your hard drive as an uncompressed SQL file. You could always zip or gzip it but in the interest of keeping things simple I like to go uncompressed.

4. Install a fresh copy of WordPress on your new host. – Most hosting companies today use cPanel as a backend which means you can use the Fantastico server application to install WordPress in one fell swoop. Just choose the directory, configure your options and install. Hosting companies that use a proprietary backend like Dreamhost offer one-click installs for WordPress as well.

5. Log into PHPMyAdmin on the new host and find the WordPress database you just created. – If you don’t know the name of your new database you can open the wp-config.php file in your FTP app on the new host and look at the MySQL settings using the instructions in step one. Choose the newly installed WP database on the left side so you can begin making the transfer.

6. Drop all the tables in the new database. – Since you want to transfer all of the contents from your backed up database in step three to the new database you just installed you’ll have to empty the new database of its content first. Select ‘Check All’ to highlight all of the tables.

Check all tables and select 'Drop'.

Check all tables and select 'Drop'.

This will bring you to a new page asking if you are sure that you want to drop all the tables in your new database. Select ‘Yes’.

7. Now go to ‘Import’ at the top to import your backup SQL database content. – Find the backup SQL file you made in step three on your computer, choose it and click ‘Go’. This will import all of your tables from the backed up database to your new database.

Note: SQL databases over 7.1MB may need to be sent to your host’s support team to be uploaded directly.

8. Open the new host up in your FTP application and edit wp-config.php. – Your database name, user and password should remain the same because you just dumped one database and imported another database’s content in its place. Though the name and login info might be the same you need to make sure your table prefixes are still correct.

What to look for in the wp-config.php file:

// You can have multiple installations in one database if you give each a unique prefix
$table_prefix = 'wp_'; // Only numbers, letters, and underscores please!

Your table prefix is what you see in the table title field. It’s the prefix before the table name. For example, using the photo of the tables above, in “wp_ngg_gallery” the “wp_” is the table prefix. Update the table prefix in the wp-config.php file if needed and save it to your server.

That’s all there is to it! Have a question or struggle? Found this tutorial useful? Let me know in the comments below!

This entry was posted in Blogging, Websites and tagged , , , . Bookmark the permalink.

32 Responses to DIY: How To Move Your WordPress Database To a New Host

  1. Mike says:

    Great article. I recently switched my Godaddy hosting from windows to Linux. They assured me that all will be fine but as I guessed all was not fine. After much investigating the only thing I had to do was reassign the file paths in my wp-options table to point to my correct domain. Godaddy of course had no idea how what the problem was.

    • I had GoDaddy about 2 years ago and they are the absolute worst host I've ever used. Their backend is not intuitive and when I left they were still running PHP 4 when many modern WordPress plug-ins required PHP 5.

      Thanks for the kinds words and I'm glad to hear you were able to figure out what went wrong in your database!

  2. seattlehomes says:

    If only I had this information a few months ago when I transferred my blog. Great guide, really specific, ought to help some people out (and get some great SEO based on the title).

  3. bradley09 says:

    If you use a similar control panel, many hosting companies will transfer your site for you. Headache-free!!!

  4. Thank you for the great step by step information. I am about to go through this process my self.
    Regards,
    jscottalexander.com

  5. susan says:

    Thanks for the article. I am stuck and need to ask a question. I have a wordpress blog hosted a local server and I want to change the theme and I cannot figure out how to do so. I searched and searched for the answer and evidently I do not know what I am looking for b/c I can not get the instructions. Do you know where I can look or do you know how to do so?

    • Changing the theme requires you to log into your admin dashboard (sitename.com/wp-admin/) -> Appearance -> Themes -> Choose Your Theme (Opens a preview window within the browser window) -> Activate (upper right corner). That should do it!

  6. What about all your content? pictures, pdf's, zip files? Does the new file structure have to be updated anywhere?

    • Craig — When you move your WordPress install you have to download your entire wp-content folder from the old host to your computer and then re-upload it to the new server. This should take care of all of your uploads in the wp-uploads directory. You don't need to update anything other than permalinks. I change it to default and then save it as my custom configuration again in the event the server doesn't take to the permalink config from the previous host.

  7. Devin Best says:

    I'm looking to use a modified version of this process to create a WordPress install.

    I use the same theme, plugins, and base settings for every blog I create, and want to bypass the process of manually installing and configuring each setting and plugin.

    How can I install a brand new, fully configured WordPress installation with plugins and settings, and use that backup as an installer for subsequent new domains? What type of mods to the MySQL database are required, if there is no content of any sort?

    Thanks.

    • Hey Devin — I don't think it's possible but unfortunately this idea is beyond the scope of my knowledge on the subject. You might want to try talking to a WordPress coder to see if they know of any tricks to auto configuring WordPress.

  8. Chris says:

    Hello,

    Not sure if you're still responding to this, but I am wondering if it is necessary to move databases, etc. if I have a brand new site? I've customized the site design at a temporary location and now need to move it to a new domain and host to go live. There is no actual content yet aside from static pages. Do I still need to do all of these steps? Or can I just do a fresh install of WP at the new location and copy the needed theme file over from the temporary site via FTP?

    Thanks!

    • Josh Ferris says:

      Chris,

      If it's a custom design you just need to copy the theme over and it should work fine. If you have an options panel configured a la WooThemes or Thesis you may need to re-set those options.

  9. JoeColleen says:

    Once the WordPress site is live, and unless I am missing something, why not simply backup your old (previous) database then use WordPress' import feature to import the database?

    • Josh Ferris says:

      Hey Joe & Colleen,

      The previous database contains information for the entire site including plug-ins. Exporting and importing the WordPress XML file will just transfer posts/pages/categories/pictures.

  10. movingplus says:

    Really Nice Information Recommend your site to anyone

    Best Free Moving Quotes
    http://www.movingplusstorage.com

  11. Matt Smedley says:

    Thanks Josh! This is exactly what I was looking for. I just transferred a blog to Godaddy using these instructions. Since Godaddy will create a mySQL database with a WordPress install, I wasn't sure if i should delete that one, or create a new one. This made it really easy to just drop the tables in the DB created by godaddy and import the backed up / exported DB I had created at the previous host.

    One little trick. Once the import was complete, the website URL had a blank page, but I was able to get into the back-end. The issue was that I needed to upload all the plugins from the previous site as well. Once the plugins were imported/uploaded, everything worked perfectly. Thanks again!

  12. Cassandra says:

    Hi Joshua,

    I read through and followed your instructions, but am stuck, can I email you for help?

    -Cassandra

  13. Eric says:

    oooh man! Great resource, but I think I am screwed. I have 60 WordPress installations, all have their own SQL Db. I create a SQL DB that will house 10 of the installations. I went through all your steps, they worked like a charm, but here's the kicker for me, on every wordpress installation, during install, I left the wp_ as wp_. Is it possible to change the names of the tables so that wp_comment etc., becomes wp_1comment and they just change the wp_config.php to reflect wp_1.

    Hope that makes sense?

    Eric

  14. *AJ* says:

    Fantastic article champ! I spend ages stuffing around with no idea how to do this and then after reading your article, got it all done in 5 minutes.

    Great Work!

    *AJ*

  15. *AJ* says:

    Fantastic article champ! I spend ages stuffing around with no idea how to do this and then after reading your article, got it all done in 5 minutes.

    Great Work!

    *AJ*

  16. Casey says:

    This is great, but I still am having problems. I setup WP on the new domain, dropped the tables, then tried to import. I get hung up on any CREATE commands for insufficient privileges. Any ideas?

  17. Pingback: How I Moved WordPress Sherpa to a New Web Host Without Anyone Noticing | WordPress Sherpa

  18. Will says:

    Great tutorial, Josh!  Thanks for saving me a few hours!

  19. Admin says:

    i do this,But after i import database in new server,it shows error(for not matching the db info with new installation,i think).I changed the config.php in the new install but the db name is not changed in phpadmin.

    what to do now?

    thanks

  20. Jonsecu0001 says:

    Hi… it worked good for me. It is the first time I did it and the database and files did not represent a problem. However I stuggled with the permalinks. Great post!

  21. Peter Weicker says:

    Thanks for one of the most useful posts I’ve seen. Had to move a client’s WP install for the first time, and this was the first resource I googled. Things have changed by the end of 2011, but it was still easy to fill in the blanks.

  22. Nagaraj says:

    Where we have to change new url ??

  23. Dan says:

    How do I perform this if I own both servers and am not using a hosting service?

  24. Pingback: All Time Favorite Posts on Geek Estate - GeekEstate Blog

  25. Gary says:

    This is just what I’ve been looking for if for some reason the webhotel I’m on would close their business.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

2008 - 2014 GEEK ESTATE · ALL RIGHTS RESERVED - THEME BY Virtual Results