2

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.

Nikhil Kashyap
  • 1,049
  • 2
  • 11
  • 15
  • Any other tool that the community knows for MySQL failover on Windows? – Nikhil Kashyap Jan 13 '09 at 12:27
  • Is there an obvious solution for linux that would justify your last statement? I'm implementing a similar setup, but with linux servers, so thought I'd ask. – Kevin Dec 23 '10 at 04:52
  • Kevin, please see: http://dev.mysql.com/tech-resources/articles/failover-strategy-part1.html#t2 I have not tried this Linux HA setup, but I would think this is normally used in Linux. – Nikhil Kashyap Dec 23 '10 at 11:01

2 Answers2

2

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.
Gary Pendergast
  • 141
  • 11
0

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