Newbie questions about failover/LB
Hello,
I apologize if this is ridiculously newbie-ish and uninformed; I'm new to replication and HA with MySQL, but I have been reading a lot lately. Currently I have one master server which almost everything uses. I have replicated one database to a couple machines that needed lots of connections, and they connect to localhost. These servers only do reads so it works out. Recently my master server had RAM die, and was down for tho hours, and it wasn't fun as I'm sure you can imagine.
I have a working automatic fail over "system" I'm testing in VMware now (not recovery, just fail over). It's basically a simple shell script that does some query checking and, if necessary, grabs a VIP; probably pretty typical. Anyway, I've read a bunch of articles about MySQL Proxy, and everything says it will do exactly what I want. I also went through this list, but I still have a few questions about the load-balancing and fail over capabilities.
My main question is: how are the master back ends set up in a fail over/LB scenario? Replication has been pretty unreliable for me so far (to my two localhost slaves), but I figure you need to replicate between your databases, and let MySQL Proxy either fail over from A to B, or load-balance between them (thus achieving fail over). This makes me think that one connection could write to one server, and one to another, which could cause inconsistencies, especially if replication was going slow or broken...so AFAICT LB between two masters would be terribly risky, but I'm also not sure how to set up failover between them. Every article I've seen says to just specify multiple back-ends, and get load-balancing, but none say anything about simple fail-over.
Is it as easy as specifying multiple backends and writing a connect function to choose the live server (hopefully based on SQL and network tests, not only network tests)? Do you have to manually disable load-balancing? I feel like I'm missing something basic about these scenarios...
Thanks for putting up with what I hope isn't too dumb/simple/obvious of a question. :)