Replication & failover with 4 servers

[Joao Villa-Lobos]

Hi all,

I've read some of the posts already on the forum about this issue but since none of them addressed the same situation that I will have to set up, I decided to create a topic of my own.

I am trying to figure out how to set up replication & failover in a scenario with 4 servers (2 per location) where any server may assume the Master role. My initial scenario is the following one:

- 2 servers in location A (One Master, One Slave);
- 2 servers in location B (Two Slaves).

For this I'm thinking on using the configuration Master-Master Active-Passive suggested on O'Reilly's "High Performance MySQL" on all of them so each one can become a Master when needed.

If the Master "dies" the other server from location A assumes the Master role whenever is possible. It will always have a bigger priority then the servers on location B.
A server on location B will only switch to Master if no server on location A is able to do so.

Since MySQL can't handle this automatically I need some other way to implement this. I've read already about heartbeat and Maatkit. Is this the way to go? Has anyone used this in a similar scenario? Is there some other way to go in order to achieve this? Any pointers about failout will be appreciated.

I want to keep this as simple as possible avoiding stuff such as DRDB. I'm not concerned about high availability just a way to switch roles automatically.

Thanks in advance,

João