Mysqldump via SSH Tunnel

There are plenty of explanations how to run a MySQL dump via an SSH tunnel, but none of them was extensive enough for me, so I try it on my own.

SSH Tunnel

First log into the machine you want the sql dump to show up. As described in the next post, make sure to use the version of mysqldump of the server you want to import into!

ssh -C -L 3307:database.example.org:3306 -N user@webserver.example.org

So let’s explain this part by part:

1. -C compress the connection, sometimes that speeds things up by a lot.
2. -N makes sure to not accidentally run a command
3. and the server in the end is the one we connect to user@webserver.example.org and tunnel through
4. Now the important and interesting part: -L 3307:database.example.org:3306, we are tunnelling normally through the web server we have ssh access to. And then we connect to the database server. Many times the web and the db server are the same, therefore it looks like this:
   -L 3307:127.0.01:3306, but sometimes the database server has its own machine and its own hostname. (My)SQL runs normally on Port 3306, this is the port our tunnel ends: database.example.org:3306. And where does it start? Exactly on our machine on Port 3307. So once you know this, you can read it easily:
   The tunnel starts on our local machine on Port 3307 and goes to database.example.org:3306

Hope that helps you to understand ssh tunnel

MySQL dump through the SSH tunnel:

mysqldump -P3307 -h 127.0.0.1 database

Two important things to do here:

1. Use -h 127.0.0.1 which tells mysqldump to use 127.0.0.1 as the host. Because the default is to use the local socket.
2. Use -P3307 which is our local port 3307 – the port our SSH tunnel starts 🙂