Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.2Mb
PDF (A4) - 31.2Mb
PDF (RPM) - 29.5Mb
HTML Download (TGZ) - 7.3Mb
HTML Download (Zip) - 7.3Mb
HTML Download (RPM) - 6.2Mb
Man Pages (TGZ) - 180.3Kb
Man Pages (Zip) - 290.3Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  Semisynchronous Replication

17.3.9 Semisynchronous Replication

In addition to the built-in asynchronous replication, MySQL 5.6 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 must 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, 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.

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.

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 after the commit is done 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 after having performed a commit), 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.

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 after committing each such statement, just as it does for explicit transaction commits.