MySql SSH tunneling

What is SSH tunneling?


This tutorial in Spanish.

SSH tunneling is a method that allows us to create a secure reliable connection (tunnel) between our computer and a remote server. The connection consists of creating a link between our machine and the remote server by means of port forwarding.

The general idea is to forward all of the traffic from a local port to the tunnel that will communicate in this case with the server’s port.

This is particularly useful when it comes to talking about the development process, given that it might be necessary to interact with a remotely-hosted database. For this matter we will need to keep a reliable connection between our computer (in which we will be running a MySql client) and the host of the database (the source that will feed the client on our computer). Basically our computer will believe that is serving a local database.

This procedure allows us to use the remote database through a forwarding SSH tunnel that avoid ports exposure. This helps us to have an encrypted connection when smuggling data from/to the database’s server.

Some free open source MySql clients you might be able to use on mac/linux:

  • MySql workbench: Linux/mac

  • Sequel Pro: Mac

  • PHPMyAdmin: Linux/mac/windows

  • Neor Profile SQL: Linux/mac/windows

SSH tunnel configuration with MySql

I will first show you how to create the tunnel manually and then with a MySql client.

NOTE: To have easier access to the SSH tunnel, you might need to attach your ssh-key fingerprint to the remote server

Create an SSH tunnel manually

To create an SSH tunnel manually you need to run the following command on your linux/mac machine:

ssh -L [local port]:[database host]:[remote port] \ [username]@[remote host]

Example

ssh -L 3306:127.0.0.1:3306 \ remote_user@192.169.0.10

Where:

[local port = 3306]: Is the port from your local machine that you are going to open in order to communicate with the remote server (the traffic will be taken from this port and forwarded to [database host]:[port] through the ssh tunnel). Use 3306 (which is the default one on MySql) for MySql connections or any other if you have configured it.

[database host = 127.0.0.1]: The server address where the database is hosted. If the db host in the same as the [remote host = 192.169.0.10], then you just need to use 127.0.0.1 here in order to create a reference to itself.

[remote port = 3306]: The port that is open on the remote host in order to be linked through the ssh tunnel

[username = remote_user]: This is the username you will use to authenticate against the remote server.

[remote host = 192.169.0.10]: Domain or IP address where the remote database is hosted.

From here if we have set the ports to 3306 or whatever you set on the túnel configuration, all connections going through the port set on the SSH tunnel configuration will use the tunnel forwarding.

And at this point you should have successfully created a tunnel which you can use to interact with MySql data source remotely.

Create an SSH tunnel for MySql workbench

Many people could dislike a solution that involves command-line interfaces to manipulate databases, besides, working with a database graphical client could make things easier and faster. That is why it is very useful to configure our MySql client to work with this SSH tunnel connection. We will be using MySql Workbench for the purpose of this article.

You can get MySql Workbench from here.

To configure the tunnel for this client, go to Manage server connections.

You will be presented with a screen like this:

picture

On the left side select a connection.

On the tab connection. As you see in the picture above, there are a number of configurable options like:

  • SSH host name: The server name where the db is be hosted on.

  • SSH username: Username that has access to the remote server.

  • SSH password: The password that will be used to authenticate against the the remote server

  • SSH key file: The path to the SSH key file in order to grant direct access when trying to connect.

These options just look pretty much like the manual configuration we recently did. You will just need to fill all of those fields in order to get the configuration required and be able to establish the SSH tunnel connection between your MySql client and the server.

Conclusions

Whenever we need to connect to a remotely hosted MySql database using a secured link between our machine and the remote server, we can use SSH tunneling connection. which will handle a secure connection between the remote server and our local machine by means of port forwarding (avoiding ports exposure). This can be set manually to the port number that was configured for MySql (3306 by default) and from then, every local connection to it will use port forwarding directly to the server’s port through the SSH tunnel. If we want to use a MySql graphical client such as MySql workbench you can also use these setting though the Manage server connections wizard.

References

https://help.ubuntu.com/community/SSH/OpenSSH/PortForwarding

https://support.cloud.engineyard.com/hc/en-us/articles/205408088-Access-Your-Database-Remotely-Through-an-SSH-Tunnel

https://mediatemple.net/community/products/dv/204403884/tunnel-local-mysql-server-through-ssh

https://hostpresto.com/community/tutorials/how-to-connect-to-a-remote-mysql-server-via-an-ssh-tunnel/

http://dev.mysql.com/doc/workbench/en/wb-mysql-connections-methods-ssh.html


*Article by: Victor Rivas at Bixlabs, Uruguay*