Node.js创建SSH隧道连接AWS RDS数据库失败求助

我是一名开发新手，近日尝试通过Node.js创建SSH隧道以连接AWS上托管的RDS数据库，但始终未能成功。

提供的终端连接命令ssh -N -L 33306:justoaca-db-prod.ciengjumlrqb.us-east-1.rds.amazonaws.com:3306 ec2-user@3.209.213.83 -i ~/.ssh/Justoaca-db-bridge.pem可正常建立隧道并访问数据库，但使用Node.js实现时出现多种问题：

• 使用ssh2库时，因SSH配置端口错误（误填33306而非默认的22），导致连接超时或直接失败；同时将SSH方法包裹在Promise中时会一直加载直至超时，不包裹则无法连接；
• 使用child_process的exec执行终端命令时，连接偶尔成功但极不稳定，无代码修改情况下有时可用有时失效。

尝试的代码 #

原ssh2实现代码 #

const mysql = require("mysql");
const { exec } = require('child_process');
const path = require('path');
const os = require('os');
var Client = require('ssh2').Client;
const { readFileSync } = require('fs');

class dbReadOnly {
  constructor() {
    this.dbConfig = {
      host: "127.0.0.1",
      port: 33306, // here I tried with port 22 and 3306
      user: "justoacaread",
      password: "XXXXXX",
      database: "justoaca",
    };

    this.tunnel = null;
  }

  connect(callback) {
    let sshConfig = {
      username: "ec2-user",
      host: "3.209.213.83",
      port: 33306, //tried here with 3306 and 22 too. 
      privateKey: readFileSync("./certificates/Justoaca-db-bridge.pem"),
    };

    console.log("Connecting...");
    const conn = new Client();
    conn.on('ready', () => {
      console.log("SSH connection established.");
      // Establish SSH tunnel to the remote database
      conn.forwardOut(
        // Source: Local IP and port for forwarding
        '127.0.0.1',
        33306,
        // Destination: IP and port of the remote database
        'justoaca-db-prod.ciengjumlrqb.us-east-1.rds.amazonaws.com',
        3306,
        (err, stream) => {
          if (err) {
            console.error('Error establishing SSH tunnel:', err);
            callback(err);
            return;
          }

          console.log('SSH tunnel established successfully.');

          // Create a database connection using the SSH tunnel stream
          const dbConnection = mysql.createConnection({
            host: '127.0.0.1',
            port: 33306,
            user: 'justoacaread',
            password: 'XXXXXXXX',
            database: 'justoaca',
            stream: stream, // Pass the SSH tunnel stream as the communication channel
          });

          dbConnection.connect((err) => {
            if (err) {
              console.error('Error connecting to the database:', err);
              callback(err);
              return;
            }

            console.log('Connected to the database successfully.');
            callback(null, dbConnection);
          });
        }
      );
    })
      .on('error', (err) => {
        console.error('Error in SSH connection:', err);
        callback(err);
      })
      .connect(sshConfig);
  }

  async query(sql) {
    console.log("Executing query");
    return new Promise((resolve, reject) => {
      this.connection.query(sql, (error, results) => {
        if (error) {
          console.error("Error executing the query:", error);
          reject(error);
        } else {
          console.log("Resolving query results");
          resolve(results);
        }
      });
    });
  }

  async disconnect() {
    console.log("DISCONNECTING!");
    //This method was used when i tried to create the ssh with the exec command from the module child_process

    // Command to stop the SSH tunnel process
    let killCommand;
    if (os.platform() === 'win32') {
      // Windows
      killCommand = 'taskkill /IM "ssh.exe" /F';
    } else {
      // Other Unix-based systems
      killCommand = 'pkill -f "ssh -N -L 33306:justoaca-db-prod.ciengjumlrqb.us-east-1.rds.amazonaws.com:3306"';
    }

    await exec(killCommand, (error, stdout, stderr) => {
      if (error) {
        console.error('Error executing the SSH disconnect command:', error);
        return;
      }

      // The command executed successfully
      console.log('SSH disconnect command executed successfully.');
      console.log('Output:', stdout);
    });

    this.connection.end();

    if (this.tunnel) {
      this.tunnel = null;
    }
  }
}

module.exports = new dbReadOnly();

child_process的connect方法代码 #

async connect() {
  console.log("connecting...")
  const command = 'ssh -vvv -N -L 33306:justoaca-db-prod.ciengjumlrqb.us-east-1.rds.amazonaws.com:3306 ec2-user@3.209.213.83 -i ./certificates/Justoaca-db-bridge.pem';
  await exec(command, (error, stdout, stderr) => {
    if (error) {
      console.error('Error executing SSH:', error);
      return;
    }
  });
 
}

方案一：修复ssh2库实现的核心问题 #

1. 核心修正点 #

• SSH连接EC2实例必须用默认端口22，而非本地转发端口33306或RDS端口3306
• 将整个SSH+数据库连接逻辑封装为Promise，解决时序问题，确保连接完成后再执行查询

修正后的完整代码 #

const mysql = require("mysql");
const Client = require('ssh2').Client;
const { readFileSync } = require('fs');

