MySQL Command Line and SSH: Importing and Exporting Database

There are times when using a GUI-based client, such as phpMyAdmin, for managing MySQL databases is not enough. Limitations include issues with importing databases and lack of security for MySQL sessions. You can get around these limitations if you’re willing to use the command line. This article introduces the use of the command line in MySQL management.

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.

{mospagebreak title=SCP (Secure Copy): Downloading and Uploading MySQL database}

Okay, now you have the MySQL backup generated in your hosting server at the path /home/temporary, you can download it securely using SSH SCP command (secure copy).

To download the MySQL database:

1. Exit your hosting server SSH session (exit command):

-bash-5.03b$ exit                                                                                                      
logout

Connection to php-developer.org closed.

2. Below is the syntax for SCP command when downloading files:

scp [ssh_username]@example.com:/your/serverpath/database.sql /your/local/downloadpath/

Suppose you have the following credentials:

username: codexm
domain/hostname: php-developer.org
Server path where the MySQL file is located: /home/temporary/
MySQL database file name: customerrecords.sql
Target download path: /home/codex-m/Desktop/

Then the command will be:

codex-m@codex-m-desktop:~$ scp codexm@php-developer.org:/home/temporary/customerrecords.sql /home/codex-m/Desktop/

Again, if your SSH server uses Port 1234, then:

codex-m@codex-m-desktop:~$ scp -P 1234 codexm@php-developer.org:/home/temporary/customerrecords.sql /home/codex-m/Desktop/

IMPORTANT: Bear in mind that the -“P” portion of the number option should now be in upper case if used with the scp command. This is different from the previous SSH command in a Terminal, where it should be in lower case.

The above command will download the customerrecords.sql data from your hosting server /home/temporary/ to your Linux desktop. If you need to see the screen shot as it was entered at the prompt, take a look: http://www.php-developer.org/screenshot/scpdownloaddatatabase.jpg

To upload a MySQL database back to the hosting server, you will just reverse the SCP syntax:

scp /your/local/path/database.sql ssh_username@example.com:/your/websites_serverpath/

Using actual credentials:

codex-m@codex-m-desktop:~$ scp /home/codex-m/Desktop/customerrecords.sql codexm@php-developer.org:/home/temporary/

Importing a Large MySQL database to XAMPP Localhost MySQL

This is typically important when importing a large MySQL database. Commonly, when using phpMyAdmin to import the database, it fails.

This assumes you already have the MySQL file in your local computer. The procedure below works for Linux/Ubuntu. The steps below work best if you have installed and configured XAMPP in Ubuntu, as in the procedure shown in this article: http://www.devshed.com/c/a/Administration/How-to-Install-XAMPP-on-Ubuntu-Linux/. It may work in other installations, however.

The following are the steps:

Step 1. Log in as root on your Linux terminal.

codex-m@codex-m-desktop:~$ sudo -s -H
[sudo] password for codex-m:
root@codex-m-desktop:/home/codex-m#

Step 2. Go to XAMPP bin directory: /opt/lampp/bin/

root@codex-m-desktop:/home/codex-m# cd /
root@codex-m-desktop:/# cd opt
root@codex-m-desktop:/opt# cd lampp
root@codex-m-desktop:/opt/lampp# cd bin
root@codex-m-desktop:/opt/lampp/bin#

Step 3. Connect to MySQL database as root.

root@codex-m-desktop:/opt/lampp/bin# ./mysql -uroot -p[password] -hlocalhost

Replace it with your own MySQL password. Do not forget to place a dot sign before the “/”.

Step 4. At the MySQL prompt, create a database and name it anything you like (example: customerrecords).

mysql> create database customerrecords;
Query OK, 1 row affected (0.00 sec)

Step 5. After creating the database, exit the MySQL prompt:

mysql> exit
Bye
root@codex-m-desktop:/opt/lampp/bin#

Step 6. Import the database (example: customerrecords.sql) to XAMPP localhost customerrecords database:

root@codex-m-desktop:/opt/lampp/bin# ./mysql -uroot -p[password] -hlocalhost customerrecords </home/codex-m/Desktop/customerrecords.sql

The above command assumes that the MySQL database file is located in your Ubuntu desktop.

Example screen shot using command prompt:

http://www.php-developer.org/screenshot/mysqimportxampp.jpg

Confirming Successful Importation of Database

Of course you need to confirm that you have successfully imported the database to XAMPP localhost. The following are the steps:

Step 1. Go to Terminal and log in as root. Then go to XAMPP bin directory. Refer to the first two steps in under the previous heading above.

Step 2. Connect to MySQL server as root.

root@codex-m-desktop:/opt/lampp/bin# ./mysql -uroot -p[password] -hlocalhost

Step 3. To display the entire database in your XAMPP localhost, enter:

mysql> show databases;

Then you will see the customerrecords database successfully imported, as shown in the screen shot below:

Step 4. Of course you also need to check the tables of that database. But before  you can view the tables, you need to use the customerrecords database first:

mysql> use customerrecords;
Database changed

Step 5. Finally, view the tables of the customerrecords database.

mysql> show tables;

After that you will see the database’s list of tables.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort