Say GTID-the system variable related to GTID

Gtid_next

When the default transaction commits, MySQL generates a GTID for it. When the transaction is played back on the Slave node, the GTID generated by the transaction on the Master node is used, which is related to a session-level system variable gtid_next.

If the value of gtid_next is AUTOMATIC (its default), when binary logging is enabled, the transaction commit results in a new GTID on both the Slave node and the Master node. However, if the gtid_next is assigned a valid GTID, then the GTID is the GTID of the transaction that follows, and the GTID is added to the GTID collection gtid_executed. The playback thread on the Slave node formally takes advantage of this, assigns the GTID generated on the Master node to @ @ SESSION.gtid_next, and then saves the GTID intact on the Slave node.

Gtid_purged

The global system variable gtid_purged represents the GTID collection of all transactions that have been committed but cleared from the binary log.

The global system variable gtid_purged is a subset of gtid_executed, and the following situations can cause changes in the gtid_purged set:

Binary logging is disabled on the Slave node, and gtid_purged changes as the playback thread commits the copied transaction.

Gtid_purged changes when the binary log file is cleared.

Gtid_purged changes when @ @ GLOBAL.gtid_purged is assigned.

You can change the gtid_purged collection to indicate that the transaction has been committed, but it has been cleared from the binary log file. When you add a GTID to the gtid_purged collection, the GTID is also added to the gtid_executed collection. This idea is used to add a read-only instance through backup. In MySQL version 5. 7, gtid_purged can be set only if the gtid_executed collection is empty.

Each binary log file header records a Previous_gtids_log_event event, which represents the collection of GTID in all the binary log files before the binary log file (that is, the Previous_gtids_log_event in the N-th binary log file is equal to the Previous_gtids_log_event in the N-1 binary log file, and the union of all Gtid_log_event in the N-1 file).

The two global system variables, gtid_executed and gtid_purged, are initialized with Previous_gtids_log_event and Gtid_log_event in the oldest and latest binary log files when MySQL starts.

The procedure for initializing the gtid_executed collection is as follows:

Gtid_executed = Previous_gtids_log_event in the latest binary log file + all Gtid_log_event in the latest binary log file + GTID in the system table mysql.gtid_executed (but the GTID collection does not contain the executing GTID, that is, @ @ GLOBAL.gtid_owned).

The idea that the gtid_purged collection is initialized is as follows:

The first equation:

The union of the GTID in the existing (existing) binary log file and the GTID in the purged binary log file, namely gtids_in_binlog.

Gtids_in_binlog = Previous_gtids_log_event in the latest binary log file + all Gtid_log_event in the latest binary log file

The second equation:

The GTID in the existing (existing) binary log file, namely gtids_in_binlog_not_purged.

Gtids_in_binlog_not_purged = gtids_in_binlog-Previous_gtids_log_event in the oldest binary log file

The third equation:

GTID, gtid_purged, which once appeared, but no longer exists in the binary log file.

Gtid_purged = gtid_executed-gtids_in_binlog_not_purged

Through these three equations, we can get:

Gtid_purged = gtid_executed-gtids_in_binlog_not_purged = Previous_gtids_log_event in the latest binary log file + all Gtid_log_event in the latest binary log file + GTID in the system table mysql.gtid_executed-((Previous_gtids_log_event in the latest binary log file + all Gtid_log_event in the latest binary log file)-oldest binary Previous_gtids_log_event in the binary log file)

That is, gtid_purged = GTID in system table mysql.gtid_executed + Previous_gtids_log_event in the oldest binary log file

One of the system parameters related to the initialization of the above two global system variables is binlog_gtid_simple_recovery. If the value is FALSE, when initializing these two variables, MySQL traverses all binary log files, not just the oldest and latest binary log files, which may take a long time.