Knowledgebase
How to export and import MySQL databases via SSH
You can back up a MySQL database or you can import one through SSH by executing just a single command. The command is very similar for both exporting and importing, but there are some differences. Of course, you need to know the name of the database, the username of a user assigned to that database, and the password of that user. You can also import and export databases through phpMyAdmin, but if your database is a big one (e.g. larger than 60 megabytes), you should do it via SSH. For more information on how to do it through phpMyAdmin read the tutorial on importing a MySQL database with phpMyAdmin and the one on backing up your site.
Backing up a database can be performed by executing a command that looks like this:
mysqldump -u (username) -p (database name) > (name of database dump file)
You have to replace (username) with the actual username of the database user, and you also have to replace (database name) with the full name of the database that you want to export. Change (name of database dump file) with any name that you want; just use the .sql extension at the end. Let's say that you want to export a database called knp_mystore. The username of the user that you have created for that database is johnsmith, and you want the exported dump file to be labeled mystore.sql. Then the command that you have to execute would look like this:
mysqldump -u johnsmith -p knp_mystore > mystore.sql
After you execute the command you'll be prompted to type the password for the database user that you've specified in the command. After you type the password press the Enter button on your keyboard and the database will be exported to the current working directory on your hosting account. The larger the database, the more you have to wait before it's exported. Smaller databases will be exported almost instantaneously. Once it's exported to your account, you can also download it to your local computer. You can do this via SSH, with an FTP client, or through the Files section of the Pixie control panel.
To import a database, upload the dump file to your hosting account, change the current working directory to the one that contains the MySQL dump file and execute a command that looks like this:
mysql -u (username) -p (database name) < (name of database dump file)
The only difference in the command for importing compared to the one for exporting is that you should use just mysql instead of mysqldump, and the bracket between the database name and the name of the dump file should be an opening one (e.g. <). So if we use the above example names, and you want to import a dump file called mystore.sql into a database named knp_mystore, and the user created for that database has a username johnsmith, the command would look like this:
mysql -u johnsmith -p knp_mystore < mystore.sql
When you execute it you'll be prompted for the password of the user. After you type it and you press Enter, just wait for the dump file to be imported into the database.