Node.js ssh2 隧道保持活动并执行 mysql 查询 [英] Node.js ssh2 tunnel keep alive and execute mysql queries

问题描述

我正在使用 node.js 实现一个 rest api，它使用 ssh2 包 隧道连接到 远程 mysql 数据库.这是我用来通过 ssh 连接并执行 query

I am implementing a rest api using node.js that uses ssh2 package tunneling to connect to the remote mysql database. Here is the code I am using to connect via ssh and to execute query

executeQuery : function(query, callback) {
        var sshConnected = false;
        var connection = require('ssh2').Client();
        var server = require('net').createServer(function(sock) {
            if (!sshConnected) return sock.end();
            connection.forwardOut(
                '127.0.0.1',
                sock.remotePort,
                '127.0.0.1',
                3306,
                function (err, stream) {
                    if (err) return sock.end();
                    stream.pipe(sock).pipe(stream);
                });
        });
        connection.on('ready', function() {
            console.log('Client :: ready');
            sshConnected = true;
            server.listen(3306);
            var sqlconn = require('mysql').createConnection(config.dbOrg);
            sqlconn.connect(function (conError) {
                if (!conError) {
                    console.log(query);
                    sqlconn.query({sql: query.sql, values: query.values}, function (qError, results, fields) {
                        callback(qError, results, fields);
                    });
                } else {
                    callback(response.dbError, null, null);
                }
                server.close();
            });
        }).connect(config.sshConfig);

但是，如您所见，它的效率并不高.

However, as you can see it is not very efficient.

对于每个新查询，我都会再次建立连接，启动 TCP 服务器并在查询后停止它.每个查询大约需要 3-4s，这真的很糟糕.任何让 ssh 连接 和 tcp 服务器 保持活动状态以在它们到达时执行 queries 的方法都会有很大帮助.谢谢！

For every new query, I am establishing the connection again, starting the TCP Server and stopping it after query. Each query takes roughly around 3-4s which is really bad. Any approaches keeping both the ssh connection and the tcp server alive to execute queries as they arrive, would be of great help. Thanks!

推荐答案

在直接使用数据库驱动的情况下，如果使用mysql2 而不是 mysql，您实际上可以将 ssh 转发流作为底层连接传递给数据库驱动程序使用.这将允许您不必创建额外的服务器来侦听要通过隧道传输的传入连接.所以你可以做这样的事情:

In the case of using the database driver directly, if you use mysql2 instead of mysql, you can actually pass the ssh forwarded stream as the underlying connection to be used by the database driver. This will allow you to skip having to create an additional server that listens for incoming connections to be tunneled. So you can do something like this instead:

var mysql = require('mysql2');
var Client = require('ssh2').Client;

var ssh = new Client();
ssh.on('ready', function() {
  ssh.forwardOut(
    '127.0.0.1',
    12345,
    '127.0.0.1',
    3306,
    function (err, stream) {
      if (err) throw err;
      var sql = mysql.createConnection({
        user: 'foo',
        database: 'test',
        stream: stream // <--- this is the important part
      });
      // use sql connection as usual
  });
}).connect({
  // ssh connection config ...
});

这篇关于Node.js ssh2 隧道保持活动并执行 mysql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！