1

We have 4 MySQL servers and our main motto is that data should replicate across all the 4 servers if one is getting updated and the same changes has to go to all other 3 servers.

And below are the few other requirements

1) Changes need to be on all 4 servers.

2) one should be active at anytime which should be on Read/Write and rest of 3 to be on read-only mode.

3) If switch/Failover happens the new master should only be read/write and other servers has to be on read only

We want to achieve the above explained funtionality through the Circular replication mechanisum? Is it possible?

If not possible then what would be the recommended HA setup solution to meet above said requirement.

Appriciate any help or suggessions on this.

Improve this question

1 Answer 1

Reset to default
1

I do not know why trying to fit circular replication in such a setup- a circular replication has multiple single point of failures (one per server, as if any server fails, the "circle" is broken) plus with non-gtid replication solving the failover is not easy at all (what is the binary coordinates equivalent to the binary coordinates of the next server?). While there are some very specific and contained cases where it could make sense, I would say yours is not one of them.

What you described, 1 read/write server and 3 read-only ones is the paradigmatic usage of traditional replication where the master is r/w and it has 3 slaves depending on it. So, a tree-like topology, not a circle.

Of course, replication, up to the most recent versions has its issues (traditionally being asynchronous, no possibility of checking for drift, no paralelization, no automatic resolution of conflicts), so in some cases you may want to check alternative options, like Galera or Tungsten replication. However, many of the shortcomings have been solved in the most recent versions with semisync replication, GTID, parallel apply of logs, and in the upcoming 5.7, multi-master replication.

My recommendation would be to stick with standard replication with one master and 3 slaves, if possible using 5.6 GTID, as it makes the failover easier.

However, replication does not provide HA by itself. There are different approaches, depending on the technology used, here you have some suggestions:

  • MySQL failover is a small first party utility that can monitor replication and help you change the topology of your setup. If you want a more all-in-one solution, check MySQL Fabric
  • MHA allows you to check and change the topology of your setup, maybe combined with virtual IPs to guarantee the service
  • HAProxy allows you to setup a couple of virtual read/write and read-only services that are backed by several MySQL servers, so it can transparently check for failure and switch the active server, or load balance between several servers

Here you have a presentation with some thoughts about different methods of doing HA with MySQL.

Improve this answer
0

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.