Contributed by: rcorder¶
- Setup two pools, one to contain the master MySQL server and a second containing all of your slave MySQL servers.
SHOW MASTER STATUS
SHOW SLAVE STATUS
as the Send String and
- 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.
echo -n '<password>' | sha1sum | cut -d' ' -f1
echo -n default | sha1sum | cut -d' ' -f1
- 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
lines once you are settled in. There are a few
log local0.warnor 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
0x01 / COM_INIT_DB
0x02 / COM_QUERY
0x0e / COM_PING
Typically upon connecting and after authentication, a client will send a
query that we save and replay the first time we switch pools. If a
SETquery 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::closeand 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.