[nycbug-talk] mysql replication and failover

[David Rio Deiros]

Hi there,

I have been doing some research about how to deploy a replication and
fail-over mysql solution with two servers (master/slave).

The replication part seems pretty easy to implement but the fail-over
seems to be more complicated.

I have seen a couple of implementations that are based on this:

. mysql is configured to do replication.
. the slave pings the master to see if it is alive
. if it is dead, the slave changes the dns information to make
the master name point to its ip.
. when the master is back again the dns is changed back to point
the master's ip.

I don't know what you guys think but I see tons of problems with this
configuration, but the most important one is:

The replication solution is totally asynchronous, which means that
the master could potentially commit information  before those changes 
are also committed to the slave. This will end up with a corrupted 
database.

The only real solution that I have seen so far is MySQL clustering but
I don't have time, neither the resources to implement that solution.

What do you guys think? Is MySQL clustering the only valid solution
here?

Thanks,
David