Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 5 down vote accepted

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

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
share|improve this answer
    
+1 wow - splitting up the write access to individual servers is brilliant! I look forward to reading the rest of ideas. –  kfmfe04 Dec 18 '11 at 0:15
    
ty for that tip on the star topology - I think once all the schemas are stable, that topology is a definitely a viable option! –  kfmfe04 Dec 21 '11 at 6:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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