0

Sorry if I ask this basic question. I am reading different articles and trying to understand how it works.

Lets suppose I have two master DB(MySQL) in two different availability zone and two different region which are used for writing data and data is replicated in Synchronous way because I do not want any data to be lost during replication.

And there are 4 slave (As per my understanding this is called Read replicas) which are used for reading the data. The data is replicated from Master to Slave as per the steps here https://dev.mysql.com/doc/internals/en/replication.html. Is it Synchronous or Asynchronous replication? The main purpose of multi-master setup as I understood is that in case one Master node is down then another Master node can be there always.

But then I am thinking, why leader-follower algorithm is not used here, for example, lets suppose I have one Master node for writing purpose and 4 slaves for reading purpose in different Availability zone. The master DB is in Region US and AZ-1. But slaves DB And data is synchronized in the same way as before. Now, if the master node goes down then one of the slave node can become Master by implementing leader-follower algorithm. So, we do not need multi master.

As per my understanding, in approach B there is a possibility that data can be lost if master node goes down. And also concurrent update into multiple Master node can cause data inconstancy. What are the pros and cons of this approach? Could you please give some use cases where both of the approaches fit.

1 Answer 1

0

Notes:

  • Primary-Replica replication is not synchronous.

  • Primary-Primary (aka Multi-Master) is simply Primary-Replica in both directions; it is not synchronous.

  • "Semi-synchronous" only assures that some replica has recorded the write.

  • Using different AZs helps avoid 'natural disasters', but leads to delays that may make any synchronous solution unacceptable.

What to do...

Plan A: Cloud providers do a lot to avoid loss of data, trust them. Keep in mind that their networks are limited by the speed of light and the "zones" are several milliseconds apart. Continents are on the order of 100ms apart.

Plan B: Have servers in 3 geographical locations and use either Galera (included with MariaDB or can be added onto MySQL) or InnoDB Cluster. Both of these are effectively synchronous, but there is a delay during COMMIT to make sure that a write has made it to the other servers. (Note: auto_commit=on` has an implicit commit on each write operation.)

Plan C: Redesign your code to use "eventual consistency" and use NDB. (This is a lot of work, and has other limitations.)

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.