Forum Discussion
myocella
Nimbostratus
NimbostratusiRule to inspect MySQL statements
Greeting,
I would like to create iRule to apply a VS to redirect MySQL traffic
to a pool based on the SQL statement. Here is my iRule:
---------------8<------------------------------
when CLIENT_ACCEPTED {
if { ( [IP::addr [IP::client_addr] equals a.a.b.b] ) or ( [IP::addr [IP::client_addr] equals c.c.d.d] ) } {
log local0.none "true"
snat xx.xx.xx.xx
log local0.none "SNAT completed"
TCP::collect
log local0.none "Data collected"
}
}
when CLIENT_DATA {
log local0.none "CLIENT_DATA"
if { [TCP::payload] contains "select" } {
pool mysql_read_pool
} else {
pool mysql_write_pool
}
TCP::release
}
-------------->8-----------------------------------
The CLIENT_ACCEPTED works as it is instructed. However, I couldn't
see the source IP was SNAT(ed) to xx.xx.xx.xx, and CLIENT_DATA
wasn't executed at all.
This VS is for accepting MySQL connections from 2 web servers (a.a.b.b and
c.c.d.d).
Can anyone shed some light on this?
Thanks
myOcella
myocellaI've tried to put any number in TCP::collect but it still doesn't work. However when I removed TCP::collect, SNAT works fine.
hoolioCirrostratus
If you capture a tcpdump, do you see the client sending any data? I'm not sure about mysql, but maybe the client establishes the connection and then waits for the server to send data first? That's just a stab in the dark though. If this is the case, take a look at the TCP::collect wiki page's section on skip bytes (TCP::collect Click here).
Aaron- ryanc_76025
Altocumulus
This, unfortunately, isn't going to work so easily...I really wish it did, but it won't.
The main problem is very close to what hoolio guessed at, the actual SELECT statement in MySQL comes only after the initial handshake, server welcome, client authentication, and subsequent information from the server about it's capabilities.
At the bottom is a link to the MySQL Server/Client Protocol wiki page. You are definitely headed down the right road, but you've got to convince the client that it is talking to a real MySQL server.
http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol - hoolioThanks for the information rcorder.
myocella, if the server does need to send data first, you could potentially use the skip bytes parameter set to 0 to allow the server to send data first. Check this post for more info from Spark:
(Click here)
Make sure to read Spark's caveats at the bottom of his post.
Aaron 
pjcampbell_7243Cirrus
are you going to use this to send reads to different server than writes?
did you make any progress?
Mario_Apitz___uHello,
is there any update to this? I'd like to know if it works the way it is described.
Regards,
Mario- L4L7_53191Gang: I think that this is a *very* tricky problem to solve correctly. It's already been noted that we're really talking about implementing a full-blown mysql proxy in iRules, and it gets worse (depending upon your implementation, of course) - imagine a statement like (taken from a random search on google for "mysql update based on select":
UPDATE products p set p.product_model=(select categories_name from categories_description where categories_name like CONCAT(p.product_mode,'%'));
Which I believe will need (or should) hit the same instance for both the update and the nested select that the update is based on. This may or may not be the best example, but IMO it's extremely easy to write a brittle rule that could break things later on, barring some very specific architectural designs and policies that will ensure you won't run into a use case like this.
-Matt