通过SSH隧道从Node-RED连接远程MySQL数据库

Hey there! Let’s walk through solving your Node-RED + remote MySQL + dynamic IP + SSH tunnel challenge step by step. Since you already know Node.js, this should click pretty quickly once you see how Node-RED maps to what you already know.

Dynamic Pi IPs break fixed IP whitelisting, so we’ve got two solid options—one quick for testing, one secure for production.

1.1 Option 1: Loosen MySQL Remote Access (Quick Test, Less Secure) #

If you just want to validate the data flow first, you can allow your MySQL user to connect from any IP:

• Log into your virtual host’s MySQL console and run:

  GRANT INSERT, SELECT ON your_battery_db.* TO 'db_user'@'%' IDENTIFIED BY 'super_strong_password_here';
  FLUSH PRIVILEGES;
  

  (Limit permissions to only what you need—no ALL PRIVILEGES unless absolutely necessary.)
• Make sure your virtual host’s firewall opens port 3306.

Note: This is not ideal for long-term use, since it exposes your MySQL to the whole internet. Stick with the SSH tunnel method below for production.

1.2 Option 2: SSH Tunnel (Secure, Recommended) #

A persistent SSH tunnel acts as a private pipe between your Pi and virtual host, so you don’t need to expose MySQL directly or worry about dynamic IPs. Here’s how to set it up:

Step 1: Passwordless SSH Login (For Auto-Reconnect) #

First, let your Pi log into the virtual host without a password so the tunnel can stay alive:

• On your Pi, generate an SSH key (press enter for all prompts unless you want a passphrase):

  ssh-keygen -t ed25519 -C "pi-battery-tunnel"
  

• Copy the public key to your virtual host:

  ssh-copy-id vhost_user@your_vhost_domain.com
  

• Test it: ssh vhost_user@your_vhost_domain.com—you should log in without entering a password.

Step 2: Persistent Tunnel with autossh #

We’ll use autossh to keep the tunnel running even if the Pi reboots or the connection drops:

• Install autossh on your Pi:

  sudo apt update && sudo apt install autossh -y
  

• Create a systemd service to manage the tunnel (create the file with sudo nano /etc/systemd/system/mysql-tunnel.service):

  [Unit]
  Description=Persistent SSH Tunnel for MySQL
  After=network.target
  
  [Service]
  User=pi
  ExecStart=/usr/bin/autossh -M 0 -N -L 3307:localhost:3306 vhost_user@your_vhost_domain.com
  Restart=always
  RestartSec=10
  
  [Install]
  WantedBy=multi-user.target
  

  Breakdown:
  • -M 0: Disables autossh’s monitoring port (it’ll auto-detect connection drops)
  • -N: Just forwards ports, no remote command execution
  • -L 3307:localhost:3306: Maps your Pi’s local port 3307 to your virtual host’s MySQL port 3306
• Start and enable the service:

  sudo systemctl daemon-reload
  sudo systemctl start mysql-tunnel.service
  sudo systemctl enable mysql-tunnel.service
  

Now your Pi can connect to localhost:3307 to reach your remote MySQL—no dynamic IP issues!

Node-RED’s MySQL node is just a wrapper for the mysql2 library you know from Node.js, so this will feel familiar.

Step 1: Install the MySQL Node #

• Open your Node-RED editor (usually http://raspberrypi:1880)
• Click the menu → Manage Palette → Install
• Search for node-red-node-mysql and install it

Step 2: Configure the MySQL Connection #

• Drag a mysql node onto the canvas, double-click it
• Click Add new mysql-config:
  • Host: localhost (we’re using the tunnel’s local port)
  • Port: 3307 (the local port we mapped in the tunnel)
  • Database: Your battery database name
  • User/Password: Your MySQL credentials
• Click Update then Done

Step 3: Build the Data Upload Flow #

Let’s create a flow that takes sensor data, formats it, and inserts it into MySQL:

1. Inject Node: Drag one onto the canvas to test with sample data. Set its payload to JSON like:

   {"device_id": "battery_site_01", "voltage": 12.4, "timestamp": "{{timestamp()}}"}
   

2. Function Node: This is where you’ll format your SQL query (just like Node.js!). Paste this code:

   // Extract data from the incoming payload
   const { device_id, voltage, timestamp } = msg.payload;
   
   // Use parameterized queries to avoid SQL injection (critical!)
   msg.topic = `INSERT INTO battery_readings (device_id, voltage, created_at) VALUES (?, ?, ?)`;
   msg.payload = [device_id, voltage, timestamp];
   
   return msg;
   

3. Connect the Nodes: Link Inject → Function → MySQL
4. Debug Node: Add one connected to the MySQL node’s output to check if inserts succeed.

Step 4: Test the Flow #

Click the inject node’s button. Check the debug panel:

• A payload of 1 means 1 row was inserted successfully
• If you get errors, test the tunnel first with mysql -h localhost -P 3307 -u db_user -p your_battery_db on your Pi to confirm MySQL is reachable.

For your scattered battery units, repeat the setup on each Pi:

• Each Pi gets its own SSH key copied to the virtual host
• You can reuse the same local port (3307) on each Pi (since they’re separate devices, no port conflicts)
• Add a device_id field to your database table (like we did in the example) to distinguish data from each site
• For consistency, export your working Node-RED flow from one Pi and import it to the others—just adjust the device_id in the inject/input nodes.

• Tunnel drops? Check the service status with sudo systemctl status mysql-tunnel.service to see logs
• MySQL connection fails? Verify the tunnel is up with netstat -tulpn | grep 3307 on your Pi
• Security reminder: Never use your MySQL root user for Node-RED connections—create a dedicated user with only INSERT and SELECT permissions.

内容的提问来源于stack exchange，提问作者Tom van Nes