A read/write MySQL proxy for BIG-IP

Greetings everyone!

It has been a very long time since I have blogged here, but this post should be worth the wait.

It is with great excitement that I announce the availability of the MySQL Proxy iRule.  This is a project I have been working on and off on for quite a while.  I would have loved to given this solution to the MySQL and iRule communities a long time ago, but the nature of the MySQL Protocol threw me a curveball.

MySQL Protocol Funny-business

Skip this part if you don't want to know the nitty-gritty protocol details.

MySQL is a banner-based protocol; meaning that when a client connects, the server talks first.  Unlike some other banner-based protocols (like SMTP), the server does not tolerate any chatter from the client prior to it delivering it's WELCOME message.  As a result, if you want to proxy the protocol, you also have to be a silent client during that initial connect.  The nature of BIG-IP does not allow this.  At it's most basic level, BIG-IP is still a full Layer 7 proxy and it's job is to do just that: take a connection, flow, stream, whatever; make an intelligent decision as to where it should go, optionally make some changes, and send it on it's way; not connect to a backend server without sending a payload.

Seeing the need to support more and varied protocols going forward, including banner-based ones, a new iRule command was added in version 11.1.  This command, LB::connect, instructs the server-side of the proxy to establish a connection (provided a load-balancing decision has been made), but to wait until we are ready to send the payload.  As a result, I was able to use this functionality to work with MySQL's banner-based protocol.

What does the MySQL Proxy iRule do?

The MySQL Proxy iRule implements a full Layer 7 MySQL proxy.  In short, using BIG-IP's dual TCP/IP stack, it pretends to be a MySQL server to the client on the frontend and pretends to be a MySQL client to the MySQL server on the backend.

If a user sends a read-only query (such as SELECT * from data), the proxy will direct that query to your pool of MySQL worker servers.  However, if the user sends a write query (such as one that uses INSERT, UPDATE, or DELETE), the proxy will direct that query to your MySQL controlling server.  It will also keep subsequent read-only queries directed to the controlling server for a limited time to allow for replication to your MySQL worker servers to complete.

Where to get the MySQL Proxy iRule / iApp?

The MySQL Proxy is available both as a stand-alone iRule and as an iApp. The iApp version allows simple creation and management of your MySQL Proxy, while the iRule allows you make changes as you see fit.  Note that the iRule version requires quite a bit of manual configuration.

Both the iRule and the iApp require BIG-IP version 11.1.0 or later.

• MySQL Proxy iRule
• MySQL Proxy iApp