MySQL Backup – How to Dump Remote Database Without mysqldump

backupMySQL

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.

Best Answer

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