CAP Theorm - Why Mysql is CA

Question

As Per CAP

Consistency - All nodes gave the same data

Availability means the ability to access the cluster even if a node in the cluster goes down.

Partition tolerance means that the cluster continues to function even if there is a "partition" (communication break) between two nodes (both nodes are up, but can't communicate).

But Mysql Default behaviour is master-slave or master-master.

So For, Master-Slave

• There is no Consistency as slave lag can happens and hence data read from slave is not consistent.
• There is no availability as slave cannot become master without DBA intervention.

So For, Master-Master

• There is no Consistency as slave lag can happens and hence data read from slave is not consistent.
• There is no availability as slave/other-master cannot become master without DBA intervention.
• Its partial tolerant as both will work fine in case of network failure.

Am i missing something ?

Fyrd

It might help to read about the PACELC theorem as CAP is often confusing. – Azole

There is fully synchronous replication, that guaranties Consistency. dev.mysql.com/doc/refman/8.0/en/replication-semisync.html – Tonetic

In a master-master cluster, you have high availability, if one node become unavailable, the others masters continue accepting writes and reads normally. – Tonetic

Answer 1

First point is CAP theorem is for distributed databases and should be read in those terms.

Now coming to CAP theorem, as per Wikipedia:

• Consistency: Every read receives the most recent write or an error
• Availability: Every request receives a (non-error) response, without the guarantee that it contains the most recent write
• Partition tolerance: The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes.

So when you say MySql default behaviour is master-slave, then it means master and slave nodes are just primary and secondary nodes and at a time only single node i.e. master is being used to process any user request. Hence, Partition Tolerance is already compromised here.

But the database is still Consistent, because all request (read/write) will go to the master node and user will get same consistent data all the time.

And the database is Available, because all request will receive response as all go to same Master node.

Hence by default Mysql in CA database. Slave lag couldn't impact either consistency or availability here.

There is another possibility for MySql, where via some configuration tweaks it can be made CP database and availability needs to be compromised. This will be kind of multiple master nodes distributed and remain in sync via networking between them. In that case, if any of the network or partition breaks among them, then Consitency is preferred means all read request will get same data, no corruption because Availability for write request is compromised.

Capapie

you say "And the database is Available, because all request will receive response as all go to same Master node." If master is not available then there will be no one to serve the request. then how can be MySQL available. Therefore MySQL in the scenario you mentioned will only be Consistent. – Stadtholder

Answer 2

MySQL database engine reaches CAP theorem with the help of introduction of a node to balance a Cluster design. So when you have two (2) nodes, if there is a network issue between the two servers, the cluster will be split in 2 partitions. And each of it will have 50% of the amount of total members (1/2). That’s why none of the partition will reach quorum and none will allow queries. So, to get a full Partition tolerance, Cluster design needs to add a third node at least so your database reaches a partition tolerance for one failure.

Thus, cluster continues to function even if the "partition" (communication break) between two nodes (both nodes are up, but can't communicate), because one partition still has quorum (2/3 = 66%, which is bigger than 50%).

Magnetochemistry

This is not what I asked for – Fyrd