Problem connecting to MySQL via SSH
I am running a node Express website on my OS X machine locally.
I need ssh to a remote mysql database so I can start writing queries against it.
I can now ssh to our remote server (by starting the mysql database) in the cloud when I do this via OS X Yosemite terminal.
But I was unable to do it in code using node-mysql middleware and tunnel-ssh node.
My code works but doesn't actually work error other than my GET when debugging my express app in Webstorm.
Some facts :
-
I can SSH into mySQL from OS X with this command:
ssh -L 3306: 127.0.0.1: 3306 ourCloudServerName.net MyUserNameHere
-
I can connect to mySQL using the following command:
mysql -h localhost -p -u myUserNameHere
-
When I am on the mysql command line and press SHOW GLOBAL VARIABLES LIKE 'PORT' I get that the server (or database, I guess that means ... not sure) is running on port 3306
-
I am debugging via Webstorm 10.0.3 This means I am debugging a node Express application 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 at trying to 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 am trying to debug in Webstorm and I never get a good error printed to the console, or if I do this is a typical node error. I might have a fundamental problem with the code and / or just not setting the correct values or using the right middleware, I just don't know, that doesn't tell me a lot while debugging. I just know that:
1) I am not getting a connection to the server via ssh. Connections show 0 in ssh server object while debugging
2) mysql connection object shows disconnected, never connected
3) I also get this node error in Webstorm while the express site is running, which doesn't tell me:
Some connection disconnected, I am running this site through localhost port 3000. Is it an expression that it cannot connect to ssh? I have no idea. It doesn't always show up or happen, it is an intermittent error and might just be a webbuffer debug thing where I just need to start debugging again and usually goes away ... but might be related, no clue.
And this is what my config object has when I check it while debugging
And in tunnel-ssh / index.js, line 70, where it returns the server that it is trying to create, I see that there is no ssh connection:
UPDATE - for replies from answers
source to share
This can be simplified if all you have to do is tunnel MySQL connections inside your application. The module mysql2
has (better) support for passing a user stream to use as a database connection, this means you don't have to start a local TCP server and listen for tunnel connections through.
Here is a usage example 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);
Of course, you will want to extend this example to add error handling at the ssh and mysql level in case they leave for any reason (the TCP connection is disconnected or, for example, the ssh / mysql services are stopped). Typically, you can simply add event handlers error
for ssh
and db
to handle most cases, although you can also listen for events end
to see when you need to recover // ssh
and db
.
Also, it might be wise to set up keepalive on both ssh and mysql level. ssh2
has a couple of keepalive parameters that behave the same as the keepalive parameters of the OpenSSH client. For mysql2
, generally, what I have done is just calls db.ping()
at some interval. You can pass a callback that gets called when the server responds to the ping, so you can use an additional timer that is cleared when the callback is executed. This way, if the callback fails, you can try to reconnect.
source to share