What is new with MySQL 8.0 Delayed Replication ?

Delayed Replication – You can deliberately execute transactions later than the master by a specific duration of time , Why you do that and for what ? Consider this, Accidentally someone did a wrong UPDATE / DELETE in the master and the transaction is committed, Now how can DBA rollback the database system to the last known good condition ? This is when we benefit from MySQL delayed slave replication investment. The default replication delay in MySQL is “0” seconds, To delay the slave by N seconds use the CHANGE MASTER TO MASTER_DELAY = N, The transactions received from the master is not executed until N seconds later than it’s commit on the immediate master. We have blogged here how to setup delayed slave replication in MySQL. In this blog post we have explained how MySQL 8.0 advanced Delayed Slave Replication features.

MySQL 8.0 and Delayed Slave Replication

In MySQL 8.0 the delayed replication is controlled by two system variables on timestamps – orginal_commit_timestamp and immediate_commit_timestamp ,  They depend on GTID of each transaction (instead of each event like in MySQL 5.7) written to the binary log.These two system variables are applicable only when your entire replication infrastructure is on MySQL 8.0.1 or above , If either Master or slave is not using these timestamps, then delayed replication from MySQL 5.7 is used. 

• original_commit_timestamp: The total number of microseconds since epoch when the transaction was written (committed) to the binary log of the original master.
• immediate_commit_timestamp: The total number of microseconds since epoch when the transaction was written (committed) to the binary log of the immediate master / slave.

The orginal_commit_timestamp will be always same on all replication when the transaction is applied. In a typical master-slave replication, the original_commit_timestamp of a transaction in the (original) master’s binary log is always the same as its immediate_commit_timestamp. In the slave’s relay log, the original_commit_timestamp and immediate_commit_timestamp of the transaction are the same as in the master’s binary log; whereas in its own binary log, the transaction’s immediate_commit_timestamp corresponds to when the slave committed the transaction.

Monitoring the Delayed Slave Replication

We strongly recommend following Performance Schema tables to monitor the replication delay (lag):

• replication_connection_status: The current status of connections to the master, This data dictionary table provides information on the last and current transaction the connection thread queued into the relay log.
• replication_applier_status_by_coordinator: The current status of the coordinator thread that only displays information when using a multithreaded slave, This data dictionary table also provides information on the last transaction buffered by the coordinator thread to a worker’s queue, as well as the transaction it is currently buffering.
• replication_applier_status_by_worker: The current status of the thread(s) applying transactions received from the master and it also provides information about the transactions applied by the applier thread, or by each worker when using a multithreaded slave.

The following two matrices from the output of SHOW SLAVE STATUS is also helpful to monitor Delayed Replication:

SQL_Delay – This is measured in seconds of replication delay which configured using CHANGE MASTER TO MASTER_DELAY=N

SQL_Remaining_Delay – This shows total number seconds left of the delay configured intentionally , i.e. Slave_SQL_Running_State is Waiting for MASTER_DELAY seconds