Mysql Master-Master Replication Topologies on >2 machines

Question

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.

Answer 1

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