Master master vs master-slave database

Master master vs master-slave database

1. Master-master vs master-slave database
architecture?
I've heard about two kinds of database architectures.
 master-master
 master-slave
Isn't the master-master more suitable for today's web cause it's like Git, every unit has the whole
set of data and if one goes down, it doesn't quite matter.
Master-slave reminds me of SVN (which I don't like) where you have one central unit that
handles things.
Questions:
1. What are the pros and cons of each?
2. If you want to have a local database on your mobile phone like iPhone, which one is
more appropriate?
3. Is the choice of one of these a critical factor to consider thoroughly?
Answer:
Master-Master: This is similar to Master-Slave architecture, the only difference is that
both the nodes are masters and replica at the same time i.e. there will be circular
replication between the nodes. It is most recommended to configure both the servers to
log the transactions from the replication thread (log-slave-updates) but it ignores its own
already replicated transactions (set replicate-same-server-id to 0) to prevent infinite
loops in the replication.
Master-Slave: In this, as each data has only one master, so consistency is not difficult.
Advantages of Master-Master Database:
 Masters can be distributed across the network that means in several physical
sites master can be located.
 If the one master fails, other masters will start updating the database.
Disadvantages of Master-Master Database:
 This introduces some communication latency, and eager replication systems are
complex in this architecture.
 Multi-Master replication systems will be mostly loosely consistent, i.e.
asynchronous, lazy and violating ACID properties.

2.  As the number of nodes involved rises and the required latency decreases, so
Issues such as conflict resolution can become intractable.

-----
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
1. Master-Slave Replication
2. Master-Master Replication
3. 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. 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.
=========================
One More…
Pros and Cons of MySQL Replication Types
There are two sides to everything. Explore both the good and the not so good of some MySQL
replication types.
Types of MySQL Replication
MySQL is one of the most popular open-source databases in the world. It is very easy to start
using MySQL for your application or website. For instance, MySQL is the default option for
popular CMS solutions such as WordPress and Magento.
However, it is not trivial making MySQL totally reliable and available in cases of high load.
Database replication and clustering can help you achieve this goal.
As MySQL is one of the first open source databases, its replication capabilities have evolved in
time. This is the list of the most used replication types for MySQL:

4.  Master-slave replication
 Master-master replication
 Group replication
 Multi-master cluster (available for MariaDB, which is the fork of MySQL)
Let’s look closer at each of them.
MySQL Master-Slave Replication
Master-slave replication was the very first replication option for MySQL database. It assumes
that you have a single Master server that accepts both reads and writes and one or more read-
only Slave servers. Data from the master server are asynchronously replicated to Slave servers.
Pros
1. It is very fast as doesn’t impose any restrictions on performance.
2. You can split read and write requests to different servers. For example, all analytics queries can
be made on Slave nodes.
Cons
1. It is not very reliable because of asynchronous replication. It means that some committed on
master transactions may be not available on slave if the master fails.
2. Write requests can hardly be scaled. The only option to scale write requests is to increase
compute capacity (RAM and CPU) of the Master node.
3. Failover process is manual in a general case. You should take care of promotion replica node to
master one.
MySQL Master-Master Replication
Master-master replication has been evolved from master-slave replication and solves its major
issues. This type of replication assumes that you have two or more master nodes that can accept
both read and write requests. In addition, you can have multiple slave nodes for each of your
masters. The replication between master nodes is asynchronous.
Pros
1. You have an option to scale write requests not only by increasing the computing capacity of a
single master node but via adding additional master nodes.
2. Failover semi-automatic because you have multiple master nodes. The chance that all master
nodes fail simultaneously is very low. If any of master nodes fail, there is at least one more
master node that can handle its requests.

5. Cons
1. Due to asynchronous replication between all master nodes, you can lose some transaction in
case one of the master nodes fail.
2. Due to asynchronous replication, you can’t be sure that backups made on each master node
contain the same data.
3. Failover is still not fully automated in case you need to promote a Slave node to Master one.
MySQL MGR Replication
MySQL Group Replication feature is implemented by a MySQL Server plugin and provides
replication in a completely new way based on distributed state machine architecture.
Group Replication allows creating fault-tolerant systems with redundancy that guarantees that
even if some of the servers fail (as long as it is not a majority), the cluster still will be available.
The unique feature of MGR replication is that it provides you with built-in automatic recovery
and conflict resolution.
How MySQL Group Replication can help you:
 Removes the need for manual switching in case of failure of one of the servers.
 Provides fault tolerance.
 Allows you to build a system with the ability to change data on any server.
 Provides automatic reconfiguration.
Pros
1. Automatic failover in case the Master node fails. Servers that belong to the same Group will
elect new Master automatically. Therefore, MySQL MGR replication can be considered as a
Clustered solution.
2. It is synchronous replication in general, so you can be confident that you will not lose committed
data in case of Master node fails.
3. You can scale both reads and writes by adding new Master and Slave nodes.
4. It is doesn’t impose big performance restrictions because it is enough to have only 2 Master
nodes for a full-fledged MySQL cluster.
Cons
1. It is available only for MySQL, but not for its forks: MariaDB and Percona.
2. One Group is limited to 9 nodes.
Galera Cluster (Multi-Master Replication)
Galera Cluster is a synchronous cluster of databases with several master nodes based on
synchronous replication. Galera performs the role of multi-master and allows you to direct read
and write requests to any node at any time. In case one of the individual nodes will be lost,

6. interruption of operations will not occur. Nodes are initialized automatically and there is no need
to back up the database manually and copy it to the new node. Galera is a very safe solution
because it provides synchronous replication, i.e. there is no visible lag on the slave side, and data
is not lost when a node is lost.
Pros
1. It is reliable because it guarantees data safety using quorum protocol.
2. Galera provides with you with true clustering capabilities, including automatic failover.
3. Read requests are very fast and can be scaled efficiently.
Cons
1. It provides large performance overhead because assumes that all transactions will be committed
to at least 3 servers. If you have massive write requests, it may result in performance
degradation even for read queries.
2. It is only supported for MariaDB and Percona XtraDB, but not supported for MySQL database.
Thanks for reading! If you have any thoughts or questions, leave a comment.
https://dzone.com/articles/pros-and-cons-of-mysql-replication-types
==================================================

One More…
Master-master vs master-slave database
architecture?
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
1. Master-Slave Replication
2. Master-Master Replication
3. 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

7.  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

8.  See known limitations
You can visit for my Blog full breakdown including architecture diagrams that goes into further
details about the 3 mentioned architectures.

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.
https://newbedev.com/master-master-vs-master-slave-database-architecture