无法通过 SSH 连接到 MySQL [英] Trouble Connecting to MySQL via SSH
问题描述
我在本地的 OS X 机器上运行 node Express 网站.
我需要通过 ssh 连接到远程 mysql 数据库，以便我可以开始针对它编写查询.
现在，当我通过 OS X Yosemite 终端执行此操作时，我可以通过 ssh 连接到云中的远程服务器(运行 mysql 数据库).
但我没有成功尝试使用 node-mysql 和 tunnel-ssh 节点中间件在代码中做到这一点.
在 Webstorm 中调试我的 express 应用程序时，我的代码运行但并没有真正出错，除了我的 GET.
一些事实:
我可以使用以下命令从 OS X SSH 到 mySQL:
ssh -L 3306:127.0.0.1:3306 ourCloudServerName.net MyUserNameHere
然后我可以使用以下命令连接到 mySQL:
mysql -h localhost -p -u myUserNameHere
当我在 mysql 命令提示符下输入 SHOW GLOBAL VARIABLES LIKE 'PORT' 时，我知道服务器(或我猜这意味着……不确定)正在端口 3306 上运行
我正在通过 Webstorm 10.0.3 进行调试这意味着我正在调试我的 Node Express 应用程序，它以这样的方式启动:
var 端口 = 3000;
app.set('port', port);
app.listen(app.get('port'), function(){console.log('Express web 服务器正在监听端口 ' + app.get('port'));})
- 我可以在 Chrome 中通过 localhost:3000 运行我的 express 应用
现在这是我第一次尝试同时使用 node-mysql 和 tunnel-ssh
mysqlConfig.js
var databaseConfig = module.exports = function(){};模块.出口 = {mySQLConfig:{主机:'127.0.0.1'，用户名:'root'，密码:'root密码'，数据库: 'SomeDatabaseName',端口:3306，超时:100000},sshTunnelConfig:{用户名:'我的用户名'，密码:'我的密码'，主机:'ourCloudServerName\.net',端口:22，//本地端口:3306,//4370//端口:3333，//本地端口:4370，dstPort: 3306,srcHost: '本地主机',dstHost: '本地主机',本地主机:'本地主机'}};connection.js
var mysql = require('mysql'),config = require('./mysqlConfig'),隧道 = 要求('隧道-ssh');var connection = module.exports = function(){};createDBConnection = 函数(){var mysqlConnection = mysql.createConnection({主机:config.mySQLConfig.host，用户:config.mySQLConfig.username,密码:config.mySQLConfig.password，数据库:config.mySQLConfig.database，连接超时:config.mySQLConfig.timeout});返回 mysqlConnection;};connection.invokeQuery = 函数(sqlQuery){var 数据 = 未定义；var sshTunnel = 隧道(config.sshTunnelConfig，函数(错误，结果){var sqlConnection = createDBConnection();sqlConnection.connect(函数(错误){控制台日志(错误代码)；});sqlConnection.on('错误'，函数(错误){控制台日志(错误代码)；});数据 = sqlConnection.query({sql: sqlQuery,超时:config.mySQLConfig.timeout}，函数(错误，行){如果(错误&& err.code){console.log("错误代码:" + err.code)}如果(错误){console.error("错误堆栈:" + err.stack)};如果(行){console.log("我们有行！！！！！！")}});sqlConnection.destroy();});返回数据；};路由器/index.js
var connection = require('../config/database/connection');var express = require('express');var router = express.Router();router.get('/', function(req, res) {var sqlStatement = "从 someTable 中选择前 10 个";var rows = connection.invokeQuery(sqlStatement);});module.exports = 路由器；所以我尝试在 Webstorm 中进行调试，但我从来没有真正在控制台上打印出一个好的错误，或者如果我这样做了，它是一个通用的节点错误，例如.我可能有一个基本的代码问题和/或只是没有正确设置值或正确使用中间件，我只是不知道，在调试过程中并没有告诉我太多.我只知道:
1) 我没有通过 ssh 连接到服务器.调试期间连接在 ssh 服务器对象中显示为 0
2) mysql 连接对象显示断开连接，从未连接过
3) 在运行没有告诉我 jack 的 express 网站时，我也在 Webstorm 中收到此节点错误:
什么连接被拒绝，我通过本地主机端口 3000 运行这个网站.这是说它无法连接到 ssh 吗?我不知道这里.这并不总是出现或发生，这是一个间歇性错误，可能只是一个 webstorm 调试问题，我只需要再次运行调试并且通常会消失......但可能是相互关联的，没有线索.
这是我在调试期间检查配置对象时的内容
在隧道 ssh/index.js 的第 70 行，它返回它尝试创建的服务器，我看到没有 ssh 连接:
更新 - 根据答案中的建议
如果您需要做的只是从应用程序内部建立 MySQL 连接隧道，这实际上可以简化.mysql2 模块(更好)支持传递自定义流以用作数据库连接，这意味着您不必启动本地 TCP 服务器并侦听连接以通过隧道.
var mysql2 = require('mysql2');var SSH2Client = require('ssh2').Client;var sshConf = {主机:'ourCloudServerName.net',端口:22，用户名:'我的用户名'，密码:'我的密码'，};var sqlConf = {用户:'root'，密码:'root密码'，数据库: 'SomeDatabaseName',超时:100000};var ssh = 新的 SSH2Client();ssh.on('准备好', function() {ssh.forwardOut(//连接将来自的源 IP.这可以是任何东西，因为我们//正在连接进程中'127.0.0.1',//源端口.同样，这可以是随机的，技术上应该是独一无二的24000,//远程服务器上的目标IP'127.0.0.1',//目的IP的目的端口3306,功能(错误，流){//你可能想要更好地处理这个问题，//如果由于服务器限制而无法创建隧道如果(错误)抛出错误；//如果你在其他地方使用 `sqlConf`，请注意以下内容//为了简化目的，通过添加流对象来改变该对象sqlConf.stream = 流；var db = mysql2.createConnection(sqlConf);//现在使用 `db` 进行查询});});ssh.connect(sshConf);当然，您将希望扩展此示例，以在 ssh 和 mysql 级别添加错误处理，以防因某种原因而消失(例如，TCP 连接中断或 ssh/mysql 服务停止).通常，您只需为 ssh 和 db 添加 error 事件处理程序即可处理大多数情况，尽管您可能希望监听 end 事件也可以知道何时需要重新建立 ssh 和 db 连接.
此外，在 ssh 和 mysql 级别配置 keepalive 可能是明智的.ssh2 有几个 keepalive 选项，它们的行为就像OpenSSH 客户端的 keepalive 选项.对于mysql2，通常我所做的只是每隔一段时间调用db.ping().您可以传入一个回调，该回调将在服务器响应 ping 时被调用，因此您可以使用一个额外的计时器，该计时器在回调执行时被清除.这样，如果回调没有执行，您可以尝试重新连接.
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
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.
这篇关于无法通过 SSH 连接到 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
