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 Enabling Shell 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 -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.
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: