Backing Up The Database via SSH/Telnet 
In order to back up your database via SSH or Telnet you will require 2 things:

1) SSH or Telnet access to your site. You will need to check with your hosting company to see if this is available.

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

Open your SSH/Telnet client and log into your website. The command line prompt you will see will vary by OS.
For most hosting companies, this will bring you into the FTP root folder.

Type in the following to create a backup in the current directory:

mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

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

mysqldump --opt -Q -u dbusername -p databasename > /path/to/backupname.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 -u dbusername -p databasename > backupname.sql

Separate directory:

mysqldump --opt -Q -h servername -u dbusername -p databasename > /path/to/backupname.sql

You can then, if you wish, download the backup to your home computer.
User Contributed Notes: Backing Up The Database via SSH/Telnet Add a Comment
Kay 20th Apr 2005, 11:06am
The best thing you could do is create a bash script that backs up all your DBs automatically (via cron) and have them sent to your email or load them on a remote server via sftp
Dean Clatworthy 20th Apr 2005, 11:11am
Just a quick note, here's a link to putty:

http://www.chiark.greenend.org.uk/~sgtatham/putty/
Ianomed 20th Apr 2005, 11:11am
If you run MySQL 4.0.23+ or 4.1.8+ you best supply the switch --hex-blob as well. Otherwise if you have thumbnails, attachments and/or avatars stored in the database, you may run into problems restoring these in-tact. These MySQL versions of course also apply on restoring, or the hexadecimal encoded binary fields won't be understood.

See http://dev.mysql.com/doc/mysql/en/mysqldump.html for additional details.
Zachery 20th Apr 2005, 07:38pm
As of MySQL 4.1 both --opt and -Q are on by default.
Hamy 18th Nov 2005, 12:35am
Use --compatible=mysql40 to backup unicode correct.
Dan Dlugos 28th Jan 2006, 11:42am
If you are getting errors like 'no such directory or file name', you must specify the file path from root, not from you user directory. This is especially important if you are on a shared hosting server. Often time this is something like /home/username/..., but check with your hosting company for your path to root if you are trying to run the shell command & get the no such directory error
Colin 29th Jun 2006, 09:50am
To specify a port, use -P (a CAPITAL p).
The whole string, with port 4000 for example would look like this:

mysqldump --opt -Q -h servername -P 4000 -u dbusername -p databasename > /path/to/backupname.sql
Erik Klein 11th Jun 2007, 01:16am
The -p in the commands listed above is the password.

Most have probably figured this out, but if you want the script to run without prompting for the password just put the password associated with the username immediately after the '-p' as in -pmypassword.

You can then set the script up in a cron job.
Add a Comment