Mysql – Best way to setup master to multi master replication

MySQLreplication

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.

Best Answer

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

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

DRBD for Disk Level Redundancy and ucarp for DBVIP Automatic Failover

Related Solutions

Mysql – Converting 3 Master + 1 Slave to 4 Master – Any advice

Here is the topology you just described

+-------------+
|             ^
|             |
V             |
M1 --> M2 --> M3
|
|
+----> S1

You would like to slip S1 as a Master into the Replication Ring so that it looks like this:

+---------------------+
|                     ^
|                     |
V                     |
M1 --> S1 --> M2 --> M3

Essentially, you only have to prep S1 to a Master and Point M2 to receive binary log entries from S1.

OK Here we go

STEP 01) Point your application at M3

STEP 02) Prep S1 to be a Master

set the server_id as a different number from M1, M2, M3
set log-slave-updates in my.cnf like you did for M1, M2, M3
enable binary logging on S1 the same way you enabled it on M1, M2, M3
restart mysql on S1

STEP 03) run STOP SLAVE; on M1

STEP 04) run SHOW SLAVE STATUS\G on S1 and M2

Make sure Seconds_Behind_Master is 0 on S1 and M2

STEP 05) run SHOW MASTER STATUS; on S1 (Record the binary log and position)

STEP 06) run this on M2

STOP SLAVE;
CHANGE MASTER TO master_host='IP Address of S1',
master_post=3306,
master_user='repluser',
master_password='replpass',
master_log_file='XXXX'
master_log_pos=YYYY;
START SLAVE;
SHOW SLAVE STATUS\G

where XXXX is the binary log from STEP 05 and YYYY is position from STEP 05

If the SHOW SLAVE STATUS\G says Yes for Slave_IO_Running and Slave_SQL_Running then you have achieved this:

+---------------------+
                      ^
                      |
                      |
M1 --> S1 --> M2 --> M3

STEP 07) run START SLAVE\G on M1

STEP 08) run SHOW SLAVE STATUS\G on M1, S1, M2, M3

Where Seconds_Behind_Master is 0 on all the servers...

STEP 09) Point you application to other servers as desired

Any questions ???

If none

Give it a Try !!!

Your mission, should you decide to accept this, is to practice this is a Dev/Staging Environment and make sure you trust this algorithm before doing this in Production.

In the event your data is caught or killed, the DBA StackExchange and I will disavow any knowledge of your actions.

Mysql – 3 Databases, Best Replication Setup

I wrote up an interesting layout last year which features DRBD pairs in two data centers (DC1,DC2) with as follows

DRBD Pair in DC1 (db1 and db2)
DBVIP for Primary of DRBD Pair1 is 10.1.2.30
DRBD Pair in DC2 (db3 and db4)
DBVIP for Primary of DRBD Pair2 is 10.1.2.40
Have MySQL Circular Replication Between DRBD Primaries
Have the 10.1.2.40 as Master_Host for DBRD Pair 1
Have the 10.1.2.30 as Master_Host for DBRD Pair 2

MySQL high availability, failover and replication with Latency

MySQL Replication : 1 Slave / Multiple Masters

Here is why I suggested this: Using two data centers, you setup automatic failover for DRBD Pair in one data center. Let the other DRBD Pair in the second data center be for DB disaser site with it own local redundancy and failover. Should you ever loses one data center, the other data center is fully read with it own local failover setup. Your app would just have to use the DBVIP of the other database center in such a catastrophic case.

Please keep in mind that using DRBD in conjunction with MySQL is only beneficial if all of your data uses the InnoDB Storage Engine. Hard failovers in DRBD could easily result in crashed MyISAM tables.

Here is another setup to consider:

As with DRBD setups, any DRBD Secondary would provide just a Disk-Level copy of your MySQL Folder. It is available as a warm standby. MySQL is not being run on the DRBD Secondary. If you want the third DB server to become hot standby, ditch DRBD altogether and use pure MySQL Replication. With three DB servers, using db3 at a remote site, simply setup the following:

+--> db1 --> db2 --> db3 -->+
^                           |
|                           V
+<--------------------------+

Using your rudimentary failover, now you have two hot standby servers. You just have to make sure each DB server has a unique server_id value. I also recommend using MySQL 5.5 because it uses SemiSync Replication which is more sensitive to communication failures and stop Replication better. You will have to setup the appropriate heartbeats and timeouts.