How Primary/Standby Replication Works

RDS for MySQL standby instances, read replicas, self-built standby databases, and DR instances established through DRS all use MySQL primary/standby (also called "master/slave") replication. This section describes how MySQL Primary/Standby Replication works.

Primary/Standby Replication Process

Click to enlarge

When data is updated on the master node, the update is written to the binlog of the master node as an event. The event types include INSERT, DELETE, UPDATE, and QUERY.
When slave nodes are connected to the master node, a binlog dump thread is created for each slave node.
When the binlog of the master node changes, the binlog dump threads notify all the slave nodes of the change and push the binlog updates to the slave nodes.
After receiving the binlog updates, the I/O thread of each slave node writes the updates to the local relay log.
The SQL thread of each slave node reads the relay log and replays the operation (such as DML and DDL) based on the event in the relay log.

Seconds_Behind_Master Calculation Method

Seconds_Behind_Master indicates the primary/standby replication delay, which can be obtained by running show slave status. The following pseudocode shows how the Seconds_Behind_Master value is calculated.

if (SQL thread is running) 
//If the SQL thread is started
{
     if (SQL thread processed all the available relay log)
     //If the binlog pulled by the I/O thread from the master node is the same as that read by the SQL thread from the relay log
     {
         if (IO thread is running)
             //If the I/O thread is started, Seconds_Behind_Master is set to 0.
             print 0;
         else
             //If the I/O thread is not started, Seconds_Behind_Master is set to NULL.
             print NULL;
     }
     else
         //If the SQL thread did not process all events written by the I/O thread, the value of Seconds_Behind_Master needs to be calculated.
         Calculate the value of Seconds_Behind_Master using the formula;
}  
else
     //If the SQL thread is not started, Seconds_Behind_Master is set to NULL.
     print NULL;

In the pseudocode, the following formula is used to calculate the value of Seconds_Behind_Master:

Seconds_Behind_Master = time(0) - last_master_timestamp - clock_diff_with_master

Explanations of the variables:

time(0): The system time of the current slave server.
clock_diff_with_master: The difference between the system time of the slave server and that of the master server. Generally, the value is 0. If the system time of the slave server is different from that of the master server, the calculated value of Seconds_Behind_Master is inaccurate.
last_master_timestamp: The execution time of an event on the master node that is updated by the SQL thread. This variable is calculated and updated when the slave node replays the event in the relay log. The update time in the multi-threaded slave (MTS) replication is different from that in single-threaded replication. MTS is enabled by default.
- MTS: The SQL thread of the slave node updates the value of last_master_timestamp after each transaction is executed. The update is performed by transaction. Therefore, large transactions and DDL operations may cause a long primary/standby replication delay. For details, see Automatic Recovery of Extended Primary/Standby Replication Delay.
- Single-threaded replication: After the SQL thread of the slave node reads a transaction from the relay log, last_master_timestamp is updated before the transaction is executed. The update is performed by transaction.

The formula for calculating Seconds_Behind_Master can be understood as follows:

Seconds_Behind_Master = System time of the current slave server – Execution time of the transaction on the master node that is updated by the SQL thread – Difference between the system time of the slave server and that of the master server

Parent topic: Primary/Standby Replication Issues

Previous topic: Primary/Standby Replication Issues

Next topic: Automatic Recovery of Extended Primary/Standby Replication Delay