Nick Manning Nick Manning - 2 months ago 12
MySQL Question

How do I use node's mysql library over ssh (on Mac)? After googling, I'm still getting "channel 3: open failed: connect failed: Connection refused"

Starting from scratch, I googled how to connect to a mysql database over ssh using node.js and the mysql library, and I came across this:

Node.js connecting through ssh

So I started a "screen" session, connected with the ssh command, and created the connection in a node script. However, I was getting an error. A comment below the accepted answer had the same issue:


I'm using a mac terminal, I typed 'screen', entered in the information you provided with my domain and password,and succesfully connected into my server via ssh. However, when I run my server.js node file the problem still persists. I'm receiving: { [Error: connect ECONNREFUSED] code: 'ECONNREFUSED', errno: 'ECONNREFUSED', syscall: 'connect', fatal: true } Is there a step here that I missed? I'm able to query successfully with this code on servers that don't require ssh.


And the response led me somewhere but did not completely explain what I need to do:


After you connected via ssh you need to connect your node.ja app to localhost. Because with that ssh command in screen you make port 3306 from mysql server available on your local machine


How exactly does one "connect your node.js app to localhost"? I saw that on the remote server side, I was getting
channel 3: open failed: connect failed: Connection refused
. So some sort of request was getting successfully sent to my remote server. However, something was failing. Googling led me to this answer:

SSH -L connection successful, but localhost port forwarding not working "channel 3: open failed: connect failed: Connection refused"


The simplest explanation for the rejection is that, on server.com, there's nothing listening for connections on localhost port 8783. In other words, the server software that you were trying to tunnel to isn't running, or else it is running but it's not listening on that port.


So now I'm stuck. How does one cause a server to "listen" so that mysql can work over ssh?

Thanks!

Answer

FWIW tunneling mysql over ssh can be accomplished in-process with the mysql2 and ssh2 modules. For example:

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

var ssh = new Client();
ssh.on('ready', function() {
  ssh.forwardOut(
    // source address, this can usually be any valid address
    '127.0.0.1',
    // source port, this can be any valid port number
    12345,
    // destination address (localhost here refers to the SSH server)
    '127.0.0.1',
    // destination port
    3306,
    function (err, stream) {
      if (err) throw err;
      var sql = mysql.createConnection({
        user: 'foo',
        database: 'test',
        stream: stream // <--- this is an important part
      });
      // use `sql` connection as usual
  });
}).connect({
  // ssh connection config ...
});

Also, since there is overhead with creating ssh connections, you might want to create an ssh connection pool for better reuse.