Accessing MySQL from a Static IP
Approximate time to read: 3 min
A common use case for XL Routes Static is accessing an internal, firewalled database from a cloud application running on Heroku, Windows Azure or other PaaS provider. For security purposes many companies only allow access to their database from a known, static IP address. This allows them to prevent the rest of the internet attempting to access their database via firewall rules.
If you are running a cloud application with an unknown, constantly changing dynamic IP address you are unable to provide a known IP and so cannot access the database. XL Routes Static solves this problem by providing a load balanced proxy server that allows you to route all (or selected) traffic via a Static IP address.
This section is rated HARD on our technical knowledge level so if you have any questions leave a comment or contact our support team.
What type of proxy can I use?
XL Routes Static offers two types of proxy, an HTTP proxy for HTTP traffic and a SOCKS5 proxy for TCP traffic. As MySQL traffic does not use HTTP you have to use the SOCKS5 proxy. So how do you use it?
Option 1 – Using the ‘socksify’ wrapper.
Recommended for: Ruby on Rails, PHP & everything except Node.js The ‘socksify’ wrapper is a script that you use to invoke all your application commands (e.g. starting your web server or worker thread) which transparently routes all outbound TCP traffic through our SOCKS proxy, including all database traffic. We have a zero configuration version that allows you to get up and running in just a few minutes.
Heroku users can find specific instructions on our Static IP Dev Center page.
Installing the XL Routes Static socksify wrapper
Download and extract the wrapper in your app directory:
$ curl https://s3.amazonaws.com/xlroutes/xlroutes-socksify-latest.tar.gz | tar xz
Now you can modify your command to start your app by prepending a call to the qgsocksify script:
bin/qgsocksify rails s
You can verify that traffic is going via our proxy by making a request to http://ip.jsontest.com/ from within your app and checking the returned IP is one of your Static IP pair.
Beware a common pitfall! Control what traffic goes through the proxy
By default all traffic from your app will go through the proxy. This includes ‘beacon’ requests from services like New Relic which can quickly eat in to your monthly quota. If your database has a static IP address (very likely) then you can add a subnet mask to make sure only database traffic is routed via the proxy.
You can provide a standard subnet mask to only route traffic to certain IP subnets via the xlroutesSTATIC_MASK environment variable.
export xlroutesSTATIC_MASK="100.30.68.0/24"
All outbound traffic to 100.30.68.* would be routed via your Static IPs. Multiple masks can be provided by comma separating the mask values:
export xlroutesSTATIC_MASK="100.30.68.0/24,99.29.68.0/24"
Option 2 – Create a SOCKS connection in your application.
Recommended for: Node.js Node.js has some issues with the socksify script that can leave your app hanging so we came up with another solution, setting up the SOCKS connection directly in your Javascript.
To do this you need the socksjs module and the xlroutesSTATIC_URL environment variable set.
npm install socksjs
You can then create a SOCKS connection and pass this to the MySQL createConnection call as shown in this example. You will need to change all MySQL connection parameters to your own database for this sample to work.
var mysql = require('mysql2');
var url = require("url");
var SocksConnection = require('socksjs');
var remote_options = {
host:'mysql.db.hostname',
port: 3306
};
var proxy = url.parse(process.env.xlroutesSTATIC_URL);
var auth = proxy.auth;
var username = auth.split(":")[0]
var pass = auth.split(":")[1]
var sock_options = {
host: proxy.hostname,
port: 1080,
user: username,
pass: pass
}
var sockConn = new SocksConnection(remote_options, sock_options)
var dbConnection = mysql.createConnection({
user: 'test',
database: 'test',
password: 'testpw',
stream: sockConn
});
dbConnection.query('SELECT 1+1 as test1;', function(err, rows, fields) {
if (err) throw err;
console.log('Result: ', rows);
sockConn.dispose();
});
dbConnection.end();
If either of these solutions are what you are looking for then sign up to a free trial XL Routes Static account and try it out now.