class dbReadOnly {
  constructor() {
    this.dbConfig = {
      user: "justoacaread",
      password: "XXXXXX",
      database: "justoaca",
    };
    this.sshConn = null;
    this.dbConn = null;
  }

  // 封装为Promise的连接方法
  async connect() {
    return new Promise((resolve, reject) => {
      const sshConfig = {
        username: "ec2-user",
        host: "3.209.213.83",
        port: 22, // 必须是SSH默认端口22
        privateKey: readFileSync("./certificates/Justoaca-db-bridge.pem"),
      };

      console.log("Connecting to SSH...");
      this.sshConn = new Client();

      this.sshConn.on('ready', () => {
        console.log("SSH connection established.");
        // 建立端口转发：本地任意地址监听33306，转发到RDS的3306
        this.sshConn.forwardOut(
          '0.0.0.0', // 本地监听地址，允许所有本地连接
          33306,
          'justoaca-db-prod.ciengjumlrqb.us-east-1.rds.amazonaws.com',
          3306,
          (err, stream) => {
            if (err) {
              console.error('Error establishing SSH tunnel:', err);
              this.sshConn.end();
              return reject(err);
            }

            console.log('SSH tunnel established successfully.');
            // 创建数据库连接，使用转发后的本地端口
            this.dbConn = mysql.createConnection({
              ...this.dbConfig,
              host: '127.0.0.1',
              port: 33306,
            });

            this.dbConn.connect((err) => {
              if (err) {
                console.error('Error connecting to database:', err);
                stream.end();
                this.sshConn.end();
                return reject(err);
              }
              console.log('Connected to database successfully.');
              resolve(this.dbConn);
            });
          }
        );
      })
      .on('error', (err) => {
        console.error('SSH connection error:', err);
        reject(err);
      })
      .connect(sshConfig);
    });
  }

  async query(sql) {
    if (!this.dbConn) {
      throw new Error('Database not connected, call connect() first');
    }
    return new Promise((resolve, reject) => {
      this.dbConn.query(sql, (error, results) => {
        if (error) {
          console.error('Query error:', error);
          reject(error);
        } else {
          resolve(results);
        }
      });
    });
  }

  async disconnect() {
    console.log("Disconnecting...");
    if (this.dbConn) {
      await new Promise((resolve) => this.dbConn.end(resolve));
      this.dbConn = null;
    }
    if (this.sshConn) {
      this.sshConn.end();
      this.sshConn = null;
    }
    console.log('Disconnected successfully.');
  }
}

module.exports = new dbReadOnly();

使用示例 #

const db = require('./dbReadOnly');

(async () => {
  try {
    await db.connect();
    const results = await db.query('SELECT * FROM your_table LIMIT 10');
    console.log('Query results:', results);
  } catch (err) {
    console.error('Error:', err);
  } finally {
    await db.disconnect();
  }
})();

方案二：稳定化child_process实现 #

如果坚持用系统SSH命令，改用spawn替代exec，并监听进程状态确保隧道稳定建立：

修正后的connect方法 #

const { spawn } = require('child_process');
const waitOn = require('wait-on'); // 先安装：npm install wait-on

async connect() {
  return new Promise((resolve, reject) => {
    const command = 'ssh';
    const args = [
      '-N',
      '-L', '33306:justoaca-db-prod.ciengjumlrqb.us-east-1.rds.amazonaws.com:3306',
      'ec2-user@3.209.213.83',
      '-i', './certificates/Justoaca-db-bridge.pem'
    ];

    this.tunnel = spawn(command, args);

    // 监听错误输出判断连接状态
    this.tunnel.stderr.on('data', (data) => {
      const msg = data.toString();
      if (msg.includes('debug1: Connection established')) {
        console.log('SSH tunnel established.');
        // 等待本地端口33306可用
        waitOn({ resources: ['tcp:127.0.0.1:33306'] }, (err) => {
          if (err) return reject(err);
          // 连接数据库
          this.dbConn = mysql.createConnection(this.dbConfig);
          this.dbConn.connect((err) => {
            if (err) return reject(err);
            console.log('Database connected.');
            resolve();
          });
        });
      } else if (msg.includes('Permission denied') || msg.includes('Connection timed out')) {
        reject(new Error(`SSH error: ${msg}`));
        this.tunnel.kill();
      }
    });

    this.tunnel.on('error', (err) => {
      console.error('Spawn error:', err);
      reject(err);
    });

    this.tunnel.on('exit', (code) => {
      if (code !== 0) {
        reject(new Error(`SSH tunnel exited with code ${code}`));
      }
    });
  });
}

说明 #

• spawn适合长期运行的进程，避免exec的缓冲区溢出问题
• 通过监听SSH的stderr输出判断连接状态
• 用wait-on确保本地转发端口可用后再连接数据库

关键注意事项 #

• 确保EC2安全组允许本地IP访问22端口
• 确保RDS安全组允许EC2实例IP访问3306端口
• Unix系统需设置PEM证书权限：chmod 600 ./certificates/Justoaca-db-bridge.pem
• 避免同时运行多个隧道进程，防止端口占用冲突

内容的提问来源于stack exchange，提问作者Mauricio Vilar