Mysql: How to configure node-mysql to connect via a proxy address?

[pierrickchabi]

I checked mysql-node connection options but I can't figure out which one will allow me to connect to a remote legacy MySQL database via a proxy?

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret',
  ...
});

I'm missing an option like: proxy : randomIPtoStaticIP.pro.xy

Any pointers would be greatly appreciated.

[sidorares]

What kind of proxy you have? SOCKS5?

I don't think driver itself should handle network proxies. There are two possible options:

• configure local server ( or tun interface ) to connect to a proxy. Connect driver to localhost (or tun ip address)
• or we could add stream as mysql connection option to allow driver to communicate over any duplex stream. Create stream connected to remote proxy using one of node.js client libraries, for example https://github.com/mattcg/socks5-client . This option is already possible with node-mysql2, see examples / connecting using custom stream

[pierrickchabi]

Thank you Andrey, my proxy is a simple url provided by Proximo (or
QuotaGuard Static) it's Heroku add-on I need to use so my requests appear
as coming from a static IP.

Do your hints still make sense for such a proxy?

[sidorares]

@pierrickchabi still not very clear how do you use proxy and why you need it. Are both app and mysql server on heroku? Why can't you just connect to mysql using hostname?

[pierrickchabi]

@sidorares The app is on Heroku. The mysql server is not on Heroku. I definitely can connect to mysql using hostnames. But mysql server needs to know my IP to accept my requests. Unfortunately due to Heroku re-routing strategies, my requests never come from a single static IP but from unpredictable IPs. That's why I'm using a proxy that makes my request appear as coming from a signle known IP. Is it more clear?

Proxy address providers I'm trying to use are Proximo and QuotaGuard Static.

[pierrickchabi]

In other words, how do I transpose such an _proxied GET request_:

var http, options, proxy, url;
http = require("http");
url = require("url");
proxy = url.parse(process.env.QUOTAGUARDSTATIC_URL); // <-- proxy url
target  = url.parse("http://any-database-as-an-api.com/");

options = {
  hostname: proxy.hostname,
  port: proxy.port || 80,
  path: target.href,
  headers: {
    "Proxy-Authorization": "Basic " + (new Buffer(proxy.auth).toString("base64")),
    "Host" : target.hostname
  }
};

http.get(options, function(res) { 
  res.pipe(process.stdout);
  return console.log("status code", res.statusCode);  
});

... into a _proxied mysql conection_ like this ?

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'my-database-host.com',
  user     : 'bob',
  password : 'secret',
  stream : // use proxy host and port here

});

[sidorares]

I'm not sure if that is possible at all, depends on which level Proximo/QuotaGuard operate - if it's application level ( http ) then you can only use them for http connections

[sidorares]

QuotaGuard says " If you need a SOCKS service then we can provide this on request." - so should be doable using socks client and connecting to it locally

[pierrickchabi]

That's an invaluable hint @sidorares. I didn't re-read the docs after you spoke about SOCKS. Actually I'm not familiar with sockets.

I've just requested a SOCKS service to QuotaGuard Static (via Heroku). Hopefully I will get it on Monday.

Should I expect them to provide something like a _quotaguardstatic.sock_ file, right ? And maybe info about maximum stream rate ? so I can use the node-mysql2 stream feature in this manner:

var net        = require('net');
var mysql      = require('mysql2');
var shape      = require('shaper');
var connection = mysql.createConnection({
   user: 'test',
   database: 'test',
   stream: net.connect('/tmp/quotaguardstatic.sock').pipe(shape(10)) // emulate 10 bytes/sec link
});
connection.query('SELECT 1+1 as test1', console.log);

[sidorares]

socks refers to http://en.wikipedia.org/wiki/SOCKS and you are talking about http://en.wikipedia.org/wiki/Unix_domain_socket

Using https://github.com/mattcg/socks5-client client the code should look like

var socks = require("socks5-client");
var socksConn = socks.createConnection({ socksHost: randomIPtoStaticIP.pro.xy, socksPort: 12345 });
var mysql      = require('mysql2');
var dbConnection = mysql.createConnection({
   user: 'test',
   database: 'test',
   stream: socksConn
});

[pierrickchabi]

Thanks for disambiguing me. And thanks for the sample code.
I will update this topic with my final result (hopefully a success) on Monday.

[pierrickchabi]

Good news! I've finaly managed to configure my Node.js app, hosted on an _random-IP_ heroku app, to access a remote _firewalled_ MySQL database, thanks to a SOCKS proxy provided by QuotaGuard Static that forwards my requests from a _static IP_, thus _whitelistable_.

Thank you for your great help @sidorares. Here is the sample code that achieves this connection. As recommanded by QuotaGuard support team, I used socksjs instead of socks5-client because the latter doesn't support authenticated SOCKS proxies.

var mysql2 = require('mysql2');
var url = require("url");
var SocksConnection = require('socksjs');

var mysql_server_options = {
  host: 'database.ho.st',
  port: 3306
};

var socks_options = {
  host: 'proxy.ho.st',
  port: 1080,
  user: 'proxy_username',
  pass: 'proxy_password'
};

var socksConn = new SocksConnection(mysql_server_options, socks_options);

var mysql_options =  {
  database: 'db_name',
  user: 'db_username',
  password: 'secret',
  stream: socksConn
}

var mysqlConn = mysql2.createConnection(mysql_options);

mysqlConn.query('SELECT 1+1 as test1;', function(err, rows, fields) {
  if (err) throw err;

  console.log('Result: ', rows);

  socksConn.dispose();
  mysqlConn.end();
});

To go further: I couldn't manage to set pooled connections using node-mysql2 + socksjs, I'm getting an error...

[sidorares]

@pierrickchabi if you have mysql2-related issues could you post them to mysql2 github?

@felixge what do you think about adding stream client option to allow to connect over any duplex stream?

[dougwilson]

I am :+1: on adding a stream option, as it seems extremely useful for lots of scenarios (especially in testing).

[mm-wang]

Was stream ever added to mysql? And is it possible to use pooled connections on mysql with a stream?