MySQL Proxy¶

Contributed by: rcorder¶

This iRule requires BIG-IP 11.1.0 or later.
It is likely the proxy will not work unless you are running BIG-IP
11.1 and MySQL 5.1, which were the versions I tested against. The
MySQL protocol has a habit of changing.
To request support for other LTM and MySQL versions, please email
solutionsfeedback@f5.com.

Description¶

iRule to implement Layer 7 full proxy for MySQL servers. This proxy alleviates the need for a developer to modify his/her application to use multiple database handles when scaling from a standalone MySQL server to a Master/Slave cluster. Read-only queries are sent to the slave pool while all others are sent to the master pool.

This proxy is also available as an iApp.

Implementation Details¶

Setup two pools, one to contain the master MySQL server and a second containing all of your slave MySQL servers.

** MODIFY the arrays ‘mysql_pools’ and ‘mysql_pools_reverse’
below accordingly
** CREATE a separate monitor for each pool.  A good starting
point could be:
*** Master) New monitor of type

mysql

, use

SHOW MASTER STATUS

as the Send String and have an empty Receive String in Row 1 Column 3.

*** Slave) New monitor of type

mysql

, use

SHOW SLAVE STATUS

as the Send String and

as the Receive String in Row 1 Column 19.

Create a custom Data Group, of type String, that contains all the users (and their passwords) that you expect to use this proxy. The passwords must be hashed with SHA1.

** GENERATE the appropriate hash(es) by using the following one-liner on the BIG-IP command line or any standard Linux host:

echo -n '<password>' | sha1sum | cut -d' ' -f1

*** For example:

echo -n default | sha1sum | cut -d' ' -f1

*** Would result in the following:

7505d64a54e061b7acd54ccd58b49dc43500b635

*** And in your data group, you would use this value as the
Value and the String would be your username.
** MODIFY the ‘users_data_group’ variable below to reference the
name of the Data Group that you just created.

Create a Standard Virtual Server, listening on port 3306. Turn on SNAT Automap, specify this iRule in the ‘Resources’ tab, and use the slave pool you created earlier as the Default Pool. Use universal as your Default persistence profile and dest_addr as the Fallback.

There is a LOT of debug logging in this iRule. You may want to comment out most of the
```
log local0.debug
```
lines once you are settled in. There are a few
```
log local0.warn
```
or higher messages that I recommend you leave in tact. All messages should show up in /var/log/ltm.

Default Persistence behavior is per-authenticated session from the client. This doesn’t do anything towards connection pooling, but is the most “secure”. If you want to change this behavior, modify ALL of the instances of
```
persist uie ...
```
(clientside and serverside, respectively) to use something else as the unique label to persist connections on.

Please let us know what you tested/changed and what did or did not work; there should be a thread in the Open Source forum on F5 DevCentral to talk about it.

Currently only supports the following Command Packets:

0x00 / COM_QUIT

(mysql_close)

0x01 / COM_INIT_DB

(mysql_select_db)

0x02 / COM_QUERY

(mysql_real_query)

0x0e / COM_PING

(mysql_ping)

** All other Command Packets should technically work, but have not been tested and will log a message to syslog with the level of WARN.

Typically upon connecting and after authentication, a client will send a
```
SET
```
query that we save and replay the first time we switch pools. If a
```
SET
```
query is sent later, it will only get applied to the node of the pool we are currently connected to. We need to set a flag so that it gets replayed if/when we ever do switch back.

There is an authenticated or not flag that we have set. If the client and/or the server disagrees with us we don’t recover properly; instead we
```
TCP::close
```
and kill everything.

SSL connections.
Compression/compressed data stream.
MySQL’s version of persistent connections. Not that it can’t or wouldn’t work, I just haven’t had a chance to test them yet. Besides, MySQL themselvess recommend against using them.
Transactions – I’ll have a fix for this in the next version. Essentially, we need to stick to a specific server to the duration of the transaction, regardless of what class (read vs. write) of query is sent.

The BIG-IP API Reference documentation contains community-contributed content. F5 does not monitor or control community code contributions. We make no guarantees or warranties regarding the available code, and it may contain errors, defects, bugs, inaccuracies, or security vulnerabilities. Your access to and use of any code available in the BIG-IP API reference guides is solely at your own risk.