Selecting backend based on the user connecting

[Jesper Juhl]

Hi,

I have a situation that I believe MySQL proxy should be able to handle, but as far as I can see it can't at the moment.

Let me describe what it is I need to do.

I have a DB server with a few thousand databases on it used by a few thousand different users.

I need to reduce the number of databases on the server - basically I need to move half the databases to a different physical server. The problem with this is that updating all the client code is out of the question, so after moving half the databases to a different physical server with a new ip/hostname half my users would no longer work. This is where I need the proxy.

Basically I need to setup the proxy on a machine with the IP/hostname of the original DB server so that is what clients connect to. Then I need to send clients to the appropriate backend server where their database is located based on their username.

The problem is that at the point where I can select a backend server (connect_server()) I don't know the username yet. I can't just pick a random backend server since I may be sending the client to a backend that doesn't hold his auth info.

As I see it, what I need is for the proxy to initially pretend to be the backend server and ask the client for authentication, then once I've recieved the auth info I can lookup what backend this user belongs to, then I need to initiate the connection with the appropriate backend and when I recieve the auth request I send the info I already recieved from the client and then send the reply back to the client.

Is this already possible and am I just not finding the functionality in the current svn code? If it's not possible, is it a planned feature?

Kind regards,
Jesper Juhl