A tutorial to show how to Import and Export large MySQL Database via SSH (shell access)

Step 1: Download SSH Client

In order to login to your server via SSH, you will need to download some software to allow you to do this. There are many packages available, but one of the most widely used is Putty – and that is the package that we will be using here. Visit this page and download the version of putty.exe suited to your system.

Once downloaded, it does not need to be installed, simply run the exe file when you wish to use it.

Step 2: Connecting to the Server

When you start the program, you will be presented with a basic configuration screen. You will need to enter in the address of the server and the method being used to connect, as in the example below, and press the open button to start off the connection to the server.

The next screen will ask you to enter your username, do that and press enter. You will then be prompted for a password – input that and press enter again, and all being well you should then be logged in and see a screen like this:

Step 3: Exporting the Database

Now we want to create a “dump” of the database. What this will do is copy all of the data in the database to a text file saved in a location that you choose on the server.

You need 4 pieces of Information to be able to do this:

* Database Name (db_username)
* Database Username (db_name)
* Database Password
* Server PATH that you would like the exported file to be saved to

Once you have got all this information, we can begin the export process.

You will need to enter the following line of code into the putty window. (you can paste text into the putty window by right clicking if you wish):

mysqldump -u db_username -p db_name > /path/to/backup.sql

Replace the db_username and db_name with the Username and Name of the Database you wish to backup – and the edit the path to match that of your server, to specify where you would like the file saved.

In the example below, the following data has been used:

* db_username: adforums_forum
* db_name: adforums_forum
* path: /home/adforums/public_html/backup/example.sql

Once the line above has been typed in, press enter – you will then be prompted for the password, as in the example below:

Enter the database password, then there will be a short delay whilst the database is exported, when complete you will be presented with a screen like this:

Step 4: Downloading the Database Dump

The database has now been exported to the server, and a .sql file will have been created in location on the server that was specified above. To get a copy of the backed up file, using your FTP program, navigate to the location of the file on your server and you should then see it listed:

You can then download this file to your computer. If the file is saved in a publicly accessible directory then after downloading it you should delete the file from the server, as anyone would then be able to come along and download a full copy of your database.

Step 5: Importing the Saved Database

Okay, will now take you through the process of importing a backed up .sql file back into the database. This backup may be created using phpmyadmin, through the Invision Power Board Admin CP or via SSH as stated above, it doesn’t matter how it was created, can still be imported this way.

You will need to upload the file to the server via FTP and note the path to that file, and then logon to the server as described above in step two.

As above, you will need 4 pieces of information:

* Database Name (db_username)
* Database Username (db_name)
* Database Password
* Server PATH of the location of the .sql file

Once you have all of this information, we can begin the import process.

You will need to enter the following line of code into the putty window. (you can paste text into the putty window by right clicking if you wish):

mysql -u db_username -p db_name < /path/to/backup.sql

In the example below, the following data has been used:

* db_username: adforums_forum
* db_name: adforums_test
* path: /home/adforums/public_html/backup/example.sql

Input the line of code above (after editing to match your database details) and press enter, enter your database password at the prompt and press enter once again.

There will be a short delay whilst the data is imported into the database, and when completed will be presented with a screen like this:

The data should now have been imported into the database. If the .sql file is saved in a publicly accessible directory then after downloading it you should delete the file from the server, as anyone would then be able to come along and download a full copy of your database.

Hope This will help you if you are seeking this kind of information.

Thanks
Babor [http://aminulbabor.blogspot.com/2008/01/how-to-import-export-mysql-database-via.html]