2

In the official doc

https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication.html It mentioned that if synchronous replication is required, use NDB instead

For scenarios where synchronous replication is required, use NDB Cluster (see MySQL NDB Cluster 7.5 and NDB Cluster 7.6).

Question 1. Does mysql not support synchronous replication, only mysql cluster support synchronous replication?

Question 2. Where is the doc for synchronous replciation for NDB I see multiple post mentioning that NDB replication is synchronous by default https://stackoverflow.com/questions/53149674/can-i-implement-synchronous-and-asynchronous-replication-with-the-mysql-cluster

But the official doc only mention asynchronous and semisynchronous replication https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-replication.html

NDB Cluster supports asynchronous replication, more usually referred to simply as “replication”. This section explains how to set up and manage a configuration in which one group of computers operating as an NDB Cluster replicates to a second computer or group of computers. We assume some familiarity on the part of the reader with standard MySQL replication as discussed elsewhere in this Manual. (See Chapter 19, Replication).

Question 3: If mysql or NDB supports synchronous replication, do they use 2PC? What happens during network partition, or replica nodes are not available? Does NDB sacrifice availability over consistency? Does NDB do leadership election?

This post say it does https://dev.mysql.com/blog-archive/2-phase-commit-in-ndbcluster/

But I can't find documentation on the behavior of the trade off on availability vs consistency during network partition or replica failure?

1 Answer 1

4

MySQL does not support synchronous replication. There will always be a delay because changes are not written to the binary log until you commit a transaction, then the replica must download the binary log and apply the changes on its instance.

The client that made changes on the primary instance does not wait for the same changes to be applied on the replica.

In the case of semi-synchronous replication, the client making changes on the primary waits on COMMIT until at least one replica has downloaded the respective binary log events — but the client still does not wait for those binary log events to be applied. It's enough assurance of redundancy to know that the replica has received the binary log.

This is still really asynchronous, in the sense that the change is not immediately available on the replica once the COMMIT has finished on the source.

NDB Cluster is no different with respect to replication. When copying data from one cluster to a remote cluster, it works via the binary log just as with any other MySQL storage engine, and it is always asynchronous (or at best semi-synchronous).

The documentation you saw that recommends "for scenarios where synchronous replication is required, use NDB Cluster" is misleading. NDB Cluster is not a replication solution, it's a storage engine.

What the writer of that documentation might have meant is that if your goal is to ensure that data is stored in more than one place, to protect against data loss in case of a storage failure, then NDB Cluster can be a solution because storing any data change copies the data synchronously to two fragment replicas. The fragment replicas are all in the same MySQL Server instance. No binary log is involved.

This is unfortunately overloading the term "replica" and that may be confusing. An NDB Cluster fragment replica is not a MySQL Server replica.

The NDB storage engine writes changes to two (or more) nodes to achieve redundancy. This does use two-phase commit, but it is handled internally in the NDB storage engine, not as part of SQL transaction semantics.

If a node of an NDB Cluster is unresponsive or a network partition occurs that make the node unreachable from the management node, the Cluster disables that node. I suggest you read about the arbitrator in an NDB Cluster.


Re your comment:

so read after write in read replica can possibly read stale data before binlog is applied in read replica

Yes, that's correct. You can't rely on the replica instance having applied binlog events immediately after COMMIT on the source instance. There is always a delay, even if it is small.

You can measure the delay (sometimes called replication lag) in various ways, which I assume you're aware of.

You can also read from a replica using a blocking function SOURCE_POS_WAIT(), so you are sure it has "caught up" i.e. applied at least up to a specific binlog position. If you know what the binlog position was on the source when you executed a given update, this allows you to wait until the replica's data includes that change.

Another definition of consistency (another overloaded term with multiple meanings) is that all constraints are satisfied before and after any given transaction. MySQL does support this by default. I.e. all transactions are committed atomically on a replica, even if there is a delay.

6
  • Does this mean, strong consistency is not achievable in mysql OR mysql cluster even with single master multiple read replica server? Because the definition of consistency is linearizability (immediate read after write) and mysql 1. does not have majority synchronous replication (only 1 read replica synchronous replication under semisynchronous replication) 2. synchronous replication in mysql does not mean commit in read replica, but the receiving of binlog, so read after write in read replica can possibly read stale data before binlog is applied in read replica
    – olaf
    Feb 17 at 22:21
  • One more follow up question , do you happen to know if there is a full synchronous solution available for usecase that need backup without possibility of dataloss? Is DRBD + mysql a viable solution? If yes, how does DBRD communicate with mysql to block commit until replication is successful?
    – olaf
    Feb 18 at 19:04
  • 1
    DRBD is block-level replication, not transaction-level replication. It does not communicate with MySQL. All I/O writes on the primary are synchronously written to storage devices on both the primary and replica servers. This is how Amazon RDS multi-AZ instances work, for example. Feb 18 at 20:28
  • 1
    Block-level replication is independent from transaction commits. MySQL does I/O incrementally on commit, but also before commit and after commit. If you use DRBD and need to fail over and start up MySQL Server on the replica server, then it will do crash recovery just like if you suddenly reboot a source server. It will recover the committed transactions and discard the uncommitted transactions. Feb 18 at 20:42
  • 1
    MySQL does fsync to the InnoDB redo log on commit. This is I/O, and thus DRBD synchronously replicates it to the replica server as it would any other I/O. The modified pages of data in the buffer pool may not yet be saved to disk on the source instance. But as with any other change, it can be reconstructed from the redo log if there is a crash. The redo log has the information needed to recover any unflushed pages. Feb 18 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.