How to backup/restore database via SSH/Telnet

In order to back up your database via SSH/Telnet, you are required two things:

1. You have ROOT access to your server:

(VPS and Dedicated server only, thats not allowed on Shared hosting). Ask your hosting provider or check again the email they sent to you when your order was complete.

You need to know:

– Server ips

– Port (default: 22)

– Username (default: root)

– Password

**2.**An SSH/Telnet Client, such as PuTTy.

Download Putty here. (link)

Open Putty client then choose SSH connection type and enter your server ip address. (Host name or ip address)

putty client

3. Backup/Restore database Commands via SSH/Telnet:

Backup database commands:

mysqldump –opt -Q -udbusername -pdatabasename > backupdbname.sql

Or to create a backup in a separate directory (signified by /path/to/) type:

mysqldump –opt -Q -udbusername -pdatabasename > /path/to/backupdbname.sql

You will be prompted for the database password. Enter it and the database will backup.

If your hosting company has you on a remote MySQL server, such as mysql.yourhost.com, you will need to add the servername to the command line. The servername will be the same as in your config.php. The command line will be:

Current directory:

mysqldump –opt -Q -h servername -udbusername -pdatabasename > backupdbname.sql

Separate directory:

mysqldump –opt -Q -h servername -udbusername -pdatabasename > /path/to/backupdbname.sql

You can then, if you wish, download the backup to your home computer.

Restore Database Commands

mysql -udbusername -pdatabasename < backupdbname.sql

Or if you do not want to change directories and you know the path to where the backup is located, type in the following:

mysql -udbusername -pdatabasename < /path/to/backupdbname.sql

You will be prompted for the database password. Enter it .

If your hosting company has you on a remote MySQL server, such as mysql.yourhost.com, you will need to add the servername to the command line. The command line will be:

mysql -h servername -udbusername -pdatabasename < backupdbname.sql

Or:

mysql -h servername -udbusername -pdatabasename < /path/to/backupdbname.sql

Note: With Shared host, you can backup wizard of Cpanel to backup and restore your database.

You can also import your database (.sql file) via phpmyadmin >> FOR SMALL SIZE DATABASE Only

For Big size database , you should consider to use Bigbump script. More info here

Written on 11 April 2012

Share your thoughts