October 9th, 2008


MySQL failover

So we're running some MySQL at work, which is a little unusual for us, but is probably long overdue. (Specifically, it's for some Wordpress instances.)

What I'm wondering is, what have people found works best (in terms of management effort first, performance second) for MySQL failover/ high-availability/ clustering/ etc.?

We're running a Master-Master setup w/ the built-in replication, and it works... but when it gets out of sync, it gets ugly. I'm not a huge fan of this, because it means a lot of potential, recurring headaches down the road. We're using HyperDB but I'm not sure how well it really works.

An alternative version of this that we're considering is running Master-Master, but only pointing the webservers to a single instance, and then failing over (either manually, or through something like linux-ha or lvs or whatever) to the second only if the first stops working. This gives us failover capabilities, but effectively means we don't have to worry about a bi-directional sync (most of the time). The downsides are that manual failover means downtime anytime there's a failure, and automatic failover opens the potential for flip-flop scenarios where it's bouncing back and forth frequently. (On the other hand, this still isn't as bad as having both masters get written to, all the time, it seems...)

I've read about using linux-heartbeat and DRBD to manage the databases, and having it literally syncing the mount itself. This is kind of nifty but we've used DRBD before and I didn't really like it, so I'm not really leaning towards this. But... it *does* seem to be potentially the most "elegant" solution in some ways.

Obviously, I know about MySQL Cluster, but since I don't know how large our data is going to grow, I'm not sure if an in-memory solution will work. That, plus the base requirement is setting up 5 nodes (2/2/1) and that seems like a lot of work for "simple" (cough) failover.

I've had a recommendation for MySQL Proxy as the "failover" layer, and then we could just run two MySQL Proxy boxes. Seems reasonable, but not sure what the plusses/ minuses are, other than the fact that I didn't find a lot of examples of people writing about it doing a quick google search, and I'm not sure how well tested or thorough it is as an HA solution.

I found this article:

Looks pretty good, and I'm reading it now, but again, I'm not sure if I should trust its solution (MPP). Maybe, maybe not.

So since I know a bunch of you are running real, live MySQL instances and presumably have some need to keep them up and running, what have you done, and how has it worked? 'eh, 'eh?