[SOLVED] MySQL "tunnel jump" connection with nodejs

Issue

This is probably a duplicate but I can't find anything for this use case.

---

Setup:

• MySQL Instance - Only IPs on the whitelist can connect via ip/user/pass. No OS/SSH access.

• Authorized Server - I have root access. Is on the MySQL Instance's whitelist. Can mysql -h -u -p into the MySQL Instance.

• Dev Laptop - Windows Laptop. Cannot be on whitelist cause of dynamic dhcp. Can SSH into Authorized Server.

This is all IT's infrastructure and cannot be changed.

---

• I use DBeaver and set up "Use SSH Tunnel" to tunnel into the MySQL Instance through the Authorized Server no problem.

• I can tunnel into Authorized Server from Dev Laptop with git bash for windows as well. (This is not getting me anywhere though)

  ssh -L 3306:localhost:3306 Authorized_Server

How can Dev Laptop run nodejs mysql queries on the MySQL Instance?

Thank you,

Ryan

Solution

If I understand correctly your configuration, you do have something like:

+--------------+     SSH Tunnel      +-------------------+    MySQL Connection         +----------------+
|              |  ---------------->  |                   |  ------------------------>  |                |
|  Dev Laptop  |  (localhost:3306)   | Authorized Server |   (MySQL_Instance_IP:3306)  | MySQL Instance |
|              |  <----------------  |                   |  <------------------------  |                |
+--------------+                     +-------------------+                             +----------------+

                  <-------------------------- Data Flow ---------------------------->

• "Dev Laptop" represents your development laptop from where you are initiating the SSH tunnel.
• "Authorized Server" is the server that is whitelisted to access the MySQL instance.
• "MySQL Instance" is the ultimate destination where the MySQL database is hosted.
• "SSH Tunnel (localhost:3306)" represents the SSH tunnel established between your development laptop and the authorized server, forwarding the local port 3306 to the authorized server.
• "MySQL Connection (MySQL_Instance_IP:3306)" indicates the connection from the authorized server to the MySQL instance over the standard MySQL port (3306).

In order to set up a "tunnel jump" connection so that your development laptop can interact with the MySQL instance through a NodeJS application, you will need to set up a local SSH tunnel to the authorized server, and from there set up a connection to the MySQL instance.

On your development laptop, you could set up SSH tunneling to forward localhost port 3306 to port 3306 on the authorized server:

ssh -L 3306:MySQL_Instance_IP:3306 aUser@Authorized_Server

Do replace MySQL_Instance_IP with the IP address of the MySQL instance and Authorized_Server with the IP address or hostname of the authorized server.

This differs from ssh -L 3306:localhost:3306 Authorized_Server, which forwards traffic from port 3306 on your Dev Laptop to port 3306 on the localhost of the Authorized Server: if a MySQL server is running on the Authorized Server itself and is listening on localhost (127.0.0.1) on port 3306, this setup would work.
However, since your MySQL instance is on a different server, this tunnel does not facilitate the connection to the MySQL instance; hence you mentioned it "is not getting me anywhere."

ssh -L 3306:MySQL_Instance_IP:3306 aUser@Authorized_Server would set up an SSH tunnel that forwards traffic from port 3306 on your Dev Laptop to port 3306 on the MySQL instance through the Authorized Server. That would effectively creates a tunnel from your Dev Laptop, through the Authorized Server, to the MySQL server, allowing you to interact with the MySQL instance as if it were running locally on your Dev Laptop.

aUser represents a service account which has the the necessary permissions to log in via SSH. It is advisable to use a user account with the least privileges necessary to accomplish the task at hand, to adhere to the principle of least privilege (PoLP) and enhance security.

---

In your NodeJS application, set up a MySQL connection using localhost and port 3306 (since the SSH tunneling is forwarding this local port to the MySQL instance port through the authorized server). Create a file (e.g., mysqlConnection.js) with:

const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'your_mysql_username',
  password: 'your_mysql_password',
  database: 'your_database_name',
  port: 3306
});

connection.connect(error => {
  if (error) {
    console.error('Error connecting to the database: ' + error.stack);
    return;
  }
  console.log('Connected to the database as ID ' + connection.threadId);
});

module.exports = connection;

Replace 'your_mysql_username', 'your_mysql_password', and 'your_database_name' with your MySQL instance username, password, and database name, respectively.

Create a NodeJS script where you want to run MySQL queries. For instance, in a file named app.js, require the mysqlConnection.js file and run your queries as shown below:

const connection = require('./mysqlConnection');

connection.query('SELECT 1 + 1 AS solution', (error, results, fields) => {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

Now, run the script:

node app.js

That script should run a MySQL query to add 1 + 1 and then console log the solution. You should see "The solution is: 2" in your console if everything is set up correctly.

Answered By - VonC

Answer Checked By - Katrina (WPSolving Volunteer)