0

My service runs on single machine, and database (mysql 5.1.66) is replicated to 2 other identical slaves (with lesser system resources, mysql 5.1.66) via chained replication. Machines are located in different isps. There will always be a single entry point to service, and if master goes down, i'll switch to the next slave and continue.

simply the scenario is as master -> slave1 -> slave2.

But i don't want to deal with resyncing the master, when it comes up. I'm thinking of switching to circular replication model. which is:

master -> slave1 -> slave2 -> master ...

I've never used circular model, and the latency between master and slaves do not bother much in current implementation. But with circular model, i guess master will also be waiting for a reply from slave2, even though no nodes other than the master will be writing the database. Is this latency -if exists- a show stopper? Is it sane to implement circular replication between different locations?

Improve this question

1 Answer 1

Reset to default
1

CIRCULAR REPLICATION

You should upgrade to MySQL 5.5 because it features Semisynchronous Replication

There are settings that can let you dictate how much delay a Master can tolerate before switching back to asynchronous replication until latency dies down. I have written posts on Semisynchronous Replication before:

Some have argued that it is not good over a geographic distance. This is debatable depending on several factors

  • Using Statement-Based or Row-Based Replication
  • Actual Distance Between Data Centers
  • Doing DML statements on all MySQL Masters
  • If you are plain old content living with latency

DATA RESILIENCY

If you have Multiple Masters in Different Data Centers for the purpose of Disaster Recovery (or as the politically correct would say, "Business Continuity"), you should try to make the Data Center intrinsically resilient for the sake of MySQL. How would you do that?

Setup DRBD in each Data Center. Then, setup Circular Replication among the DRBD Primaries.

Here are my posts on setting this up

Improve this answer
2
  • Rolando thanks for your answer. If i understood correctly, the timeout for switching to asynchronousness is the bottleneck here. If i set the timeout to very small values, would it make this scenario feasable? I only need master, or whatever node is serving the service at a given time, to not wait the one before itself.
    – altunyurt
    Commented Apr 4, 2013 at 14:35
  • Yes, setting rpl_semi_sync_master_timeout=500 should cause 0.5 second delays to switch to asynchronous replication.
    – RolandoMySQLDBA
    Commented Apr 4, 2013 at 15:14

Your Answer

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

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