Best way to setup master to multi master replication

ORIGINAL QUESTION:
I have multiple servers that all need to act as masters, as if one fails the next server kicks in and takes over. I'm just wondering what the best way to replicate is. All servers are in different data centers.

Questions

• Would it be ok to set up every server as a master-master with the 'supermaster'?

• What sort of conflicts would I have?

• Are there better ways to do this?

UPDATED QUESTION:
We have servers all around the world in different data centers and each server needs access to a db usually this is on localhost.

Each server needs to be able to update the database and each server is pretty much a mirror of every other server. These all stay in sync via one server(supermaster) that all can communicate with.

All the servers only communicates with supermaster, they are not aware of any other server. The super master it self is a mirror of the other servers, just with the added service of syncing them all.

Some times the supermaster is offline for various reasons; when this happens the other servers carry on as normal which includes reading and writing data. When the supermaster comes back up, it starts the syncing process and sorts out and resolves conflicts between the other servers, when this is complete all servers have a mirror of the data and are all 'synced'.

So after thinking a bit more on the question, My questions is;

• Is there any way to have a single db on each server that are the same

across the board, without having a single point where I can update
the db as in a Star Topology?

• Is there a better option for replicating mysql across the board for my situation?

Any help is much appreciated.

There are four(4) options you need to combine
Option 1: MySQL in Star Topology

So as not to reinvent the wheel, please read my past posts about this subject

• Mysql Master-Master Replication Topologies on >2 machines

• https://stackoverflow.com/questions/5273043/mysql-in-star-topology/5781504#5781504

• https://serverfault.com/questions/264374/mysql-in-star-topology/264444#264444

Option 2: Use DRBD

• The supermaster should be a DRBD pair and using a DBVIP

• The Star Topology's distribution master should replicate from the DRBD primary

• All Slaves should replicate from Star Topology's distribution master

• See my past post on using DRBD in Circular Replication between two datacenters (DRBD cannot sync well over geographic distance but can provide disk-level + failover capability per data center)

Option 3: Use Semisynchronous Replication

Any network latency can affect the collecting of binary logs and its shipping of entries over a network could be bottleneck. Using MySQL 5.5's Semisynchronous Replication can allow you to tune the MySQL Heartbeat characteristics so as to minimize replication losing its place due to any significant network latency.
Option 4: Circular Replication

If you have multiple masters, circular replication is a must. There are many products out there for providing circular replication, such as mysql-mmm (mentioned in the comment), which is great within the confines of a single datacenter. This is only one piece of your puzzle in that four(4) things must be accommodated outside of just circular replication:

• Network latency

• Geogrpahic Distance

• Disk-level Redundancy per DataCenter (handled by DRBD)

• Automatic Failover of DBVIP backbone of DRBD (via ucarp)

CAVEAT

This may not be your full answer but these options can provide ideas you can try out in different scenarios. For example, when in the star topology, you could do the following:

SCENARIO #1

• Have the DB Server at one data center be SuperMaster holding InnoDB data

• Have the Distribution Master in the same data center (binary logs only)

• Have all Slaves replicate from the Distribution Master

SCENARIO #2

• Have the DB Server at one data center be SuperMaster holding no data, which means the supermaster is the Distribution Master (binary logs only)

• Have all Slaves replicate from the Distribution Master

SCENARIO #3

• Setup a Distribution Master in each Data Center in Circular Replication

• Within each Data Center, have MySQL Instance (Local SuperMaster) with InnoDB as the only Storage Engine

• From each Local SuperMaster, hang two or more read slaves. Each read slave should use MyISAM as the main Storage Engine. Each MyISAM table should have ROW_FORMAT=FIXED to increase read speed 20-30%

As far as the possibilities go, it's up to your imagination and department budget.

Links for MySQL and DRBD

• http://dev.mysql.com/doc/mysql-ha-scalability/en/ha-drbd.html

• http://downloads.mysql.com/docs/mysql-ha-drbd-en.a4.pdf

DRBD for Disk Level Redundancy and ucarp for DBVIP Automatic Failover

• http://www.drbd.org

• http://www.ucarp.org

StackExchange Database Administrators Q#9424, answer score: 8