In my work, I usually take on corporate clients. Often, they require on-premises solutions. Sometimes, they provide me with access to internal servers that do not have an external IP address. So it can be hard to administer these servers from the outside.
Thankfully, these servers still have access to the public internet. Therefore, I use a technique called Reverse SSH to establish connection with said server from the outside. In this article, I will elaborate about how to establish a database connection from a client outside to the MySQL server sitting on the internal server.
Setup of Situation
Alice: The on-premises internal server (assumed to be running on Ubuntu and installed MySQL using the default port 3306)
Bob: The client from the outside trying to access (any operating system that can perform SSH easily and has a MySQL client to test with . Default as Mac OSX. Can also be any standard Linux OS)
Fayth: The middleman server that Alice will connect to (also assumed to be Ubuntu)
On Alice, you may need to install packages such as
autossh. Because this article touches on establishing MySQL connection over ReverseSSH, you definitely need to have installed MySQL. When you are ready with all these assumptions, you can go to the next section.
Part 1: Establishing Connection from Alice to Fayth
There are two parts to the whole setup. The first part of the whole setup involves setting up connection between Alice to Fayth even though our objective is to connect from Bob to Alice. Hence, the word “Reverse” in Reverse SSH. Meaning, if this part doesn’t work, the second part does not matter.
How to setup initial SSH connection from Alice to Fayth
Alice SSH into Fayth. I usually do this by opening another
screen so as to avoid disrupting the main screens.
# as alice@Alice screen # now in say screen1.ttyl of Alice ssh fayth@fayth # now in fayth which proves the SSH works # type exit + ENTER to go back to Alice # now at #press CTRL+a then d to detach screen1.ttyl and go back to main screen
Note that screen1.ttyl still exists even after you detach from it. Now you are ready to establish MySQL over the SSH connection to Alice
Let’s reconnect to Fayth and establish a Reverse SSH connection.
# as alice@Alice reattach the detached screen screen -d -r # Because Alice knows you only have 1 detached screen, it will pick the right one which is screen1.ttyl # Note the 19922 port number I use here you need it for part 2 autossh -R 19922:localhost:22 fayth@Fayth # now in fayth and the reverse SSH is established. You can detach by press CTRL+a then d to detach screen1.ttyl and go back to main screen
How to open MySQL over SSH connection from Alice to Fayth
For MySQL, you need to do a similar step as the previous section. Similarly, I recommend using a separate
screen to do so.
# as alice@Alice screen # this creates yet another detached screen let's call this screen-mysql.ttyl of Alice # Note the 19306 port number I use here you need it for part 2 autossh -R 19306:localhost:3306 fayth@Fayth # Now in fayth and the reverse SSH is established. You can detach by press CTRL+a then d to detach screen-mysql.ttyl and go back to main screen
If you can find yourself in Fayth, this means that the MySQL port 3306 in Alice is now accessible via the 19306 port in Fayth. This is the key for your MySQL connection from Bob to Alice in Part 2.
Part 2: Establishing Connection from Bob to Alice
In Part 2, you can connect directly from Bob to Alice via Fayth thanks to the work done in Part 1.
How to connect SSH from Bob to Alice
# as bob@Bob ssh fayth@Fayth # Now in Fayth and you need to use the port number in the previous section ssh alice@localhost -p19922 # Now in Alice hence proving it works!
As you can see from the commands above, you are simply hopping from Bob to Fayth and then from Fayth to Alice, so long as Alice is connecting to Fayth as stated in Part 1.
How to connect MySQL from Bob to Alice
If you have tried the previous exercise, you have now confirmed that the Reverse SSH works. If you do not need reverse SSH, you can stop the SSH connection in screen1.ttyl from Alice to Fayth for the MySQL connection. What you do need for the following to work is the SSH connection you established in screen-mysql.ttyl from Alice to Fayth.
- Open your favorite MySQL client. In my case, I will use sequel pro but this should work regardless the client. So you need to figure out the exact clicks and commands meant for your favorite MySQL client
- Type in the MySQL database username and password meant for the one in Alice. Your database host should also be 127.0.0.1 or localhost depending on your MySQL setup in Alice.
- Type in the port as 19306. Note this is the same port I chose in Part 1 for the MySQL connection
- Pick your SSH user and host as fayth@Fayth
- Pick your SSH port as 22
Test the connection. It should work now.