Re: Haproxy with Mysql

Hi Manoj,

On Sun, Aug 14, 2011 at 09:28:33PM +0000, Manoj Kumar wrote:
> Hi,
>
> Is there any way to forward mysql client ip address to backend mysql. We have Mysql servers frontend by haproxy but when we check the processlist on mysql servers it is showing IP address of the haproxy. We can not able to find which client is sending requests to Msqyl servers.

It is possible to do that, but not recommended. This requires either a very recent Linux kernel or to apply the TPROXY patches to your current kernel and to patch the iptables tool. You then have to build haproxy with the LINUX_TPROXY feature enabled, so that it can bind to the client's IP address when connecting to the server, and last, you have to reconfigure your mysql servers so that they route by default via the haproxy machine, so that the return traffic can pass there too. It can be a bigger issue if your clients and servers are on the same LAN because you'll have to add host-based routes.

Clearly, having a machine spoof the client's IP address is really an annoying thing to do. It does work but needs to reconsider many points in your architecture. This is typically something that should be avoided whenever possible.

Alternatively, the PROXY protocol that was implemented in haproxy to pass the IP address of the client to the server might be implemented, but you need to implement it in mysql so that it can make use of it. I know that some other people have implemented it in other products. The big advantage is that you don't have to change your routing nor patch your kernels.

Regards,
Willy Received on 2011/08/15 00:22