ntang: (Default)
[personal profile] ntang
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?

Anonymous( )Anonymous This account has disabled anonymous posting.
OpenID( )OpenID You can comment on this post while signed in with an account from many other sites, once you have confirmed your email address. Sign in using OpenID.
Account name:
If you don't have an account you can create one now.
HTML doesn't work in the subject.


Notice: This account is set to log the IP addresses of everyone who comments.
Links will be displayed as unclickable URLs to help prevent spam.

November 2010

 12345 6

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Sep. 23rd, 2017 12:00 am
Powered by Dreamwidth Studios