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!