At some juncture you will be faced with the task of moving an existing website from one server to another. It’s an easy enough task to make a backup and transfer your website’s public_html directory to a new server, but all of your content lives in a MySQL database, and you will need to bring it with you if you want to keep your site intact.There are a couple of options at your disposal for this task. One is to use the web-based interface PHPMyAdmin, which you will find in the database section of Cpanel if you are a Cpanel user. The other is to ssh into the servers and use the mysql command if you’re on a unix server.

For most people the easiest way to do this is to use PHPMyAdmin on both ends. It’s self explanatory, just choose the database you need to export and save it to your local computer as a file. Or, use ssh in and use mysqldump at the command line:

mysqldump -u database_username -p name_of_database > foo.sql

Which will prompt you for the database user’s pasword, and then dump the database to foo.sql.

Depending on how large your website is, we can approach the task of importing it to the new site in one of two ways.

First, before you import the old database to your new box, create a database and a database user and password for the new server in Cpanel, just like you had to do on the old server. Make sure you associate the database user you create with the new database, and make sure you give that user all permissions.

If you have a small MySQL database, <100megs or so depending on your server’s PHPMyAdmin settings, you can use PHPMyAdmin to import the database. I recently moved my site via PHPMyAdmin, and it went without a hitch, but the largest database I had was just over 110 megs. I moved my friend’s website to my server last week, and his database was over 300 megs and required everything to be done from the command line. Moving his site went even smoother than moving mine, but I understand that some of you may find the command line intimidating and will prefer to use PHPMyAdmin.

If your website’s database is larger, chances are that PHPMyAdmin will timeout after the database is partially imported. Don’t panic. I think that using the command line is just as easy or easier, and is less cryptic than using the PHPMyAdmin interface.

You will need to upload the SQL dump to your new server. Once there, close out your ftp program and fire up a terminal. SSH into your new server and switch to the directory you uploaded your SQL dump to. A simple mysql command in your shell will get you up and running:

mysql -u database_username -p name_of_database < foo.sql

This will bring up a password prompt. Just enter the password of the database user as you created it in Cpanel, and the database will be imported.

Now it’s time to kick the tires and make sure everything is ok after the move. Remember, if there is a problem, you can always make a fresh dump of the database and import it again. Maybe you used PHPMyAdmin and checked a box you shouldn’t have, maybe your new database’s database user wasn’t given all permissions. Whatever the issue, it’s nothing that can’t be fixed.

Becoming familiar with exporting and importing MySQL databases isn’t rocket science, and I guarantee you it’s a skill you will use again someday. Take your time, be sure of what you’re doing before you start hammering the keyboard, and your move will painless.

Subscribe To RSS Feed Updates (What is RSS??)

Bookmark & Share

Related Posts

• I Need Wordpress MySQL Help, And I’m Willing To Pay
• WordPress Pages - Where Are They Stored In MySql?
• WordPress 2.1 And UTW Database Issues
• Site Problems Were Caused By A Massive Spam Attack
• Create Your Online Database Applications In Minutes With Zoho Creator 2.0
• The Truth About Shared Hosting
• I Need Help Importing Emails Into GMail