Over a million developers have joined DZone.

MySQL Replication Options and Their Challenges

DZone's Guide to

MySQL Replication Options and Their Challenges

Each of the different MySQL replication options, MySQL native and otherwise, come with their associated caveats. Here are five of them.

· Database Zone ·
Free Resource

Download the Altoros NoSQL Performance Benchmark 2018. Compare top NoSQL solutions – Couchbase Server v5.5, MongoDB v3.6, and DataStax Enterprise v6 (Cassandra).

Previously we discussed replication for MySQL systems. In this post, we will discuss various MySQL replication options.

For MySQL, replication is the primary go-to strategy for high availability (HA) and, to some extent, scale. By providing additional copies of the primary database, a MySQL system using replication can withstand the loss of the master database by promoting the slave to be the new master and updating the application endpoints. In addition, replication can provide scale by providing additional read-only copies of the database for the application to leverage. This offloads application read requests from the master, allowing the master to focus more on writes.

This makes replication seem like a magic bullet. However, each of the different MySQL replication options, MySQL native and otherwise, come with their associated caveats, namely the following:

  • What level of HA do the various MySQL replication options provide?
  • What level of scale do the various MySQL replication options provide?

And the biggest question:

  • How much latency does each of the MySQL replication options create?

In other words, if the application reads from a replication slave or secondary master, what’s the level of consistency between data on the primary master, and data on the replication slave or secondary master? As a practical example of how data consistency matters, imagine you’ve set up your e-commerce site catalog to be on one MySQL server, and your checkouts to be on a separate server (this is actually done with Magento 2). Correspondingly, there can be latency between the two servers due to replication lag. This means that item descriptions or attribute dependencies that get updated on the catalog server might not be immediately reflected in active sessions on the checkout server or the inventory server. This can affect what items are actually available-to-promise, and create disparities with inventory. This will affect checkouts, either causing delays in checkout or potential misorders, which have to be handled by customer service.

There are five main replication options used with MySQL workloads.

1. MySQL Asynchronous Replication

MySQL Asynchronous Replication is the default replication used by many deployments. Asynchronous means the master will COMMIT regardless if the slave has processed the binlog entry. Thus, there will always be some number of transactions with COMMIT on master that aren’t yet aren’t represented on the slave. The result is a trade of slave latency for throughput. HA and read scale are provided as long as some level of data consistency between master and slave is tolerable to the application. In other words, if/when the master goes offline, the slave that is promoted to master will likely have some transactions missing. Similarly, as in the e-commerce example above, applications can leverage read slaves for read scale if the applications can tolerate some degree of data inconsistency.

2. MySQL Semi-Synchronous Replication

MySQL Semi-Synchronous Replication provides more guarantees of high availability in exchange for lesser performance. Semi-synchronous means the master will block after COMMIT until at least one semi-sync slave confirms it’s received the transaction’s details and flushed its relay log to disk. That means there’s always a durable copy of the master’s transaction information available on the slave but doesn’t guarantee that it has been processed yet. Thus, there may be some number (less than asynchronous replication) of transactions which have COMMIT on master aren’t yet represented on the slave. The result is a higher level of HA. As long as the slave is allowed to complete the relay log before being promoted to master, there will be no transactions lost. However, this represents some level of delay before the newly promoted master can resume the production workload.

3. Certification Replication (Codership’s Galera)

Certification Replication (Codership’s Galera) creates a multi-master topology, with each master able to accept write and read transactions. When any master processes a transaction, transaction details are transmitted to all cluster nodes as a certificate. Next, all the other cluster nodes must confirm that the certificate is valid. The local master commits before application ACK and the other masters COMMIT in the background. The result is strong consistency between nodes as long as only a single master accepts writes. This also results in full HA, as each master contains a full, consistent copy of the data. However, if writes are accepted to more than a single master, performance can be significantly impacted, to the extent Codership recommends applications write to a single master. Correspondingly, write scale with certification replication works best as a consolidated workload, i.e. multiple applications writing to a single master each rather than a single application scaling out writes to multiple masters.

4. MySQL Group Replication

MySQL Group Replication is very similar to certification replication. It’s a multi-master topology, with each master able to accept write and read transactions. When any master processes a transaction, the transaction information is transmitted to all cluster nodes. A quorum of other nodes must confirm the transaction is valid. Then the local master commits before application ACK, while the other masters COMMIT in background. The result is strong consistency for the clustered masters in quorum as long as only a single master accepts writes. HA is strong for all nodes in quorum. And again, similar to certification replication, MySQL group replication provides read scale similar to read slaves but does not scale out writes.

5. ClustrixDB Data Replication (Synchronous)

ClustrixDB Data Replication (Synchronous) is very different than the other MySQL replication options. To be very clear, ClustrixDB does not use MySQL Replication between its nodes. ClustrixDB is MySQL compatible, clustered relational scale-out database. Every ClustrixDB node is in identical, global transactional state at all times. All nodes (at least two) containing data associated with the transaction are durably updated before application receives ACK. The result is strong consistency (including durable WAL-flush) for all cluster nodes in quorum per transaction. HA is strongly consistent at all times, and the number of total data copies is configurable via nResilience. And due to automatic fine-grained data distribution and query fanout, ClustrixDB linearly scales out both write and read transactions with each node added to the cluster.

Summary of MySQL Replication Options

In summary, ClustrixDB uses synchronous communication to ensure all participating nodes actually complete and durably log the local transaction(s) before the application is informed of the COMMIT. There is no possibility of skew between transactional state between the nodes.

In comparison, MySQL replication is usually an asynchronous process by which a slave RDBMS consumes the changes made on the master RDBMS. This results in skew between the master and slave. Even with Galera’s “certification-based replication,” there is still skew between master and the slave. Replication is designed to provide multiple full copies of the RDBMS for fault tolerance, with the trade off of some skew.

Download the whitepaper, Moving From Relational to NoSQL: How to Get Started. We’ll take you step by step through your first NoSQL project.

database ,mysql ,mysql replication ,data replication ,replcation

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}