Overview
You can import database content into your DreamHost MySQL database over SSH by running mysql commands from the command line. This works both for a backed-up .sql file you upload to your server and for copying a database directly from another host.
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. See this article to learn more about how to find your database credentials.
How do I import a database?
The following sections explain how to run SSH commands to import a database.
The database must be empty before running the following commands.
If the file you're importing contains a table that already exists, an error is thrown and the import will not complete.
How do I import a backed-up database .sql file via SSH?
- Review this article 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, and 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.
How do I import from my 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
Be aware of the following when running this command:
- There is no space between -p and the password.
- The destination database must already exist; if it doesn't, the command fails.
- If you run this command from your old host, you must grant remote access to your DreamHost database by editing the Allowable Hosts field in your panel.