How to dump remote database without mysqldump?

Question

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: 
mysqldump: Got error: 1045: Access denied for user 'user'@'localhost'
   (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 127.0.0.1 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.

Answer 1

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

Answer 2

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

Answer 3

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...

Answer 4

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...