17.4.9 Semisynchronous Replication

In addition to the built-in asynchronous replication, MySQL
8.0 supports an interface to semisynchronous
replication that is implemented by plugins. This section discusses
what semisynchronous replication is and how it works. The
following sections cover the administrative interface to
semisynchronous replication and how to install, configure, and
monitor it.

MySQL replication by default is asynchronous. The master writes
events to its binary log and slaves request them when they are
ready. The master does not know whether or when a slave has
retrieved and processed the transactions, and there is no
guarantee that any event will ever reach any slave. With
asynchronous replication, if the master crashes, transactions that
it has committed might not have been transmitted to any slave.
Failover from master to slave in this case might result in
failover to a server that is missing transactions relative to the
master.

With fully synchronous replication, when a master commits a
transaction, all slaves will also have committed the transaction
before the master returns to the session that performed the
transaction. Fully synchronous replication means failover from the
master to any slave is possible at any time. The drawback of fully
synchronous replication is that there might be a lot of delay to
complete a transaction.

Semisynchronous replication falls between asynchronous and fully
synchronous replication. The master waits until at least one slave
has received and logged the events (the required number of slaves
is configurable), and then commits the transaction. The master
does not wait for all slaves to acknowledge receipt, and it
requires only an acknowledgement from the slaves, not that the
events have been fully executed and committed on the slave side.
Semisynchronous replication therefore guarantees that if the
master crashes, all the transactions that it has committed have
been transmitted to at least one slave.

Compared to asynchronous replication, semisynchronous replication
provides improved data integrity, because when a commit returns
successfully, it is known that the data exists in at least two
places. Until a semisynchronous master receives acknowledgment
from the required number of slaves, the transaction is on hold and
not committed.

Compared to fully synchronous replication, semisynchronous
replication is faster, because it can be configured to balance
your requirements for data integrity (the number of slaves
acknowledging receipt of the transaction) with the speed of
commits, which are slower due to the need to wait for slaves.

Important

With semisynchronous replication, if the master crashes and a
failover to a slave is carried out, the failed master should not
be reused as the replication master, and should be discarded. It
could have transactions that were not acknowledged by any slave,
which were therefore not committed before the failover.

If your goal is to implement a fault-tolerant replication
topology where all the servers receive the same transactions in
the same order, and a server that crashes can rejoin the group
and be brought up to date automatically, you can use Group
Replication to achieve this. For information, see
Chapter 18, Group Replication.

The performance impact of semisynchronous replication compared to
asynchronous replication is the tradeoff for increased data
integrity. The amount of slowdown is at least the TCP/IP roundtrip
time to send the commit to the slave and wait for the
acknowledgment of receipt by the slave. This means that
semisynchronous replication works best for close servers
communicating over fast networks, and worst for distant servers
communicating over slow networks. Semisynchronous replication also
places a rate limit on busy sessions by constraining the speed at
which binary log events can be sent from master to slave. When one
user is too busy, this will slow it down, which can be useful in
some deployment situations.

Semisynchronous replication between a master and its slaves
operates as follows:

A slave indicates whether it is semisynchronous-capable when
it connects to the master.

If semisynchronous replication is enabled on the master side
and there is at least one semisynchronous slave, a thread that
performs a transaction commit on the master blocks and waits
until at least one semisynchronous slave acknowledges that it
has received all events for the transaction, or until a
timeout occurs.

The slave acknowledges receipt of a transaction's events only
after the events have been written to its relay log and
flushed to disk.

If a timeout occurs without any slave having acknowledged the
transaction, the master reverts to asynchronous replication.
When at least one semisynchronous slave catches up, the master
returns to semisynchronous replication.

Semisynchronous replication must be enabled on both the master
and slave sides. If semisynchronous replication is disabled on
the master, or enabled on the master but on no slaves, the
master uses asynchronous replication.

While the master is blocking (waiting for acknowledgment from a
slave), it does not return to the session that performed the
transaction. When the block ends, the master returns to the
session, which then can proceed to execute other statements. At
this point, the transaction has committed on the master side, and
receipt of its events has been acknowledged by at least one slave.
The number of slave acknowledgments the master must receive per
transaction before returning to the session is configurable using
the
rpl_semi_sync_master_wait_for_slave_count
system variable, for which the default value is 1.

Blocking also occurs after rollbacks that are written to the
binary log, which occurs when a transaction that modifies
nontransactional tables is rolled back. The rolled-back
transaction is logged even though it has no effect for
transactional tables because the modifications to the
nontransactional tables cannot be rolled back and must be sent to
slaves.

For statements that do not occur in transactional context (that
is, when no transaction has been started with
START
TRANSACTION or
SET autocommit =
0), autocommit is enabled and each statement commits
implicitly. With semisynchronous replication, the master blocks
for each such statement, just as it does for explicit transaction
commits.

The
rpl_semi_sync_master_wait_point
system variable controls the point at which a semisynchronous
replication master waits for slave acknowledgment of transaction
receipt before returning a status to the client that committed the
transaction. These values are permitted:

AFTER_SYNC (the default): The master writes
each transaction to its binary log and the slave, and syncs
the binary log to disk. The master waits for slave
acknowledgment of transaction receipt after the sync. Upon
receiving acknowledgment, the master commits the transaction
to the storage engine and returns a result to the client,
which then can proceed.

AFTER_COMMIT: The master writes each
transaction to its binary log and the slave, syncs the binary
log, and commits the transaction to the storage engine. The
master waits for slave acknowledgment of transaction receipt
after the commit. Upon receiving acknowledgment, the master
returns a result to the client, which then can proceed.

The replication characteristics of these settings differ as
follows:

With AFTER_SYNC, all clients see the
committed transaction at the same time, which is after it has
been acknowledged by the slave and committed to the storage
engine on the master. Thus, all clients see the same data on
the master.

In the event of master failure, all transactions committed on
the master have been replicated to the slave (saved to its
relay log). A crash of the master and failover to the slave is
lossless because the slave is up to date. As noted above, the
master should not be reused after the failover.

With AFTER_COMMIT, the client issuing the
transaction gets a return status only after the server commits
to the storage engine and receives slave acknowledgment. After
the commit and before slave acknowledgment, other clients can
see the committed transaction before the committing client.

If something goes wrong such that the slave does not process
the transaction, then in the event of a master crash and
failover to the slave, it is possible that such clients will
see a loss of data relative to what they saw on the master.