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, a 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 64 MB in size, it can be imported into your new database via phpMyAdmin.
- If it's 64 MB or more, (or if you encounter a timeout while importing) the only other option is to import it from the command line.
To import a backed up database through phpMyAdmin:
- Log into phpMyAdmin.
- Click the 'Import' tab on the top right pane.
- Click the Browse button and locate the file to import.
- Click the Go button on the bottom right to import the database.
Importing a backed up database using SSH
- Review Enabling_Shell_Access to change your FTP or SFTP user into a SHELL user.
- Upload your database file with a .sql extension to your web server. Review the FTP article for details.
- Log into your web server via SSH.
- 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
|Note:||There is no space between "-p" and the password.|
Explanation of flags within the above import command
- writes a copy of a database to a single file.
- redirects its output to another "mysql" client
- specifies the hostname
- specifies the username associated with the SQL database (NOT your FTP or Shell username)
- specifies is the SQL user's password
- specifies the name of the database
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 fails 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.
To resolve this issue:
- Open the SQL file in a text editor (not a word processor, such as MS-Word).
- 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`;
- 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.
- 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.
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 remove the DEFINER mentioned in the .sql file at the line specified in the error.
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: