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.