Migrate MySQL

From DreamHost
Jump to: navigation, search

This article will help you migrate your MySQL database 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 > MySQL Databases.

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 (Telnet or 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.

Explanation

The "mysqldump" command writes a copy of a database to a single file; this is known as "dumping." With these parameters, the database is written straight to your new server's database. The -h flag specifies the hostname, -u the username associated with the SQL database (NOT your domain management username for your host), -p is the SQL user's password, and finally there's the database name. Once the file is transferred, MySQL will populate the destination database with the data from the dumped file. 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 > MySQL Databases. 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 is to use phpMyAdmin, a web-based SQL manager available on many hosts' domain-management websites.

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

  1. From phpMyAdmin
  2. From cPanel (cPanel.net)
  3. From the shell via 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

  1. Log into phpMyAdmin with a web browser by navigating to the hostname for the database. The hostname (as well as the user name and password associated with that database) can be found in DreamHost Web Panel > Goodies > MySQL Databases. If you are unable to reach the hostname (e.g. if you're switching hosts and haven't yet updated the DNS records) you can mirror your DreamHost site at a dreamhosters.com sub-domain, e.g. yourhostname.dreamhosters.com. See the "Testing" section below for more details.
  2. Select the database that you would like to import data to on the top left side of phpMyAdmin
  3. Click the small icon SQL icon.
  4. Select Import Files from the new pop-up window.
  5. Click Browse, select the file from your computer, then click Go

If your file is close to the size limit it may take a few minutes to upload the file and populate your database. PHPMyAdmin may ask you to perform the upload again in order to finish the import. Once the upload has completed, you will see a confirmation message. After the confirmation message, it may take several additional minutes before all the tables are fully populated. You can refresh your PHPMyAdmin periodically to follow along as the table-count inches upward.

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 Telnet or SSH client won't respond or do anything for a bit - but that's good news. It's working. Before long you should get a success message back.

If you're using shared hosting and don't have shell access to your MySQL box, then SSH into a new separate shell user login and upload your file. Then you can use the mysqlimport utility. You can also use this to import a csv.

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

Testing

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, make a "mirror" of your DreamHost-hosted domain, by adding "yourdomainname.dreamhosters.com" as a mirrored subdomain. To do this, navigate to DreamHost Web Panel > Domains > Manage Domains, click the "Add New Domain / Sub-domain" button, and scroll down to the "Mirrored" section. Enter the new domain name in the "Domain that mirrors:" text field, and select the domain you want to mirror in the drop-down. Make sure to leave ".com", ".org", or ".net" out of your subdomain name: e.g. if your domain is "yourdomain.net", the new subdomain should be "yourdomain.dreamhosters.com". Then click the "Mirror with this subdomain now!" button.

Once your mirror has been activated, you'll want to make sure you have a MySQL host name setup on it. You'll need to manually create the MySQL host name for your new mirrored sub-domain in the Goodies > MySQL Databases section of your web panel. The new MySQL host should be named in this format: mysql.yourdomain.dreamhosters.com. Then after that host name is setup, navigate to the mirror's phpMyAdmin interface, which should be located at yoursqlhostname.yourdomain.dreamhosters.com. Make sure to log in with the sql user login information you used to set up the database, not your DreamHost login information.

Once you've logged into the phpMyAdmin interface, select the appropriate database, then "browse" the tables and edit their values to replace instances of "yourdomain.net" with "yourdomain.dreamhosters.com". When this is completed, you can then view your site at yourdomain.dreamhosters.com as installed. Once you've successfully confirmed and configured the installation, edit the appropriate SQL values back to "yourdomain.net"; you may then update your domain's DNS entries.

Troubleshooting

AUTO_INCREMENT

After importing your database to DreamHost, you may find that AUTO_INCREMENT has been dropped from columns that formerly had that attribute.

Solution: it seems to be a problem with the mysql compatibility mode, which reverts to 3.23 instead and thereby somehow drops the auto_increment value. The solution I have come up with is to only export the DATA (not the STRUCTURE) of my database backup and apply it to a structure that still has the auto_increment value set were it's meant to be.

This article tells a little more about it. It is also submitted as a bug according to this page. Hope it helps somebody out there in the same situation as me. :) I will now go and (hopefully) save my forum.

Access Denied

Many SQL files created by exporting the contents of an existing database will contain a CREATE DATABASE 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 even from a script - you must create all databases from within 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`;

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 the CREATE DATABASE statement, 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. Assuming you have given the MySQL user sufficient permissions, the subsequent table creation, and other commands, in the SQL file should work fine now that the problematic CREATE DATABASE statement has been removed.