CODEWITHSUNDEEP
mysql
John L.
John L.

Reputation: 1953

MySQL InnoDB Cluster Instance Requirement

Why does MySQL InnoDB cluster require at least three instances to work? Why isn't one primary and one secondary instance not enough? Isn't this how SQL Server failover cluster works for example?

Upvotes: 2

Views: 740

Answers (1)

Michael - sqlbot
Michael - sqlbot

Reputation: 179054

You and I are hiking in the woods. I turn to speak to you and suddenly realize you are not there. In that scenario... which one of us is lost and missing? Is it you? Or is it me? Can that even be defined?

If a node fails involuntarily, the remaining nodes must be able to confirm whether they form an authoritative quorum of the survivors. Otherwise, it isn't safe for them to continue operating, because they have no way of confirming their role in the cluster.

With fewer than 3 nodes, if one node is lost, it is impossible for any number of the remaining nodes to form a group that is equivalent in size to a majority (more than half) of the number of nodes before the failure occurred, and thus a quorum of survivors is impossible.

With 3 nodes, if 1 fails, the remaining nodes still form a majority of the number of nodes before the failure, because:

let n = 3
(n - 1) > (n × 50%) # true

With two nodes to begin with, the loss of one node leaves the other node unsure of whether it is isolated or the last one standing, and with no way to confirm this, since:

let n = 2
(n - 1) > (n × 50%) # false

https://dev.mysql.com/doc/refman/8.0/en/group-replication-fault-tolerance.html

Upvotes: 6

Related Questions