Migrate MySQL

From DreamHost
Jump to: navigation, search

This article will help you migrate your MySQL databases from your old host to DreamHost. Throughout this guide you will see parts of commands in Bold - that signifies that you must replace the text there with the correct information for you. This article also assumes that you have already created a database at DreamHost through the DreamHost Web Panel > Goodies > Manage MySQL section.

One Step Solution

It may be possible for you to move your database to DreamHost with one command passed through shell. This solution requires Shell SSH access to one of the hosts, AND remote access to the "other" database. The command looks like this:

mysqldump -h oldhost -u oldusername -poldpassword olddbname | mysql -h newhost -u newusername -pnewpassword newdbname

Note: there is no space between "-p" and the password.


The "mysqldump" command writes a copy of a database to a single file or alternatively in the above example, the "|" redirects its output to another "mysql" client which uses that for input for restoring. The -h flag specifies the hostname, -u the username associated with the SQL database (NOT your ftp or shell username), -p is the SQL user's password, and finally there's the database name. Note that the destination database must already exist -- if it does not, this command will fail.

If you are running this command from shell on your old host, you must grant remote access to your DreamHost database from the DreamHost Web Panel > Goodies > Manage MySQL section. Click the username on the database you want to migrate your data to, then add the IP address of your old server to the "Allowable Hosts" box.

A Longer Solution

If you are unable to use the one-step solution, it's possible to move the database manually, by backing up the database on your old host to your personal computer, then importing the backed-up data into your new database on DreamHost.

Creating the MySQL Backup

The method of creating a backup of your MySQL database differs from host to host. The simplest method for smaller (< 7MB) databases is to use phpMyAdmin, a web-based SQL manager available on many hosts' domain-management websites.

See the article Backup MySQL for instructions on methods to make a SQL file containing your old database:

  1. From phpMyAdmin - Backup a Database section.
  2. From your shell account with mysqldump.

Importing Your Data

If your SQL backup file is under 7mb in size, it can be imported to your new database with DreamHost's phpMyAdmin utility. If it's 7mb or more, you'll need to import it through the command line.

Importing Through phpMyAdmin

Please see phpMyAdmin - Restore a Database section.

Importing Through the Shell

If you're working with a database 7mb or larger, this is the process for you. First you'll need to make sure you have a User set up with access to shell. To do this log into the DreamHost Web Panel > Users > Manage Users > Edit the user and place a tick mark for shell access. Save these changes.

  1. Upload your SQL file via FTP into your home directory. If the DNS records are already in place, you can ftp straight to yourdomain.net and reach the home directory. Otherwise you can reach the same directory at yourdomain.dreamhosters.com. Make sure your login information matches the username associated with the domain in DreamHost Web Panel > Domains > Manage Domains, under the "Web Hosting" heading. You can log in to any of your domains with any of your logins, but only the associated user will be able to view the files associated with the domain.
  2. Log into your server with SSH
  3. Issue the command to import your SQL file:
mysql -h mysql.example.com -u username -ppassword dbname < outfile.sql

Once you issue that command your SSH client won't respond or do anything for a bit - but that's good news. It's working. When the command completes, if there was no further output and you go directly to the command line prompt, it means the command finished successfully.

Alteratively if you don't have a SQL file but instead a CSV file to import, then you can use the mysqlimport utility.

mysqlimport --ignore-lines=1 --fields-terminated-by=, --fields-enclosed-by=\" --columns='id,email,address,phone' --local --host=HOSTNAME.COM --user=USERNAME --password=PASSWORD -C -v DB_NAME FILENAME.csv


When migrating MySQL from one host to another, it's possible to test the new installation before modifying your domain's DNS entries. To do this, please see Viewing your site before DNS change and Accessing your database before DNS change pages.


Access Denied

Many SQL files created by exporting the contents of an existing database will contain a CREATE DATABASE and a USE statement intended to completely automate the process of migrating the database to a new installation.

Trying to import your data into a new database on DreamHost with such a file will fail, as you do not have sufficient permissions on DreamHost to CREATE DATABASE from the command line (or anywhere except the Control Panel).

Using the import command above as an example, you would receive an error message similar to:

ERROR 1044 (42000): Access denied for user 'your_user_name'@'yourhost/hostmask' to database 'newdbname'

That message is the indication that you don't have sufficient permissions to create the new database, and is caused by the CREATE DATABASE statement in the SQL file being imported. This is not a major problem, you have already created the database, so the CREATE DATABASE statement that is in your exported SQL file is not even needed.

To resolve this issue, you need to open the SQL file in a "pure" text editor (not a "Word Processor"), and delete the line:

CREATE DATABASE `newdbname` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_c$ USE `newdbname`;


USE `newdbname`;

The exact text of the CREATE DATABASE statement will differ depending upon the database name and the character set used - just delete everything from the beginning of the line through, and including, the semi-colon at the end of the line.

Once you have deleted these statements, save your edited SQL file, and repeat the import command as indicated in the previous section.

If you have done everything else correctly, the import should proceed without further issue.