Mysql Master-Master Replication Topologies on >2 machines I have 3 Mysql servers that I would like to replicate: two (including the main server) are local and one is remote.  Let's call my main server A, the secondary server B, and a remote, development server C.

As a possible configuration, I started reading about a ring configuration.  Some comments seem to look down on this type of topology.

My question is, given the current state of Mysql 5.1, what types of master-master topologies are advisable and known to be fault-tolerant?

Is a ring really a bad idea, in general?

In my case, A and B are on an intranet that isn't expected to have any network failures so B can theoretically be used as a failover for A.  Most of the time, I am working on C, a remote machine, which sometimes gets network hiccups in reaching A and B.  None of the data is currently critical (ie as long as they eventually get mirrored, it's fine), but I expect A and B to be mirrored tightly - within say a 5 second timeframe on light loads (I think this should be an easy requirement given my preliminary tests).

I currently have B as a Slave to A, but I am looking forward to going Master-Master on A, B, and C.
A ring can be a very good idea under certain conditions

For servers A, B, C

Server A 


Master of B
Slave of C


Server B


Master of C
Slave of A


Server C


Master of A
Slave of B


All three servers have 3 databases (db1, db2, and db3)

Here is the only setup that protects the three databases


restrict all reads and writes to db1 on Server A
restrict all reads and writes to db2 on Server B
restrict all reads and writes to db3 on Server C


Replication would do the following in respone


backup database db1 by replicating to db2 and db3
backup database db2 by replicating to db3 and db1
backup database db3 by replicating to db1 and db2


IMHO performing writes (INSERTs,UPDATEs,DELETEs) for db1 to server A,B,C makes all auto_increment values separate and distinct. This would make it brittle to restore data to other boxes.

More to come...

UPDATE

I have another crazy idea

Have you ever heard of a star topology? I posted stuff earlier on this subject


https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444
https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504


Create a master with two slaves

For servers A, B, C

Server A 


Master of B and C
All tables use storage engine BLACKHOLE


Server B


Slave of A
All user tables use storage engine InnoDB


Server C


Slave of A
All user tables use storage engine InnoDB


Benefits


Post all writes (INSERTs, UPDATEs, DELETEs) to serve A
Writes should be fast becasue BLACKHOLE tables map to /dev/null
Replication actual posts real data on Slaves (Server B and Server C)
no actual data will reside in server A, only binary logs
All SELECTs can go between Server B and Server C via a Load Balancer
Slaves would virtually be identical into terms of content


Drawbacks


Once you set this up you cannot freely use ALTER TABLE statements
If replication breaks at Server A, it stops updating the Slaves