MySQL Forums
Forum List  »  Router & Proxy

Redirecting client to specific backend on a user/db basis
Posted by: Gurvan Kervern
Date: March 11, 2010 08:38AM


The company I work for would like to set up a production environment in which client applications would have one point of entry and then be redirected to multiple MySQL instances/servers.
The choice would be made first based on user (user A goes to server X, user B to server Y, etc...) and maybe later on user + db (user A has the right to access databases L, N, O, R on server X, databases F, G, I on server Y, etc...)

We thought about MySQL Proxy so I started to study it and looking at the MySQL authentification process, I started to wonder if it was at all possible after MySQL 4.1 since the client won't send its hashed password until it has received the scramble buff from the server.

I saw that Pete Clapham, Boyan Dimitrov and Arnold Daniels have worked on this problem but can't find a way to contact them to ask if they were successful.

I had two ideas, it would be great if a MySQL Proxy developper or lua coder could tell me if it is possible :

Maintaining a pool of connections and make a tail call from read_auth() to connect_server() to start again authentification on the right backend, so read_auth() would look a bit like that :

function read_auth()
if proxy.connection.client.username == somecondition
return connect_server(someparameter) --not sure if connect_server() can accept parameters, the documentation says not, so maybe redifining a global variable instead

I know a tail call is possible in lua, but wouldn't it mess with MySQL Proxy's successive calls of connect_server(), read_handshake(), read_auth(), read_auth_result()?

Creating on the proxy a table of all known users, their SHA1(password) (and potentially on which backend are the databases they can access) so we would have :

______ _____ | BACKEND 1|
|CLIENT|----------->|proxy|-----------> __________
array_of_users | BACKEND 2|

Then when a client tries to connect, generate a fake Handskahe Initialization Packet and send it back to the client, receive the Client Authentification Packet, process it to see it it corresponds to his/her password.
If it does then connect to the appropriate backend in place of the client, proceed to the authentification (the proxy would have the necessary elements : the client's SHA1(password) and the scramble buff of the server), and then forward the OK Packet to the client with read_auth_result() normaly.

Is it possible to forge every kind of packet from connect_server() and send it back to the client? I saw nothing about that in the documentation, only that you could drop the connection, or choose a specific backend based on server load, etc... or round robin.

I have never coded in Lua and am really a newbie with Mysql Proxy so if anyone has any idea on how this could work (or not), that would be great.

By the way, if anyone knows of another MySQL tool that could allow me to realize the project I'm working on, I'm all ears.


EDIT : sorry, my explanatory "ascii art" got messed up. It was intended to show that the table was located on the proxy server, not the client or backend.

Edited 1 time(s). Last edit at 03/11/2010 08:41AM by Gurvan Kervern.

Options: ReplyQuote

Written By
Redirecting client to specific backend on a user/db basis
March 11, 2010 08:38AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.