Description of MySQL Primary/Standby Replication
MySQL replication is based on the replication source server track of all changes (additions, deletions, modifications, etc.) to its database in its binary log (binlog). The binlog serves as a written record of all events that modify the database structure or content (data) from the moment the server was started. Typically, the SELECT statements are not recorded because they modify neither database structure nor data.
Each copy that connects to the source requests a copy of the binlog. That is, it pulls the data from the source, rather than the source pushing data to the copy. The copy also executes the events in the binlog that it receives. This has the effect of repeating the original changes just as they were made on the source. Tables are created or their structures are modified. Data is inserted, deleted, and updated according to the changes that were originally made on the source.
Because each copy is independent, the replaying of the changes to the binlog from the source occurs independently on each copy that is connected to the source. In addition, because each copy only receives a copy of the binlog by requesting it from the source, the source can read and update the copy of the database at its own pace and can start and stop the replication process at will without affecting the ability to update to the latest database status on either the source or the copy.
Description of the Primary/Standby Replication Process:
When data is performing a DML operation in the Master node, the transaction will write the DML operation in the form of an event to the binlog of the master node according to the binlog format. DML operations include: insertion, deletion, update, etc.
When Slave nodes are connected to the Master node, a binlog dump thread is created for each standby node.
When the binlog of the Master node changes, the binlog dump threads notify the standby node (if there are multiple Slaves, all standby nodes will be notified) 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 contents included in the logs to the local relay log ("relay log" for short).
The SQL thread of the Slave node reads the relay log written by the I/O thread and generates corresponding DML statements according to the event content recorded in the relay log, and puts them back into the Slave to complete the entire primary/standby replication process.
Description of Main Source Codes:
/* The pseudo code to compute Seconds_Behind_Master: Source: sql/rpl_replica.cc if (SQL thread is running) //If the SQL thread is started { if (SQL thread processed all the available relay log) ///If the position when the binlog is pulled by the I/O thread from the Master is the same as that of the relay log applied by the SQL thread relative to the binlog of the Master { if (IO thread is running) //If the I/O thread is started, set the delay to 0 print 0; else //If the I/O thread is not started, set the delay to NULL print NULL; } else //If the SQL thread did not apply all events written by the I/O thread, the value of Seconds_Behind_Master needs to be calculated compute Seconds_Behind_Master; } else //If the SQL thread is not started, set the value of Seconds_Behind_Master to NULL print NULL; */ |
Calculation Formula for Seconds_Behind_Master: Seconds_Behind_Master = time(0) - last_master_timestamp - clock_diff_with_master.
Explanations of the Formula Variables:
1. time(0): The system time of the current Slave node server.
2. last_master_timestamp: The execution time of a transaction on the Master node during the SQL thread processing in the Slave node. This variable is calculated and updated when the Slave node replays the event in the relay log.
3. clock_diff_with_master: The difference between the system time of the Slave node and that of the Master node server. Generally, the value is 0. If the system time of the Master node is different from that of the Slave node, the calculated value of the Slave node replication delay is inaccurate.
To sum up: Seconds_Behind_Master = System time of the current Slave node server – Execution time of the transaction on the Master node during the SQL thread processing in the Slave node – Difference between the system time of the Slave node and that of the Master node server.