How to setup an SSH tunnel on Linux and use it to connect to remote mysql database (+ node/php examples)

How to setup an SSH tunnel on Linux and use it to connect to remote mysql database (+ node/php examples)

Introduction

This article is a step by step tutorial on how to setup an ssh tunnel -reliably ensuring it's running, and automatically restart it when necessary- to forward local traffic to remote destination. This works on servers running Linux and is intended to help people setup an ssh tunnel to connect to external services ie. mysql that's running on remote server. To follow along, a basic knowledge of ssh, Linux shell and cron is highly recommended.

There is a light at the end of every tunnel - Quote
There is a light at the end of every tunnel - Quote

Basics

During your programming/IT career, there is a high chance that you've already used SSH. Whether connecting to a remote server or cloning a git repository. Either ways, both involving SSH on a basic level. But beyond these two uses-cases, SSH offers some others functionalities and purposes which may boost your workflow and sometimes are necessary. We are talking about SSH tunneling -aka local or remote port forwarding. In this article, our main focus is the local port forwarding, especially when trying to connect from your application/script code to an external remote server; as an example, connecting nodejs script to mysql database in a remote server through ssh. Finish talking, let's get the hands dirty!

Discussing possible approaches

Since we want to access a remote service, mysql database from a nodejs script as an example, we can use the tunnel-ssh npm package. This is absolutely doable, especially when you don't have enough access to create a system-level ssh tunnel using the command line. The main problem regarding this approach is that both the software and the platform are tied together. Personally, I don't want them tied and I prefer separating concerns: your code shouldn't know about any ssh tunneling, and your ssh tunneling shouldn't depend on a particular script to be running.

Setup

We are assuming that you have two running servers (Server_A acting as the client, Server_B acting as the remote database server, running mysql on the default 3306 port), and you can connect from Server_A to Server_B using your favorite ssh client, i.e simply: ssh -i my_private_key server_b_user@server_b_ip ( Personally I prefer using password-less ssh login using keys, but the same tutorial applies if you want to use ssh password-full login, the main difference is that you will be prompted to enter it)

Having our requirements met, creating an ssh tunnel from Server_A to Server_B is quite simple, its a single command that you have to run:

ssh -L [local_ip:]local_port:localhost:remote_service_port -N -i my_private_key server_b_user@server_b_ip

  • -L: here we specify the type of port forwarding ( -L for local, -R for remote and -D for dynamics )
  • local_port: The local machine IP address and port number. When local_ip is omitted, the ssh client binds on the localhost.
  • localhost:remote_service_port: The IP/Hostname and the port of the remote server's service.
  • -N: not to execute a remote command
  • -i my_private_key, server_b_user and server_b_ip: the key, the ssh user and the server IP address.In our specific use case -trying to connect to a remote mysql service running on Server_B- the above command will be (I choose local available port 33066 but you could use any available port bigger than 1024. Ports =< 1024 are root restricted):


ssh -L 33066:localhost:3306 -N -i my_private_key server_b_user@server_b_ip

This way, when we try to connect to localhost at port 33066, the request will be forwarded to remote server 3306 using the ssh protocol. We can test if we can connect using telnet (you can otherwise use the netcat command):

Testing connectivity using telnet client
Testing connectivity using telnet client

To connect from your nodejs script or you php file, simply you have to configure a mysql connection to use the local address and port used above, i.e 33066, and then specify the remote mysql user credentials as if you were connecting from withing the remote server: ie localhost:3306:

nodejs example:

php example:

Probably you noticed that our nodejs/php clients don't know about any ssh tunneling, that's what we must always aim at πŸ˜€

Ensuring the tunnel is always up

This command is useful when you launch it but once you exit the terminal or reboot your computer/server, the tunnel will be down, your connection is broke and you have to launch it again. In order to keep it running and reliably ensure it gets restarted when necessary/not working properly, there is a multitude of choices and you can use the autossh package as it does the job but it requires more setup. My favorite and personal alternative is a simple cronjob that does the job pretty well. It's a cronjob that calls a small bash script every 5 minutes to check if my tunnel is properly running otherwise launch it:

Conclusion

This is the end of this tutorial, we have seen how to create a ssh tunnel, ensure it's always UP with two examples on how we can connect to remote mysql database using nodejs/php. I hope this was helpful and if you encounter any kinds of problems or you do have suggestions for improvement, your comment is highly welcome. The most important thing to remember here is that there is always light at the end of the tunnelπŸ˜ƒ