MySQL failover using MySQL Proxy

Question

Hi,

I'm looking at implementing a failover mechanism for a MySQL database using MySQL Proxy (http://forge.mysql.com/wiki/MySQL_Proxy)

My proposed setup is as follows: One active MySQL server and one passive MySQL with two way replication enabled between the both of them. MySQL Proxy is the failover that sits in between these two servers and redirects traffic to the standby in case the active fails and vice versa.

Just wanted to know the community's experience here about using MySQL Proxy as a failover tool for two MySQL servers. Any gotchas that I should be looking at? Any other tips/tweaks/hacks that can improvise this setup?

And oh, I'm on Windows and hence the need for MySQL Proxy.

Answer 1

MySQL Proxy is a valid solution, plenty of people have been using it for high traffic production systems. There are a few things to remember:

• It's still in Alpha, so you really want to test it thoroughly before implementing it.
• Your application should have a failover method if the Proxy server goes down. If it's a website, perhaps a "we'll be back shortly" messsage and a notification system to tell you that it's down. MySQL Enterprise Manager is pretty good for this, though it does cost money.
• It will add more latency than a hardware load balancer, but it will allow you to customize it much more with Lua.

Answer 2

I've been using MySQL proxy for sometime now and found it to be very useful. However there seems to be new bugs introduced in 0.6.1 version as compared to 0.6.0. So you might want to be careful in choosing the versions. I think that this is a great product for failover and query injections, but since its in Alpha, bugs are to be expected.

http://forums.mysql.com/read.php?146,202953,202953#msg-202953

Answer 3

Hi can anyone cite examples of "plenty of people have been using it for high traffic production systems" we have big internal debate here on whether or not to use it and it would help to have examples