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

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.