Master-master vs master-slave database architecture?
Solution 1:
While researching the various database architectures as well. I have compiled a good bit of information that might be relevant to someone else researching in the future. I came across
- Master-Slave Replication
- Master-Master Replication
- MySQL Cluster
I have decided to settle for using MySQL Cluster for my use case. However please see below for the various pros and cons that I have compiled
1. Master-Slave Replication
Pros
- Analytic applications can read from the slave(s) without impacting the master
- Backups of the entire database of relatively no impact on the master
- Slaves can be taken offline and sync back to the master without any downtime
Cons
- In the instance of a failure, a slave has to be promoted to master to take over its place. No automatic failover
- Downtime and possibly loss of data when a master fails
- All writes also have to be made to the master in a master-slave design
- Each additional slave add some load to the master since the binary log have to be read and data copied to each slave
- Application might have to be restarted
2. Master-Master Replication
Pros
- Applications can read from both masters
- Distributes write load across both master nodes
- Simple, automatic and quick failover
Cons
- Loosely consistent
- Not as simple as master-slave to configure and deploy
3. MySQL Cluster
The new kid in town based on MySQL cluster design. MySQL cluster was developed with high availability and scalability in mind and is the ideal solution to be used for environments that require no downtime, high avalability and horizontal scalability.
See MySQL Cluster 101 for more information
Pros
- (High Avalability) No single point of failure
- Very high throughput
- 99.99% uptime
- Auto-Sharding
- Real-Time Responsiveness
- On-Line Operations (Schema changes etc)
- Distributed writes
Cons
- See known limitations
You can visit for my Blog full breakdown including architecture diagrams that goes into further details about the 3 mentioned architectures.
Solution 2:
We're trading off availability, consistency and complexity. To address the last question first: Does this matter? Yes very much! The choices concerning how your data is to be managed is absolutely fundamental, and there's no "Best Practice" dodging the decisions. You need to understand your particular requirements.
There's a fundamental tension:
One copy: consistency is easy, but if it happens to be down everybody is out of the water, and if people are remote then may pay horrid communication costs. Bring portable devices, which may need to operate disconnected, into the picture and one copy won't cut it.
Master Slave: consistency is not too difficult because each piece of data has exactly one owning master. But then what do you do if you can't see that master, some kind of postponed work is needed.
Master-Master: well if you can make it work then it seems to offer everything, no single point of failure, everyone can work all the time. The trouble with this is that it is very hard to preserve absolute consistency. See the wikipedia article for more.
Wikipedia seems to have a nice summary of the advantages and disadvantages
Advantages
If one master fails, other masters will continue to update the database.
Masters can be located in several physical sites i.e. distributed across the network.
Disadvantages
Most multi-master replication systems are only loosely consistent, i.e. lazy and asynchronous, violating ACID properties.
Eager replication systems are complex and introduce some communication latency.
Issues such as conflict resolution can become intractable as the number of nodes involved rises and the required latency decreases.