MySQL-----gtid_executed Detailed Original

MySQL version 5.6 Open Gtid mode, you must turn on the parameter log_slave_updates,

Simply put, a binary log must be recorded on the slave. This, regardless of the cost of performance or storage, will undoubtedly increase correspondingly

The MySQL 5.7 version began without the need to enable parameter Log_slave_updates in Gtid mode, the most important reason is that 5.7 introduced a new table gtid_executed under the MySQL library, the table structure is as follows:

mysql> SHOW CREATE TABLE mysql.gtid_executed\g

1. Row ***************************
table:gtid_executed
Create table:create Table ' gtid_executed ' (
' Source_uuid ' char (+) not NULL COMMENT ' uuid of the source where the transaction is originally executed. ',
' Interval_start ' bigint (a) Not NULL COMMENT ' first number of interval. ',
' Interval_end ' bigint (a) Not NULL COMMENT ' last number of interval. '
PRIMARY KEY (' Source_uuid ', ' Interval_start ')
) Engine=innodb DEFAULT charset=utf8mb4 stats_persistent=0
1 row in Set (0.00 sec)

In a nutshell, the table records the currently executing gtid. The column source corresponds to the UUID, and the column interval_start/interval_end represents the transaction number. The most important reason to configure the parameter Log_slave_updates in MySQL 5.6 is that when slave restarts, it is not possible to know the Gtid location where the current slave has been run, because the variable gtid_executed is a memory value:

Mysql> SELECT @ @global. gtid_executed\g
1. Row ***************************
@ @global. gtid_executed:7af7d3ea-933b-11e5-9da7-fa163e30f9a2:1-72054
1 row in Set (0.00 sec)

So MySQL 5.6 is handled by scanning the last binary log at startup to get the Gtid location information currently executing. Of course, if the DBA accidentally deletes the binary log, this can be a catastrophic problem.

Therefore, MySQL 5.7 will gtid_executed this value to persist. Using the same techniques as MySQL 5.6 handles SQL thread save location, the Gtid value is persisted in a InnoDB table and committed with the user transaction to achieve data consistency:

START TRANSACTION;

# User Statement

......

INSERT into mysql.gtid_executed VALUES (...)

END;

Unanimously do not understand what the above words mean, today in MYSQL 5.7 version of the Master.info relay-log.info and the corresponding binary files are deleted, the relay log file deleted, MYSQL restart, see slave1 show Master Status is affected,

Found unaffected, start slave normally executes from the original execution point, but in slave1 performing reset master, the mysql.gtid_executed table is emptied, stating that it has persisted, MySQL version 5.6 MY.CNF Log_ Slave_updates is used when MySQL restarts, because mysql.gtid_executed is not persisted in memory, the value of the reset variable will be emptied, and the MySQL 5.6 processing method is to scan the last binary log at startup to get the Gtid location information currently executed. The downside to doing this is to write a binary log more, increasing IO costs

If the binary log is enabled from the MySQL server, the update to table mysql.gtid_executed occurs only when the binary rotation is present, as a reboot can still be used to determine the current running Gtid location by scanning the binary log.

MySQL-----gtid_executed Detailed Original