7

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.

Improve this question

1 Answer 1

Reset to default
8

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:

  1. Network latency
  2. Geogrpahic Distance
  3. Disk-level Redundancy per DataCenter (handled by DRBD)
  4. 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

Improve this answer
6
  • Sounds as though there is a bit to think about. I'm interested in option 1 (first link) (under 'update') are there any tutorials around the web dealing with this, so I can get an idea of how it is done in depth?
    – Elgoog
    Dec 19, 2011 at 23:49
  • also another question could I mirror the Distribution Master in case of failure? With all other slaves still active and updates getting send to the 'mirror' DM
    – Elgoog
    Dec 19, 2011 at 23:52
  • That's what DRBD is for: To mirror mysql DB folder for SuperMaster. You could also setup another DRBD pair just for mirror the Distribution Master.
    – RolandoMySQLDBA
    Dec 19, 2011 at 23:59
  • After thinking about this during lunch I have updated my question to better reflect the situation. Weather or not your answer is still applicable, I thank you for your help and would like to get your thoughts on the situation.
    – Elgoog
    Dec 20, 2011 at 0:48
  • Elgoog, would you be interested in MySQL MMM? It uses scripts to create a single ip address as a point of entry to write to any available MySQL server in your pool of servers. Here is a link, if you'd like me to post an answer with how I set it up at work let me know. mysql-mmm.org
    – Craig Efrein
    Dec 20, 2011 at 6:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.