Home arrow MySQL arrow MySQL Command Line and SSH: Importing and Exporting Database

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.

TABLE OF CONTENTS:
  1. MySQL Command Line and SSH: Importing and Exporting Database
  2. SCP (Secure Copy): Downloading and Uploading MySQL database
By: Codex-M
Rating: starstarstarstarstar / 5
December 14, 2010

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Codex-M
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: