How to download or copy large mysql databases with the help of putty?

This is one of the best examples I came across when I got tired in trying to download approax 1 GB of mysql database through phpmyadmin (export).

Download large size database(s) using putty ssh access:

1. Download and login to putty

Note:
– You need to download putty software. Google to get it.
– Your server must support access to SSH using third party software’s.
– User name and password of your hosting account (with proper privileges)

2. As soon as you get access to the shell window on putty. Change the current directory as per your preference.
3. Use the following commands to dump the mysql database or back up the mysql database.

shell> mysqldump databasename1 > databasename1_dump.sql  

If you get any access denied error. Please try the above command with your database username and password. Example:

shell> mysqldump -u dbusername -p databasename1 > databasename1_dump.sql     

As soon as you will hit enter key, you will be asked to enter the database password. Please enter the password and hit enter key again. In a few mins (time depends upon the size of database) the dump will be created. You can see the file through your cpanel or ftp as databasename1_dump.sql

You can download this file using ftp or cpanel now.

Copy or Duplicate MySQL Database:

In the above steps you learnt – how one can dump or take back up of MySQL database. Now, its time to copy or import that database into some other database.

I believe you are already logged into putty and your directory in shell window is pointing to the directory where the dump file is placed.

Use the following commands to import or copy a database into another database.

In case you want to create a database from Shell, you can use the following command:

shell> mysqladmin create anotherdatabasename

Now, here are the commands to copy or duplicate or import a small or large file size database:

shell>mysql anotherdatabasename < databasename1_dump.sql

If you get any access denied error. Please try the above command with your database username and password. Example:

shell> mysql -u dbusername -p anotherdatabasename < databasename1_dump.sql     

As soon as you will hit enter key, you will be asked to enter the database password. Please enter the password and hit enter key again. In a few mins (time depends upon the size of database) the dump will be imported or copied to the another database as supplied by you.

Hope this example was useful to you.

Thanks!

Related Post