Tunnel MySQL connections over SSH through a firewall

You may be familiar with SSH as the secure terminal alternative to venerable telnet. However, SSH can do much more than just running a terminal to a remote computer. One of the interesting features is its port forwarding capability. This feature is documented in RFC 4254, Chapter 7, TCP/IP Port Forwarding.

For a setup at work I want to connect to a database server in a remote network. I can only access this network with SSH through the remote firewall. The firewall is blocking all traffic except SSH and HTTP, i.e. TCP connections on port 22 and 80 . Luckily, SSH is all I need to get to the database server. The SSH port forwarding feature allows me to create a (TCP/IP) tunnel over the network, through the firewall.

An SSH tunnel is not the same as a kernel level tunnel, such as BSD's tun. But that's OK to me in this case, since I'm connecting from a Debian GNU/Linux computer to an IBM pSeries AIX server. I don't want to delve into any AIX specifics, so SSH lowers the required level of knowledge as well :-).

As a bonus, the tunnel is encrypted, so the traffic that goes over the tunnel cannot be sniffed while in transit on the Internet.

Recipe

I ended up with this command:

jeroenp@tripel:~$ ssh -f -N -L localhost:4321:dbms.tycoon.com:3306 jeroenp@remote.tycoon.com

Explanation of command flags

The tunnel command in SSH is -L followed by a specification of which start point to connect to which end point: -L [bind_address]port:remote_host:hostport

bind_address

I'm explicitly binding the local start point to localhost so that this tunnel is not accessible from other computers on my local area network.

port

As local port, I chose 4321 because it is easy to remember and higher than 1024, so that I don't need to be root to listen at that port.

remote_host

The remote host is the computer where you would like to end up. This is the hostname of the database server in my example. Remember that this does not need to be the same hostname as the computer you will connect to via SSH. In my sample both hostnames are the same, but in production environments you may well want to connect to a database server where you don't have an account.

hostport

Port 3306 is the port that is used by the MySQL database server on the remote host for connections to the database.

I ended up with this -L specification:

-L localhost:4321:dbms.tycoon.com:3306

Two flags where added to the ssh command to make this tunnel work practically. If you leave them out, you'll start an ordinary SSH shell session, along with the tunnel;

-N indicates that no remote command, such as a shell, will be started. That's just what I need, since I'm only interested in the tunnel.

-f forks the ssh command from the shell, so that the SSH tunnel runs in the background and you can safely disconnect the terminal.

Connecting to the MySQL database

You can now connect to the database as if it were a local process. For example with the MySQL command line client:

jeroenp@tripel:~$ mysql -h localhost --port=4321 --protocol=TCP -u admin -p

The hostname for the database is now localhost. The value for port is the left, local port from the -L flag in the ssh command.

Troubleshooting

This recipe assumes you have a working SSH connection to the remote SSH server. Try running a SSH shell first, before you start experimenting with the tunnel. It is also worthwhile trying to run a command to connect from that SSH server to the final endpoint. E.g. in my case running:

jeroenp@remote.tycoon.com:~$ mysql -h dbms.tycoon.com -u admin -p

Another gotcha that got me: In case you're running a MySQL database on localhost as well, you may need to specify the protocol, to prevent MySQL from connecting to your local database server via your local UNIX domain socket.

Alternate use cases

The port forwarding principle is really simple, and is often used as a secure wrapper around POP3 email retrieval over a WLAN (WiFi) network. Instead of a MySQL database server, you might as well connect to Oracle, PostgreSQL or a JDBC database on port 1521.

If you want to go one step further, you could even connect the entire local and remote networks over the SSH tunnel. This HOWTO uses PPP to convert packets into a character stream and SSH to encrypt it and transmit it to the remote computer.

Honing the solution

I added some extra flags to the final SSH command

-n reconnects stdin to /dev/null so that no accidental character input is send over the line (but not the tunnel).

-6 tells SSH to only listen to IPv6. This saves an open socket. Which is fine and neat.

-2 just to be sure, only connect with the SSH v2 protocol.