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’.
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:
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.
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!