How do I manage a MySQL to import or export my database using SSH? .: Netfirms 24/7 Support

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. Please follow these steps to check if SSH can be enabled on your account:

ENABLE SSH FOR YOUR ACCOUNT:

1. Login to the Netfirms Control Panel at http://www.netfirms.com
2. Click Site Tools
3. Click SSH
4. Click Enable

Then find an SSH client. Here are a couple of suggested free SSH tools:

Putty: http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html
WinSCP: http://prdownloads.sourceforge.net/winscp/winscp380.exe?download

SSH connections can be established with the following login credentials:

Username
Password
Host: ssh.netfirms.com (port 22)

CONNECTING TO A MYSQL DATABASE:
To connect to a mysql database via a SSH session: (where Username is the database username and dbid is the database id):

%mysql -A -q -uUsername -pPassword DatabaseID

NOTE: Your mysql Username, Password, DatabaseID information is stored in your Control Panel at https://controlpanel.netfirms.com. Refer to Site Tools, Database Manager, and then click Admin for the database you are trying to administer.

BACKING UP YOUR MYSQL DATABASE:
1. Using Netfirms File Manager or the touch command in SSH, create a file called dbBackupFile.sql. The file may reside anywhere inside your /www folder.

2. To make a backup copy of your mysql database via a SSH session (backs up your database in extended format using the -e switch):

%/usr/local/nf/bin/mysqldump -e --force --quick -h mysqlhost -u Username -pPassword DatabseID > dbBackupFile.sql

(You will then be prompted to enter your database password.)

DUMPING A TABLE FROM YOUR MYSQL DATABASE:

1. Using Netfirms File Manager or the touch command in SSH, create a file called tables.sql. The file may reside anywhere inside your /www folder.

2. Execute the following command in SSH (backs up your database in extended format using the -e switch): /usr/local/nf/bin/mysqldump -e --force --quick -h mysqlhost -u Username -pPassword DatabaseID tablename > tables.sql

IMPORTING DATA INTO YOUR MYSQL DATABASE:
To import a sql file to an existing mysql database hosted on your account:

%/usr/local/nf/bin/mysql --force --quick -h mysqlhost -u Username -pPassword DatabaseID < File.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.

IMPORTING TABLES INTO YOUR MYSQL DATABASE:
To import a table to an existing mysql database hosted on your account:

% /usr/local/nf/bin/mysql --force --quick -h mysqlhost -u Username -pPassword DatabaseUD tablename < tables.sql

NOTE 1: For additional help in mysql commands: %mysql -h

NOTE 2: Commands such as myisamchk and grant are not permitted by users for security reasons.

SCHEDULING YOUR DATABASE BACKUPS

To create a cron job to schedule your database backups please click here for further instructions.

How do I manage a MySQL to import or export my database using SSH?

Related Articles