mysql-proxy-discuss team mailing list archive

[Samit Pal <samit04@xxxxxxxxx>]

Thanks for the detailed reply Jan.

The ip based mechanism is an option though it might be a little
cumbersome to maintain so many ips with the scale that i am in. I had
another thought. What if i use a master mysql server for the the
clients to connect to via the proxy till the auth phase. Once
authenticated can we switch the backend to a new one in the read_query
phase based on username, now that we have the user name, default db
etc through the read_auth() hook?


On Tue, Jun 9, 2009 at 12:47 AM, Jan Kneschke <jan@xxxxxxxxxxx> wrote:
> Samit Pal wrote:
>> Hi Folks,
>>
>> I plan to use the following mysql architecture. We will have multiple
>> mysql servers on different ports on the same machine. Each db server
>> process will run as a different user. I want to use mysql proxy and
>> run it on port 3306 (on the same machine may be to start with), so
>> essentially users connect to the mysql proxy which should switch the
>> connection to the appropriate db server possibly based on the username
>> supplied. I am not looking for load balancing, just pure switching. Is
>> this possible with mysql proxy?
>
> Nope. It is not and won't ever be possible in a transparent way.
>
> The problem is the way the auth-protocol works:
> 1) client connects proxy,
>   proxy connects to a server and get a "challenge",
>   sends it back to client
> 2) clients send username and the scrambled password
>   as response to the challenge to the proxy ... to the server
>
> At the time you get access to the username, you already had to select a
> mysql-server to connect to.
>
> The only way to make it work is:
> 1) client connects to proxy, proxy sends back the challenge
> 2) client sends username + response to proxy
>   proxy verifies data against a local auth-storage
>   proxy connects to server, gets a new challenge
>   proxy sends client's username + scramble password
>
> The problem is: the local auth-storage has to have the same information
> as the mysql-servers mysql.user table. We need that hashed password or
> cleartext-password, any of them is fine. None of them is on the network.
>
> We don't support that yet, but that's the only way to do it.
>
> It is easy if you do something else instead:
> * give each user its own IP address
> * put all the IPs as virtual interface on the proxy box
> * let the proxy check the proxy.connection.client.dst.address what IP
> the client connected and pick the right backend
> * that works in 0.7.x
>
>> Thanx
>> Samit
>
> cheers,
>  Jan
> --
>  jan: "Gee, Brain^WEric, what'd you wanna do tonight?"
> eric: Same thing we do everynight: Take over the HelloWorld!
>