SSH — Importing a database

View the following article to find your database credentials:

Importing a backed up database .sql file via SSH

  1. Review Enabling Shell Access to change your FTP or SFTP user into a SHELL user.
  2. Upload your backed up database file with a .sql extension to your web server. Review the FTP article for details.
  3. 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 -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:
    [server]$ 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

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:

See also

Did this article answer your questions?

Article last updated .