Node.js connecting through ssh

Question

I have a node.js server that works but needs to be set up for ssh connections:

var mysql = require('mysql')
var io = require('socket.io').listen(3000)
var db = mysql.createConnection({

    host: 'hostname',
    user: 'username',
    password: '12345',
    database: '12345',
    port: 3306,
    socket: '/var/run/mysqld/mysqld.sock' 
})

db.connect(function(err){
    if (err) console.log(err)
})

I'm aware that there are ssh npm libraries for this purpose, however the options available (ssh2, node-sshclient, etc) appear to deal with pretty intricate features that may overcomplicate things. I'm looking for the simplest way to connect to my mysql db through ssh. What would be the best way to accomplish this?

Solution

If you are running a linux/unix system do the following:

Connect to your mysql server via ssh and proxy the mysql port (default is 3306) via this ssh tunnel.

This works as follows:

1 Type in screen (to start a screen session which is permanent even if the shell gets closed).

2 Type into screen shell:

ssh -L 3306:127.0.0.1:3306 your_servers_domain_or_ip -lyour_login_name

3 Enter your ssh password / or use a PKI auth to avoid manual steps

4 Done... now it’s possible to connect MySQL like you would do when it’s installed on the same machine as your application.

Connect to MySQL from node.js like below:

var db = mysql.createConnection({
    host: '127.0.0.1', // Important to connect to localhost after connecting via ssh in screen
    user: 'username',
    password: '12345',
    database: '12345',
    port: 3306
});

Comments

[Troy Griffiths]

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.

[Bernhard]

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

[aidonsnous]

@Bernhard what for windows? I am using putty to connect to my server and have a node.js app locally that I would like to connect to the database using ssh.

[Bernhard]

@aidonsnous If you're using cygwin.com i guess it will work. But then maybe try to run your node app via cygwin too. Many of my colleagues use this on windows to have a linux like environment. Hope it helps.

[The pyramid]

thanks for the info , but when i type the command in the terminal , of course after changing ip and user , i log in my vps , and it's working but when i type exit the connection are terminated . so how to keep connection running without loging into my vps

[Bernhard]

@The4thpyramid If you're using sceen command beforehand the session persists even when you close the terminal. Alternatively when you're target server supports PKI authentication you could setup connection on system start see e.g. here askubuntu.com/questions/884138/…