If you find yourself in a situation that requires changes to your site, and your developer isn't available to help, DreamHost's skilled support team may be able to assist you for a small fee. You can find more information about supported services and associated costs in the DreamHost Professional Services article.
Overview
This article explains how to import database content via SSH.
The database must be empty before running the commands in this article.
If the file you're importing contains a table that already exists, an error is thrown and the import will not complete.
Locating your database credentials
This article assumes you already have a backup of your MySQL database. To import the backup file, you'll need your database credentials. View the following article to find your database credentials:
Importing a backed-up database .sql file via SSH
- Review Creating a user with Shell (SSH) access to change your FTP or SFTP user into a SHELL user.
- Upload your backed-up database file with a .sql extension to your web server. Review the FTP article for details.
- 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.
[server]$ mysql -h mysql.example.com -u username -p dbname < databasefile.sql
- You are prompted to enter your MySQL user's password.
- 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.
Importing from your old host to DreamHost via SSH
You can also run a single command in your Shell terminal to copy and import a remote database:
[server]$ mysqldump -h OLDHOSTNAME -u OLDUSERNAME -pOLDPASSWORD OLDDATABASENAME | mysql -h DREAMHOSTHOSTNAME -u DREAMHOSTUSER -pDREAMHOSTPASSWORD DREAMHOSTDATABASE
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
The destination database must already exist; if it doesn't, the command fails.
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: