I have configured a MySql Replication with 1 Master 1 Standy and 1 Slave. Master and Standby are put on different availability zones on US East region and the Slave is on the US West region. Both Standby and Slave are replicating from the Master.
The Application layer can do the failover from Master to the Standby.For switching the replication of the Slave to the Standby when the Master dies. I have no way to assure that the Standby's data is ahead comparing to the Slave's data. If Standby's data is behind the Slave's data I need to scan the Slave's binary and relay logs to find out what is missing and transfer it the Standby. That's the THEORY.
Does anyone know any tool which can handle this ? or alternative solution for my context ? The ultimate goal is the availability of MySql Server for application layer. I have tried MySql-Proxy but not quite happy with it. Mysql Cluster is not an option for us.
Thanks in advanced.
Kane,
Take a look at SchoonerSQL from Schooner Information Technology (my company). It is designed to have zero slave lag locally through multi-threaded synchronous replication (in current release) and automatic fail-over across regions through multi-threaded Asynchronous WAN replication (to be released in < 2 wks). http://www.schoonerinfotech.com/products/schoonersql