Connecting to mysql using ssh tunnel does not work

Question

I am trying to connect to a remote server via ssh tunnel and then connect to a mysql.

I first tried on mysql workbench and it works fine. The "fake" information looks like this: With the same information I tried to connect to mysql with nodeJS but I have no idea how to do it. Please help. This is what I have tried so far.

var mysql = require('mysql');
var tunnel = require('tunnel-ssh');

var config = {
  username:'hoffnung8493',
  Password:'password123',
  host:'hoffnung.com',// not sure if its right
  port:22,// not sure if its right
  dstHost:'hoffnung.com',// not sure if its right
  dstPort:3306, // not sure if its right
  localHost:'127.0.0.1',// not sure if its right
  localPort: 27000 // not sure if its right
};

tunnel(config, function(error, server){
  var db = mysql.createConnection({
    host: '127.0.0.1',// not sure if its right
    user: 'stock_id',
    password: 'stock_password',
    database: 'database_name',
    charset  : 'utf8'
  });
db.connect();
db.query(`show tables`, function(error, result){
  console.log(error);
  console.log(1, result);
  })
})

module.exports = db; //probably wrong

Then from main.js I want to send query like this:

db = require('./lib/db.js');

db.query('show tables', function(error, result){
    console.log(result)
});

On my console I get the following error message:

0|main   | Error: All configured authentication methods failed
0|main   |     at tryNextAuth (/home/cabox/workspace/node_modules/tunnel-ssh/node_modules/ssh2/lib/client.js:376:17)
0|main   |     at SSH2Stream.onUSERAUTH_FAILURE (/home/cabox/workspace/node_modules/tunnel-ssh/node_modules/ssh2/lib/client.js:587:5)
0|main   |     at emitTwo (events.js:125:13)
0|main   |     at SSH2Stream.emit (events.js:213:7)
0|main   |     at parsePacket (/home/cabox/workspace/node_modules/ssh2-streams/lib/ssh.js:3929:10)
0|main   |     at SSH2Stream._transform (/home/cabox/workspace/node_modules/ssh2-streams/lib/ssh.js:669:13)
0|main   |     at SSH2Stream.Transform._read (_stream_transform.js:186:10)
0|main   |     at SSH2Stream._read (/home/cabox/workspace/node_modules/ssh2-streams/lib/ssh.js:251:15)
0|main   |     at SSH2Stream.Transform._write (_stream_transform.js:174:12)
0|main   |     at doWrite (_stream_writable.js:385:12)

After I fixed from "Password" ->"password", I added db.query after db.connect to see if it works and I got the following error

0|main   | { Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'stock'@'firstpg-server197-22' (using password: YES)
0|main   |     at Handshake.Sequence._packetToError (/home/cabox/workspace/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
0|main   |     at Handshake.ErrorPacket (/home/cabox/workspace/node_modules/mysql/lib/protocol/sequences/Handshake.js:124:18)
0|main   |     at Protocol._parsePacket (/home/cabox/workspace/node_modules/mysql/lib/protocol/Protocol.js:278:23)
0|main   |     at Parser.write (/home/cabox/workspace/node_modules/mysql/lib/protocol/Parser.js:76:12)
0|main   |     at Protocol.write (/home/cabox/workspace/node_modules/mysql/lib/protocol/Protocol.js:38:16)
0|main   |     at Socket.<anonymous> (/home/cabox/workspace/node_modules/mysql/lib/Connection.js:91:28)
0|main   |     at Socket.<anonymous> (/home/cabox/workspace/node_modules/mysql/lib/Connection.js:502:10)
0|main   |     at emitOne (events.js:115:13)
0|main   |     at Socket.emit (events.js:210:7)
0|main   |     at addChunk (_stream_readable.js:266:12)
0|main   |     --------------------
0|main   |     at Protocol._enqueue (/home/cabox/workspace/node_modules/mysql/lib/protocol/Protocol.js:144:48)
0|main   |     at Protocol.handshake (/home/cabox/workspace/node_modules/mysql/lib/protocol/Protocol.js:51:23)
0|main   |     at Connection.connect (/home/cabox/workspace/node_modules/mysql/lib/Connection.js:118:18)
0|main   |     at /home/cabox/workspace/inven_man/lib/db2.js:24:4
0|main   |     at Server.<anonymous> (/home/cabox/workspace/node_modules/tunnel-ssh/index.js:89:13)
0|main   |     at Object.onceWrapper (events.js:314:30)
0|main   |     at emitNone (events.js:105:13)
0|main   |     at Server.emit (events.js:207:7)
0|main   |     at emitListeningNT (net.js:1349:10)
0|main   |     at _combinedTickCallback (internal/process/next_tick.js:135:11)
0|main   |   code: 'ER_ACCESS_DENIED_ERROR',
0|main   |   errno: 1045,
0|main   |   sqlMessage: 'Access denied for user \'stock\'@\'firstpg-server197-22\' (using password: YES)',
0|main   |   sqlState: '28000',
0|main   |   fatal: true }
0|main   | 1 undefined

Answer 1

I cant test it right now, but it should be sufficient to set port in the options to 27000.

tunnel(config, function(error, server){
  var db = mysql.createConnection({
    host: '127.0.0.1',
    port: 27000, // the local port you defined for your tunnel
    user: 'stock_id',
    password: 'stock_password',
    database: 'database_name',
    charset  : 'utf8'
  });
  db.connect();
})

Beside that the options are case sensitive, so it should be password and not Password.

var config = {
  username:'hoffnung8493',
  password:'password123',
  host:'hoffnung.com',// not sure if its right
  port:22,// not sure if its right
  dstHost:'hoffnung.com',// not sure if its right
  dstPort:3306, // not sure if its right
  localHost:'127.0.0.1',// not sure if its right
  localPort: 27000 // not sure if its right
};

Answer 2

First of all, I'd like to suggest you to change / hide the domain name in the future, just for caution cases.
Secondly, can you post the output you get from the console? If you get any output.
Thirdly, as this is an ssh connection matter, you need to make sure you are set as allowed to connect to the remote server, on port 22 in this case, whether in it's settings / firewall settings.

Regarding the settings, double check the variable values to be sure not to confuse between the MySQL and SSH connections variables, rely on the fact that the MySQL Workbench connection is working.