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]

PuTTY is tops on the short list of applications I install first on any Windows machine. Over the years I’ve used PuTTY, I’ve installed it on a huge number of computers but I’ve always had one complaint; There isn’t a configuration file I can backup or move to a new machine.

Getting all the little comfort settings correct for each SSH connection can be a pain. The third time you set emulation, keys, encryption type, etc. for each machine you regularly access, you lose patience. The fifth time you find yourself re-entering settings you might do what I did, find a better way.
Moving PuTTy settings between computers is an easy task once you know what’s involved. We’ll find the registry keys where PuTTy stores its configuration information and export them to a file. We’ll then use that file we’ve made to import our configuration on the target machine.

Exporting Your PuTTy Configuration

Putty stores its settings in the Windows registry. To save a backup of your Putty settings, you’ll need to export this registry key to a file.

HKEY_CURRENT_USERSoftwareSimonTatham

(Simon Tatham is the original developer responsible for PuTTy)

1. Click Start->Run and type “RegEdt32″ in the “Open” dialog. Click “Ok”

2. One RegEdt32 starts, you’ll be presented with an application which looks something like:

3. Press “Ctrl+F” to bring up the Find dialog. Enter the name of the key, “SimonTratham” in the “Find What” field, and make sure only “Keys” is checked in the “Look At” section of the dialog. Finally, click “Find Next”

4. The search may take a while, reminding us that the Windows Registry is a large and mysterious place where dragons be. Let’s use these few seconds to reflect on the fact that you should never, ever, never change things in the registry unless you are absolutely, positively, totally, completely, 100% dead sure that you know exactly what you’re doing. When the search completes we’ll see the key name for which we’re looking.

5. Click File->Export. Give your file an appropriate name like, “putty.reg” and click “Save”

Importing Your PuTTy Configuration

To import your saved PuTTy configuration on any other Windows computer simply copy your exported registry key, right click on the file and click “Merge”

Windows will ask you for confirmation that you want to import this set of registry values. We know this file is safe, because we created it but, you should never import registry information from an unknown source.

That’s all you need to know about moving your PuTTy configuration from one machine to another. This can be really useful information when upgrading to a new PC or, if you’re an office IT guy where your users all have a standard list of servers they need to connect via SSH, you can create a reference configuration on once machine and “share” it between every computer in the office.