Migrating your MySQL database from Dreamhost

Those of you who are still using the Dreamhostess with the Mostest (downtime) are probably looking for another solution for your webby goodness after the networking train wreck of the past few months (1, 2, 3, 4, 5, 6, you get the picture. By the way, if this sounds like awesome hosting to you, by all means, use my referral link when you signup with them! ).

You know I’m in love with Slicehost, for all the wrong reasons (they tease me with wait lists to even sign up, then they hit me with the pure root goodness, and I’m jonesing for another hit). I’ve got my Debian VPS all setup to roll, just need to get my data from Dreamhost over to the new server.

Website files? No problem. I used handy old rsync, and even put together a little bash script that I reference from a cron job, to do regular syncing of the Dreamhost data until I’m ready to go live with the new site.

But what about the database? My websites are all database driven, which mean no MySQL migration = no website! I tried using the phpmyadmin interface first, and simply exporting / importing the database from one install to another. Well, the database file is about 30 MB (this is a pretty big blog we’re moving), so that’s right out!

What’s a noob server admin to do?

mysqldump

Command Line to the rescue! Shut down that phpmyadmin, and crank up ye olde terminal. SSH into your new server, the one you are migrating to, and run the following command. The username and password in the first part should be for the MySQL database you are migrating FROM, the second part of the command should use the username and password you are migrating TO. Everything in CAPS should be replaced with your own details. No line breaks, put the whole thing on a single line:

Now that is Easy Breezy Covergirl!

IP Access on Dreamhost

If you’re moving your database away from Dreamhost, you’ll need to do a little unlocking of the database first. Dreamhost restricts access to your databases by IP address (or the resolved domain name equivalent). This a good thing, a security feature, but it will thwart our attempts to use mysqldump from our new server. No worries, you can add access for whatever IP your new server is on. Here’s how it works.

From your Dreamhost control panel, hit Goodies >> Manage MySQL. Locate the database you’re migrating, then click on the USERNAME next to the database. It will take you to a page that lets you manage the privileges for that user. Toward the bottom, find the option for “Allowable hosts.” It will look something like this:

allowable hosts

The %.dreamhost.com line should be there by default, allowing any dreamhost server to get at your data.

You will need to add a new line, containing the IP address (or IP range using wildcards) for the server you intend to migrate to. Note that the IP address they list beneath this is the IP address for your current browser connection to the control panel, not the IP address you need to use in order to allow server access from your new machine. Don’t be fooled!

If you are on Slicehost, you have a dedicated IP number that you can use here.

Save, exit, run the command line for mysqldump from your new machine, and you should get a clean import of your database into the new server.

Taste the Freedom!

PS. Don’t cancel that dreamhost account just yet, though. Sometime in the future, I’ll show you how I used .htaccess on my new server to treat my dreamhost account like a big ole’ hard drive for storing and serving large media files.

Make Dreamhost your bandwidth bitch!

3 thoughts on “Migrating your MySQL database from Dreamhost

  1. Hey where is the promised htaccess article. I have dreamhost and am force to do the exact same thing you are doing!…run away from dreamhost because their MySQL is slow as Moses running the hundred yard dash on Mondays and Tuesdays.

  2. Did you move back to Dreamhost?

    root@brie:/home/aleida# whois commandlineidiot.com | grep Name
    Domain Name: COMMANDLINEIDIOT.COM
    Name Server: NS1.DREAMHOST.COM
    Name Server: NS2.DREAMHOST.COM
    Name Server: NS3.DREAMHOST.COM
    Domain Name: commandlineidiot.com
    root@brie:/home/aleida# host commandlineidiot.com
    commandlineidiot.com has address 67.205.48.115
    root@brie:/home/aleida# whois 67.205.48.115 | grep DREAM
    NetName: DREAMHOST-BLK7
    NameServer: NS1.DREAMHOST.COM
    NameServer: NS2.DREAMHOST.COM


    Brie

Leave a Reply