Migrate MySQL

From DreamHost
(Redirected from Restore SQL backup)
Jump to: navigation, search

Overview

When moving your hosting to DreamHost, it may be necessary to migrate your website’s database. This article describes how to transfer a MySQL database to your DreamHost account.

Creating a MySQL database in your panel

Before you can import a database, it must first be created in your panel. It is not possible to create the database during import.

As a security precaution, all DreamHost database can only be created within the panel on the (Panel > ‘Goodies’ > ‘MySQL Databases’) page. View the following article for details on how to create your database:

Importing your database

  • If your SQL backup file is under 7MB in size, it can be imported to your new database via phpMyAdmin.
  • If it's 7MB or more, the only option is to import it through the command line.

To import a backed up database through phpMyAdmin:

  1. Log into phpMyAdmin.
    01 Migrate MySQL.fw.png
  2. Click the 'Import' tab on the top right pane.
  3. Click the Browse button and locate the file to import.
  4. Click the Go button on the bottom right to import the database.

Importing a backed up database using SSH

  1. Review Enabling_Shell_Access to change your FTP or SFTP user into a SHELL user.
  2. Upload your database file with a .sql extension to your web server. Review the FTP article for details.
  3. Log into your web server via SSH.
  4. Make sure you’re in the same directory you uploaded the .sql file into, then run the following command to import the .sql database file.
    mysql -h mysql.example.com -u username -ppassword dbname < databasefile.sql
    
    • Once you issue the command, your SSH client won't respond or do anything for a bit as it works on importing your file. The import successfully completes when the command line prompt appears.
    • Alternatively, if you don't have a SQL file but instead a CSV file to import, then you can use the following 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
    

One step solution (only in SSH)

You can also run a single command in your Shell terminal to copy and import a remote database:

mysqldump -h OLDHOSTNAME -u OLDUSERNAME -pOLDPASSWORD OLDDATABASENAME | mysql -h DREAMHOSTHOSTNAME -u DREAMHOSTUSER -pDREAMHOSTPASSWORD DREAMHOSTDATABASE
Note2 icon.png Note: There is no space between "-p" and the password.


Explanation of flags within the above import command

    mysqldump
    writes a copy of a database to a single file.
    |
    redirects its output to another "mysql" client
    -h
    specifies the hostname
    -u
    specifies the username associated with the SQL database (NOT your FTP or Shell username)
    -p
    specifies is the SQL user's password
    "DREAMHOSTDATABASE"
    specifies the name of the database
Note2 icon.png Notes:
  • The destination database must already exist; if it doesn't, the command will fail.
  • If you are running this command from shell on your old host, you must grant remote access to your DreamHost database by making the remote server an “allowable host”. For more information, please visit the following article:
Managing MySQL users, allowable hosts, and privileges


Troubleshooting

ERROR 1044

Most SQL files contain a CREATE DATABASE and a USE statement, which are created when you export the contents of an existing database. These statements are intended to completely automate the process of migrating the database to a new installation.

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

When you use the import command as described in the previous section, you'll see the following error message:

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

This error message indicates that you don't have sufficient permissions to create the new database, which is caused by the CREATE DATABASE statement in the SQL file being imported. This is not a major problem as you have already created the database. Because of this, the CREATE DATABASE statement that is in your exported SQL file is not even needed.

The solution

To resolve this issue:

  1. Open the SQL file in a text editor (not a word processor, such as MS-Word).
  2. Delete the lines that begin with CREATE DATABASE and USE. For example:
    CREATE DATABASE `newdbname` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_c$ USE `newdbname`;
    
    and
    USE `newdbname`;
    
    • The exact text of the CREATE DATABASE statement differs depending on the database name and the character set used.
    • Make sure to delete everything from the beginning of the line through, and including, the semi-colon at the end of the line.
  3. Once you have deleted these statements, save your edited SQL file, and then repeat the import command as indicated in the previous section.

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

ERROR 1227

If you restore a MySQL backup to a database under another user and have any views in your database, you'll get an error similar to this:

ERROR 1227 (42000) at line 694: Access denied; you need the SUPER privilege for this operation.

You'll need to change the DEFINER mentioned in the .sql file at the line specified in the error.

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, please review the instructions in the following articles: