Pros and Cons of 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:

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

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