How do I import or export my MySQL database using SSH

By April 9, 2012 No Comments

Your MySQL databases can be administered through an SSH session via command line (especially if you have large sized data to import/export). To access your account using SSH you must subscribe to a hosting plan that supports this feature.

Enable SSH for your account:

  1. Login to your Netfirms Control Panel.
  2. Click Hosting.
  3. Click Settings.
  4. Select Enable in the SSH pull-down menu.

We suggest using Putty to connect to your account as it is a freely available SSH client for windows.

SSH connections can be established with the following login credentials:

  • Username: Your Netfirms member name.
  • Password: Your Netfirms account password.
  • Host: membername.netfirms.com (port 22).

Connecting to the MySQL shell

To connect to a mysql database via a SSH session: (where [Username] is the database username, [Password] is the database password and [DatabaseID] is the database id):

mysql -u[Username] -p[Password] [DatabaseID]

Note: Your mysql Username, Password, DatabaseID information may be retrieved from the Netfirms Control Panel

Importing a .sql file into your database

To import a .sql file to an existing mysql database hosted on your account:

/usr/local/nf/bin/mysql -hmysqlhost -u[Username] -p[Password] [DatabaseID] < backupFile.sql

For example:

/usr/local/nf/bin/mysql -hmysqlhost -uu12345678 -psecret d12345678 < backupFile.sql

Note: If you encounter a “max_questions” error, you should redo your mysql backup using the -e switch as in the example above. Then try the import command again.

Exporting your MySQL database

/usr/local/nf/bin/mysqldump -e -hmysqlhost -u[Username] -p[Password] [DatabseID] > dbBackupFile.sql

For example:

/usr/local/nf/bin/mysqldump -e -hmysqlhost -uu12345678 -psecret d12345678 > backupFile.sql