Faking Production – database access

One of our services has been around for a while, a realy long time.  It used to get developed in production, there is an awful lot of work involved in making the app self-contained, to where it could be brought up in a VM and run without access to production or some kinds of fake supporting environment.  There’s lots of stuff hard coded in the app (like database server names/ip etc), and indeed, and there’s a lot of code designed to handle inaccessible database servers in some kind of graceful manor.

We’ve been taking bite sized chunks of all of this over the last few years, we’re on the home straight.

One of the handy tricks we used to get this application to be better self-contained was avoid changing all of the database access layer (hint, there isn’t one) and just use iptables to redirect requests to production database servers to either local empty database schema on the VM, or shared database servers with realistic amounts of data.

We manage our database pools (master-dbs.example.com, slave-dbs.example.com, other-dataset.example.com etc) using DNS (PowerDNS with MySQL back end), in production, if you make a DNS request for master-dbs.example.com, you will get 3+ IPs back, one of which will be in your datacentre, the others will be other datacentres, the app has logic for selecting the local DB first, and using an offsite DB if there is some kind of connection issue.  We also mark databases as offline by prepending the relevant record in MySQL with OUTOF, so that a request for master-dbs.example.com will return only 2 IPs, and a DNS request for OUTOFmaster-dbs.example.com will return any DB servers marked out of service.

Why am I telling you all of this?  Well, it’s just not very straight forward for us to update a single config file and have the entire app start using a different database server. Fear not, our production databases aren’t actually accessible from the dev environments.

But what we can do is easily identify the IP:PORT combinations that an application server will try and connect to.  And once we know that it’s pretty trivial to generate a set of iptables statements that will quietly divert that traffic elsewhere.

Here’s a little ruby that generates some iptables statements to divert access to remote, production, databases to local ports, where you can either use ssh port-forwarding to forward on to a shared set of development databases, or to several local empty-schema MySQL instances:

require “rubygems”
require ‘socket’

# map FQDNs to local ports
fqdn_port = Hash.new
fqdn_port[“master-dbs.example.com”] = 3311
fqdn_port[“slave-dbs.example.com”] = 3312
fqdn_port[“other-dataset.example.com”] = 3314

fqdn_port.each do |fqdn, port|
puts “#”
puts “# #{fqdn}”
# addressess for this FQDN
fqdn_addr = Array.new

# get the addresses for the FQDN
addr = TCPSocket.gethostbyname(fqdn)
addr[3, addr.length].each { |ip| fqdn_addr << ip }

addr = TCPSocket.gethostbyname(‘OUTOF’ + fqdn)
addr[3, addr.length].each { |ip| fqdn_addr << ip }

fqdn_addr.each do |ip|
puts “iptables -t nat -A OUTPUT -p tcp -d #{ip} –dport 3306 -j DNAT –to{fqdn_port[fqdn]}”

And yes, this only generates the statements, just pipe the output into bash if you want the commands actually run.  Want to see what it’s going to do?  Just run it.  Simples.

Joel does it again

Joel Spolsky has an excellent set of brief articles on management styles in IT (focused on software developement, but I believe the comments apply across the board for creative technology[1] roles)

They are as follows:

Worth a read if your management or managed. I work with several ex-military people, where we have a mix of C&C and measurement…I’m still mulling over what that means 🙂

[1] Technology workers split into 2 camps, those of us who design, implement & maintain any kind of IT system and the end users. I’m focusing on the former, but end users shouldn’t be devoid of creative thought, they are the actual users of systems we create.

Change of scene..

On the 15th July I jumped on a place (well, two planes, and jumped is hardly right, as easyJet stung me an extra £40 for being over weight[1]) and headed to Durham, North Carolina, for a couple of months, work offered me an exciting opertunity, and after some discussions with my wife, we took it, she joined me here a week later. Her work has been good enough to let her take some extended leave so she can spend the time with me out here, she joind me out here a week later.
We’ve been living in a hotel since we got here, but on Wednesday we move into an apartment in Chapel Hill, NC, about 15 minutes from the office & the hotel we are in now.

So far everything has gone very smoothly, no major hassles or problems.

Before I left, we had a big family & friends BBQ at our house, and although the weather did it’s best to put a dampner on the whole thing (I ended up BBQing in the garage door, helped by my Dad and my brother in-law, the garage now stinks of food..) it was great to see everybody and I was touched to hear everybodies good wishes. Thank you to everybody who turned up and even to those who couldn’t 🙂

We’ve been out and about trying to explore our new surroundings, doing our best not to die in the heat.

So far we’ve eaten are way round most of the local places (living in a hotel does have draw back, constantly eating out becomes a chore and is doing my waistline no favours at all), shopped at the local mega mall and smaller strip malls, been to Jordan Lake, the Museum of Life & Sciences.
[1] my luggage, not me, although they would have been justified in that too.