Take the 2-minute tour ×

I want to dump the database on my remotely hosted site in regular intervals using a shell script. Unfortunately the server is locked down pretty tight, has no mysqldump installed, binary files can't be executed by normal users/in home directories (so I can't "install" it myself) and the database lives on a separate server, so I can't grab the files directly.

The only thing I can do is log into the webserver via SSH and establish a connection to the database server using the mysql command line client. How can I dump the contents to a file a la mysqldump in SQL format?
Bonus: If possible, how can I dump the contents directly to my end of the SSH connection?

I like the SSH tunnel idea proposed below, however, I'd need to double tunnel it.

localhost -> remote web server -> remote database server

This works okay:

$ ssh -f -L 3306:mysql.example.com:3306 user@ssh.example.com -N

However, trying to log in to mysql fails:

$ mysqldump -P 3306 -h localhost -u user -p db
Enter password: 
   (using password: YES) when trying to connect

I guess it expects the user credentials to be 'user'@'mysql.example.com'. How can I coax it into using that instead of the "real" host?

Okay, I got it to work by adding mysql.example.com to my hosts file and connecting via mysqldump -P 3306 -h mysql.example.com -u user -p db. Issue resolved! I'd still be interested in a solution that doesn't require me to edit the hosts file though.

share|improve this question
I'd suggest that you grant permissions for 'user'@'localhost' in addition to 'user'@'mysql.example.com' in MySQL, instead of editing the hosts file. –  Znarkus Feb 1 '11 at 15:49

4 Answers 4

up vote 12 down vote accepted

You could set an ssh tunnel and use mysqldump on your local machine.

ssh -f -L 3306:localhost:3306 user@remoteserver -N
mysqldump -P 3306 -h localhost -u dbuser dbname
share|improve this answer
Thanks, figured it out! –  deceze Apr 21 '10 at 3:27
On Unix, 'localhost' is treated differently. In which, the port number is ignored, you need to use to make this work. dev.mysql.com/doc/refman/5.0/en/connecting.html –  Adrian Gunawan Jan 19 '14 at 23:41

http://sypex.net/en/ - you can use this script for database backup after uploading it to server. Very handy and quick

share|improve this answer

Upload this script to your web server (hope you have at least FTP access), start it, specify database name/host(address of your "separate server")/username/password and dump database to any location you want...

share|improve this answer

If your server is a Web server, you can use a PHP script which dump the database to screen. On your backup server, put a cron which wget the dump page. Then your database is dumped to your backup server just by HTTP.

Don't forget security : Everybody is available to dump your base. Put a password, or an IP limitation...

share|improve this answer

Your Answer


