MySQL Command Line and SSH: Importing and Exporting Database

To reiterate, typical limitations on phpMyAdmin include the following:
1. Importing a large database is a problem in phpMyAdmin. For example, if you have a MySQL database that you need to import to your XAMPP Local host MySQL.
2. Since phpMyAdmin is browser-based, most web companies do not offer SSL encryption for MySQL sessions. Thus, it is highly possible that sensitive information might get compromised during a MySQL session.
This tutorial uses SSH connectivity in MySQL. This encrypts the entire MySQL session, including the sending of passwords.
If you need an introduction to the use of SSH for MySQL database, you can read this tutorial: http://www.devshed.com/c/a/MySQL/MySQL-in-SSH-Basic-Guide/
This is a beginner tutorial that covers new and important topics in MySQL management using the command line.

 
Connecting to a MySQL database using a Linux Terminal or DOS mode
You might know the pUTTY SSH client that is used to connect to an SSH server and perform SSH sessions: http://www.chiark.greenend.org.uk/~sgtatham/putty/
However, you can use MySQL in SSH with a Linux terminal or Windows DOS command line mode without using pUTTY.
This section uses Linux as the operating system, but the commands to Windows DOS will be the same; only the command prompt and the file system path will be different.
1. Open up a Terminal (Applications - Accessories - Terminal). You will then see the Linux prompt:
codex-m@codex-m-desktop:~$
2. The first thing that you will do is connect to your website hosting SSH server. Of course, this assumes you have SSH enabled for your hosting account. For details, you need to refer to your hosting support. If you have SSH enabled for your hosting, then connect to it by:
codex-m@codex-m-desktop:~$ ssh your_ssh_username@yourdomain.org
Password:
-bash-3.2$
You will not see your password as you type it, so enter it carefully. If you are connecting to your SSH server for the first time, it may require you to accept a certificate, so just type "yes."
You can even copy your password (for example, from Keepas) and paste it into the password field in the command line; this is much more efficient than typing it. After you have copied the password, click on the blinking cursor beside the password field. Then go to Edit -> Paste and press the enter key. When you see the bash prompt, you have successfully connected to your website SSH server.
Sometimes, your SSH server will use a port that is different from the default port 22. So, for example, if your SSH server uses port 2345, then you need to specify it:
codex-m@codex-m-desktop:~$ ssh -p 2345 your_ssh_username@yourdomain.org
Password:
-bash-3.2$
Important: Bear in mind that the "p" port number option should be in lower case. If you use an upper case "P," then it won't work.
3. Now you are connected to your SSH server, you can easily connect to your MySQL database. Use the syntax below:
-bash-3.2$ mysql -u[username] -p[password] -h[hostname]
The brackets contain your MySQL login credentials. Again, you can use the copy and paste method described in the second step.
Note: -u, -p and -h are all in lower case.
For example, if your login credentials are the following:
username: codex
password: harrymagic
hostname: codex.potterharry.com
Below is the command that you will enter into the SSH bash prompt:
-bash-3.2$ mysql -ucodex -pharrymagic -hcodex.potterharry.com
After entering this command, you will then see this message and the MySQL prompt:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 123456
Server version: 5.0.91-log MySQL Community Server (GPL)
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>
You have successfully connected to your hosting server MySQL database using SSH.
Exporting/Backing up MySQL database using SSH Command Line
Let's illustrate a few common tasks. Suppose you need to export/back up an existing MySQL database to your local computer using SSH command line.
And then you need to import the database (which is now in your local computer) to your XAMPP local MySQL server. XAMPP is a local Apache/PHP/MySQL development server.
First, connect to your website SSH server (follow the first two steps of the instructions above). To export a database, you will need to use the mysqldump command. Once you see the SSH bash prompt, you can issue a command like this:
-bash-2.05b$ mysqldump -u[username] -p[password] -h[hostname]  [databasename] >/path/to/temporary/databasename.sql
Let's use the previous MySQL login database credentials, and suppose the name of the database is customerrecords. Also assume that you will place the exported MySQL database backup in this path: /home/temporary/. Then you will give the  database file the name "customerrecords.sql."
So you will enter it as follows:
-bash-2.05b$ mysqldump -ucodex -pharrymagic -hcodex.potterharry.com customerrecords >/home/temporary/customerrecords.sql
If you like to see it exactly as it was entered into the command prompt, see this screen shot: http://www.php-developer.org/screenshot/screenshotexportdatabase.jpg
IMPORTANT: Make sure the export server path /home/temporary resides on your website hosting server and NOT in your local computer.
After this process is done, try visiting /home/temporary/. You will see the database named  customerrecords.sql there.