Imagine you’re a secret agent, and you need to access a top-secret database hidden in your company’s local area network (LAN). The problem? This database doesn’t have a public IP address, so you can’t access it directly. What do you do? You create a secret tunnel, of course!

In this article, we’ll be your Q, providing you with the tech tools to create your own secret tunnel - an SSH tunnel - to connect your local database with an external server.

Step 1: Install MySQL

First, we need to set up our database. We’ll use MySQL, and Docker will help us install it quickly. It’s as simple as running the following command:

docker run --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root mysql:8

Step 2: Initialize Data

Once MySQL is installed, we can use MySQL client tools to connect to our local database. It’s like dialing into our secret agent headquarters:

mysql -uroot -proot

To make our mission easier, we’ll create an example database and table, and insert some data:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
flush privileges;
SELECT * from information_schema.user_privileges where grantee like "'root'%";
CREATE DATABASE example;
USE example;
CREATE TABLE `example` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NULL, PRIMARY KEY (`id`));
INSERT INTO `example` (`name`) VALUES ('I am local');

Step 3: Create the SSH Tunnel

Now comes the fun part: creating our secret tunnel. Let’s assume the IP address of our cloud server is xx.xx.xx.xx. We can use the SSH command to create a reverse tunnel, mapping our local 3306 port to the cloud server’s 13306 port:

ssh root@xx.xx.xx.xx -R 3306:0.0.0.0:3306 -N

This command logs into the cloud server as the root user and runs a tunnel process in the background, forwarding the cloud server’s 13306 port to our local 3306 port.

Step 4: Connect to the Database

Finally, we can connect to our database from the cloud server. It’s like walking through our secret tunnel and emerging in our local network:

mysql -h127.0.0.1 -P13306 -uroot -proot

Then, we can execute SQL statements to query or manipulate data:

USE example;
SELECT * from `example`;

The result should look something like this:

+----+------------+
| id | name       |
+----+------------+
|  1 | I am local |
+----+------------+

And there you have it! You’ve successfully accessed a database in a local area network from the cloud using an SSH tunnel. It’s like being a tech-savvy secret agent, but without the danger of being chased by villains. Happy tunneling!