High availability SQL server setup

[Steve Bertrand]

Hi all,

For some time, I've been considering consolidating all/most of our SQL
databases (all MySQL) onto a single dedicated cluster setup.

I'm looking for feedback on the best way to do this.

All of the options I've considered so far have both their drawbacks and
benefits. From what I can tell, there's no one single way to be able to
have everything that I want.

Off the bat, I haven't found a way to create a cluster that can have
more than one host in the cluster writable.

My objective would be to start with two very high end boxes. One would
sit in my primary location, the other a few blocks away over a gi fibre
link.

I would want the remote box to pick up immediately if the master server
fails. I figure I could achieve this using network trickery for IP
failover, CARP or the like and span a couple of vlans across the fibre.

I would want each SQL server connected to separate edge routers to
ensure both server and network resilience. Each box has two GigE NICs,
so off the bat, I'd have each box doing VRRP to two separate edge gear
at each location.

My concern is, is that I can't envision how both boxes could possibly
stay in a continuous state that would allow such fail-over, and
fail-back. (fail-back is less of a concern...if it comes to it, I'd
rebuild by hand if necessary).

I've considered ZFS replication, but there could be several minutes
worth of snapshot missing if the primary fails.

I already have MySQL replication in many spots, but that's only one
write master and read-only slaves.

Can you provide any details or new ideas that I'm missing in order to
have the holy grail of SQL redundancy?

Cheers,

Steve