Home arrow MySQL arrow Page 2 - MySQL Command Line and SSH: Importing and Exporting Database

SCP (Secure Copy): Downloading and Uploading MySQL database - MySQL

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

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.



 
 
>>> 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: