Just FYI, I already posted about this here:
http://forums.mysql.com/read.php?146,167125,168813#msg-168813 - but that's deep in a thread so could easily get missed, that's why I'm starting a new post.
With read/write splitting or just plain sending queries from one user to a different backend than another user there is a small problem. Connections are kept alive in a pool as in tutorial-keepalive.lua - the problem is that if a user reuses a connection that was previously auth'ed by a different user, then the user may not be able to access his databases unless the user that auth'ed the original connection also has access ot those same databases.
As a simple test, setup two backends with the same two users existing on both. Create a database1 on one server and give user1 access to it, create a database2 on the other server and give user two access to that one.
Since both users exist on both servers, connections from both users can be authed no matter what server we send the initial connection to - so far so good. Then we can look at the user who is connected and send his queries to the backend that has his database - most of the time this works great. However it breaks down as soon as user1 gets assigned a connection from the pool that was initially authed by user2 - in that case the queries get rejected since user2 (who owns the connection) does not have access to user1's database.
How can we solve this problem?
Let me give a real life example and the reason why I need this.
I have a MySQL server that is overloaded - the hardware can't be upgraded any further, so scaling performance that way is out of the question. The server has a bit over 7000 databases ovned by some 6000 users.
What I need is to split the databases between two new servers and then setup the proxy with the old servers IP so that clients can continue to connect like they always have. The proxy is then supposed to send queries from half the users to the first backend where their databases live and the other half should go to backend2 where their databases live.
I have no control what-so-ever over the applications that users connect to the databases with and changing *anything* on the user side of things is out of the question. I need to do this so it is completely transparent to the users.
I've extended the tutorial-keepalive.lua example with some Lua code to look up each user on their first query and determine the backend they live on and it works perfectly except when old connections get reused, since each user only has access to her own databases and get access denied errors when a connection that was authed by a different user is reused.
Is there any way to solve this problem or will I have to give up on MySQL proxy for this project?
Kind regards,
Jesper Juhl