DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
  1. DZone
  2. Data Engineering
  3. Databases
  4. Pros and Cons of MySQL Replication Types

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.

Matthieu Robin user avatar by
Matthieu Robin
·
Feb. 06, 19 · Opinion
Like (5)
Save
Tweet
Share
42.74K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Replication (computing) MySQL Database master Cons clustering

Published at DZone with permission of Matthieu Robin. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • The 5 Books You Absolutely Must Read as an Engineering Manager
  • Fargate vs. Lambda: The Battle of the Future
  • HTTP vs Messaging for Microservices Communications
  • Reliability Is Slowing You Down

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: