What you are proposing is not really CAP Theorem unless the cluster is totally a share-nothing architecture. Please note what CAP Theorem is from my old post from
Jan 01, 2013 : Consistency in ACID and CAP theorem, are they the same?
- Consistency (All Nodes Have Same Data via Eventual Consistency)
- Partition-Tolerance : system continues to operate despite arbitrary message loss or failure of part of the system
Each node would have a complete copy of the full dataset. The given DB Cluster would need to be support eventual consistency i.e. have ACID Compliance support cluster wide.
YOUR ACTUAL QUESTION
But is it true in fact of using MySQL? Or maybe we need to use any kind of middleman? How does CAP theorem apply to MySQL?
The MySQL setups that operate on CAP Theorem are as follows:
Your best middleman ??? If the data you write is critical and needs to be available, read the data from the Master you wrote it. In any of the aforementioned Cluster Setups, any node can be a Master. If data is written on one Master, they should all have it and be ready to be read back provided all nodes in a Cluster run COMMIT on that same given transaction.
If you have slaves attached to these Masters, each slave must wait on its Master COMMIT for data to be be replicated and become visible on a slave.