Trouble Connecting to MySQL via SSH

问题:

I am running a node Express website on my OS X machine locally.

I need to ssh to a remote mysql database so I can start writing queries against it.

Now I'm able to ssh to our remote server (running the mysql database) in the cloud when I do it via my OS X Yosemite terminal.

But I haven't been successful trying to do it in code using the node-mysql and tunnel-ssh node middleware.

My code runs but doesn't really error out except for my GET when debugging my express app in Webstorm.

Some facts:

• I'm able to SSH into mySQL from OS X using this command:

  ssh -L 3306:127.0.0.1:3306 ourCloudServerName.net MyUserNameHere

• I'm able to then connect to mySQL using the following command:

  mysql -h localhost -p -u myUserNameHere

• When I'm at the mysql command prompt and type SHOW GLOBAL VARIABLES LIKE 'PORT', I get that the server (or database I guess that means...not sure) is running on port 3306

• I'm debugging via Webstorm 10.0.3 This means I'm debugging my Node Express app which starts like this:

  var port = 3000;

  app.set('port', port);

  app.listen(app.get('port'), function(){ console.log('Express web server is listening on port ' + app.get('port')); })

  • I can run my express app via localhost:3000 in Chrome

Now here is my first attempt to try and use both node-mysql and tunnel-ssh

mysqlConfig.js

var databaseConfig = module.exports = function(){};

 module.exports = {

    mySQLConfig: {
       host: '127.0.0.1',
       username: 'root',
       password: 'rootPassword',
       database: 'SomeDatabaseName',
       port: 3306,
       timeout: 100000
    },

    sshTunnelConfig: {
       username: 'myusername',
       password: 'mypassword',
       host: 'ourCloudServerName\.net',
       port: 22,
       //localPort: 3306, //4370
       //port: 3333,
       //localPort: 4370,
       dstPort: 3306,
       srcHost: 'localhost',
       dstHost: 'localhost',
       localHost: 'localhost'
    }
 };

connection.js

 var mysql = require('mysql'),
    config = require('./mysqlConfig'),
    tunnel = require('tunnel-ssh');


var connection = module.exports = function(){};

createDBConnection = function(){
    var mysqlConnection = mysql.createConnection({
        host: config.mySQLConfig.host,
        user: config.mySQLConfig.username,
        password: config.mySQLConfig.password,
        database: config.mySQLConfig.database,
        connectTimeout: config.mySQLConfig.timeout
    });

    return mysqlConnection;
};


connection.invokeQuery = function(sqlQuery){

    var data = undefined;

    var sshTunnel = tunnel(config.sshTunnelConfig, function(error, result) {

        var sqlConnection = createDBConnection();

        sqlConnection.connect(function (err) {
            console.log(err.code);
        });
        sqlConnection.on('error', function (err) {
            console.log(err.code);
        });

        data = sqlConnection.query({
            sql: sqlQuery,
            timeout: config.mySQLConfig.timeout
        }, function (err, rows) {

            if (err && err.code) {
                console.log("error code: " + err.code)
            }
            if (err) {
                console.error("error stack: " + err.stack)
            }
            ;

            if (rows) {
                console.log("We have Rows!!!!!!")
            }
        });

        sqlConnection.destroy();
    });

    return data;
};

router/index.js

var connection = require('../config/database/connection');
var express = require('express');
var router = express.Router();

router.get('/', function(req, res) {
    var sqlStatement = "Select top 10 from someTable";

    var rows = connection.invokeQuery(sqlStatement);
});

module.exports = router;

So I try debugging in Webstorm and I either never really get a good error printed to the console or if I do, it's a generic node error like. I could have a fundamental code problem and/or just not setting up the values right or using the middleware right, I just don't know, It's not telling me much during debug. I just know that:

1) I'm not getting a connection to the server via ssh. Connections show 0 in the ssh server object during debug

2) mysql connection object shows disconnected, never connected

3) I'm also getting this node error in Webstorm while running the express website which doesn't tell me jack:

What connection is refused, I'm running this website via localhost port 3000. Is this saying it couldn't connect to ssh? I have no idea here. This doesn't always show up or happen, it's an intermittent error and might just be a webstorm debug thing to where I just need to run debug again and usually goes away...but might be interrelated, no clue.

And here is what my config object has when I check it out during debug

And in tunnel-ssh/index.js, line 70 where it returns the server that it attempts to create, I see there is no ssh connection:

UPDATE - per suggestions from answers

回答1:

This can actually be simplified if all you need to do is tunnel MySQL connections from inside your application. The mysql2 module has (better) support for passing a custom stream to use as the database connection, this means you do not have to start a local TCP server and listen for connections to tunnel through.

Here's an example using mysql2 and ssh2:

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

var sshConf = {
  host: 'ourCloudServerName.net',
  port: 22,
  username: 'myusername',
  password: 'mypassword',
};
var sqlConf = {
  user: 'root',
  password: 'rootPassword',
  database: 'SomeDatabaseName',
  timeout: 100000
};

var ssh = new SSH2Client();
ssh.on('ready', function() {
  ssh.forwardOut(
    // source IP the connection would have came from. this can be anything since we
    // are connecting in-process
    '127.0.0.1',
    // source port. again this can be randomized and technically should be unique
    24000,
    // destination IP on the remote server
    '127.0.0.1',
    // destination port at the destination IP
    3306,
    function(err, stream) {
      // you will probably want to handle this better,
      // in case the tunnel couldn't be created due to server restrictions
      if (err) throw err;

      // if you use `sqlConf` elsewhere, be aware that the following will
      // mutate that object by adding the stream object for simplification purposes
      sqlConf.stream = stream;
      var db = mysql2.createConnection(sqlConf);

      // now use `db` to make your queries
    }
  );
});
ssh.connect(sshConf);

You will want to expand upon this example of course, to add error handling at the ssh and mysql level in case either go away for some reason (TCP connection gets severed or the ssh/mysql services get stopped for example). Typically you can just add error event handlers for ssh and db to handle most cases, although you may want to listen for end events too to know when you need to re-establish either/both the ssh and db connections.

Additionally it may be wise to configure keepalive at both the ssh and mysql level. ssh2 has a couple of keepalive options that behave just like the OpenSSH client's keepalive options. For mysql2, typically what I've done is just call db.ping() on some interval. You can pass in a callback that will get called when the server responds to the ping, so you could use an additional timer that gets cleared when the callback executes. That way if the callback doesn't execute, you could try to reconnect.

回答2:

This is SELinux problem and your httpd / webserver is not allowed to connect over network, answer to your problem is following command:

setsebool -P httpd_can_network_connect 1

It should work fine then. I believe you are using apache?