pitter patter on the keyboard

Once your hosting clients are all settled in you may find one day that you need to change their MySQL server address or other configuration parameters. Naturally it’s not going to look good on you or be a very good use of your time to contact every webmaster and have them update their settings. Worse, juggling two active database servers would be a nightmare. Fortunately Oracle came up with mysql-proxy, a lightweight app that sits between your clients and MySQL server(s) which acts as a drop-in replacement for mysqld. Users connect to the proxy like they would the actual server and it transports data to and fro. You can do all sorts of neat things to the data while it’s in motion with lua scripts but that goes beyond the focus of this article.

By default mysql-proxy listens on port tcp/4040 and mysqld listens on tcp/3306. In my experience most users who come from other ISPs are already wired to use localhost as their default SQL host and I don’t want to make them have to remember the port number, which is typically defaulted in most webapps. If you’re running mysqld on the same host you’re serving web from you’ll need to change the port it listens on in /etc/mysql/my.cnf.

MySQL has a contentious age-old convention of changing “localhost” to “use the local socket” rather than resolving localhost to 127.0.0.1 and connecting via TCP. That’s because at the time the decision was made local sockets were far more efficient than using TCP, now it’s not so much an issue. We need to configure mysql-proxy to listen to a socket too or our users will have to use the numeric address, lest they encounter this error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

This can be specified – not all too intuitively – by replacing the proxy-address value in /etc/mysql/mysql-proxy.cnf with the default path to the local socket, thus:

proxy-address = /var/run/mysqld/mysqld.sock

Configure the proxy-backend-address variable to reflect the actual server’s location and port number. Restart mysql-proxy and you now have a working, default-looking configuration that can be redirected anywhere. Thanks to the lua capability of the proxy you can even implement fast and easy load balancing and failover, but that will be the topic of another article!

When talking about the hosting platform I use to deliver the BKN sites it’s hard to get across the makeup of the network to someone who’s not familiar with virtual machines and vlans. This diagram illustrates how the network is logically divided and connected:

One managed switch is divided into an external vlan and an internal vlan.  This prevents traffic from the private network from crossing over onto the upstream network which is also patched into the external side of the switch. There are currently four physical servers and each one has a physical (cat6) connection both to the external vlan and the internal vlan ports on the switch. It’s safe to think of the virtual machines as tiny servers stuffed in a physical server’s package. Inside of the physical server there are virtual network connections that function like real world cables and switches.

Any number of virtual machines may route for the internal vlan and since every physical server is connected to both the external and internal sides of the switch the physical location of the router VM can quickly move to or be replaced at any other server. This opens up not only the option to live-migrate the virtual machine without dropping connections but also run a standby router (or small army) with IP failover that can automatically cut in if the active physical server fails. Not yet implemented yet at this location but I’m working on it.