Running mysqldump via SSH on separate mysql server

I've got an hosting environment consisting of two separate servers: web-server and db server. The web-server is accessible via SSH only (and of course port 80). The DB server is inaccessible from the Internet and can only be accessed via the web-server.

The web-server doesn't have mysqldump and the space on the hard disk wouldn't allow to take a DB dump on the web-server.

I would like to use my local mysqldump and connect it via the web-server to the DB server. I'm not sure how to get the bash commands on order to work. I've tried this:

SSH Tunnel

ssh -f -L 3312:localhost:3306 user@web-server -i ~/.ssh/key-file -c cipher -N

mysqldump

mysqldump -P 3312 -h 127.0.0.1 -u user -p database > /tmp/db.sql

I'm aware that the db server needs to be included in the commands somehow, but I'm unsure how and where to include the IP of the DB server.

Solution 1:

The -L parameter for SSH has three values. From your example:

• 3312 is the local port SSH listens to on the computer where you execute SSH
• localhost is the destination host that remote SSH daemon opens the tunnel to
• 3306 is the destination port that repote SSH daemon opens the tunnel to

In order to get mysqldump connect to DB server's port 3306 via localhost port 3312, the SSH command needs to be:

ssh -f -L 3312:<dbserver ip>:3306 user@web-server -i ~/.ssh/key-file -c cipher -N