"20180608" MySQL5.7 new Table mysql.gtid_executed

MySQL5.7 added table mysql.gtid_executedmysql5.6 master and slave, get gtid_executed from library

At the time of MySQL5.6, master-slave replication opens Gtid, and when slave executes show slave status \g, it can get the collection information of the Gtid that is currently executing. In MySQL5.6, this value is not persisted to a file or database table, but is read into memory every time the slave is restarted from the end of the last Binlog file. Then this will cause a problem, in the library does not open log_slave_updates this parameter, or the maintenance personnel accidentally removed the Binlog file, then after the slave restart will not get gtid_executed value, Then the master-slave environment will be hung up.

It is also important to note that even if we turn on the log_slave_updates parameter and write all the transactions from master to slave local Binlog when it is played locally, the disk IO and disk space resources will inevitably be wasted.

MySQL5.7 the new mysql.gtid_executed

In MySQL5.7, the gtid_executed information is persisted to the table, which is the mysql.gtid_executed table:

[email protected] 11:03:  [(none)]> show create table mysql.gtid_executed \G*************************** 1. row ***************************       Table: gtid_executedCreate Table: CREATE TABLE `gtid_executed` (  `source_uuid` char(36) NOT NULL COMMENT ‘uuid of the source where the transaction was originally executed.‘,  `interval_start` bigint(20) NOT NULL COMMENT ‘First number of interval.‘,  `interval_end` bigint(20) NOT NULL COMMENT ‘Last number of interval.‘,  PRIMARY KEY (`source_uuid`,`interval_start`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 STATS_PERSISTENT=0

Execute SQL to see some specific information about this table:

[email protected] 11:04:  [(none)]> select * from mysql.gtid_executed ;+--------------------------------------+----------------+--------------+| source_uuid                          | interval_start | interval_end |+--------------------------------------+----------------+--------------+| 4725234c-1acc-11e8-9ff9-000c29a80f41 |              1 |          781 || 4725234c-1acc-11e8-9ff9-000c29a80f41 |            989 |         1011 || 4cf4a087-1ba3-11e8-92f6-000c2920ceb4 |              1 |         2239 |+--------------------------------------+----------------+--------------+3 rows in set (0.00 sec)

After you add a mysql.gtid_executed table, you can get information directly from this table after the slave restart. It is important to note that mysql.gtid_executed is updated on both the master and slave servers, and the table Slave_relay_log_info is only updated from the server.

You can see that the above table mysql.gtid_executed does not record the value of the Gtid for each transaction, but instead records the starting and ending values of the Gtid, because in order to avoid the record madness of this table grows, So MySQL5.7 introduces a new process for specialized compression, and has special parameters to set this compression ratio.

[email protected] 11:58: [(None)]> Select Thread_id,thread_os_id,name,processlist_command,processlist_ State from Performance_schema.threads where name is like '%compress% '; +-----------+--------------+--------------------- -----------+---------------------+-------------------+| thread_id | thread_os_id | name | Processlist_command | Processlist_state |+-----------+--------------+--------------------------------+---------------------+---------        ----------+|        27 | 23839 | thread/sql/compress_gtid_table | Daemon | Suspending |+-----------+--------------+--------------------------------+---------------------+----------------- --+1 row in Set (0.00 sec) [email protected] 11:59: [(None)]> show global variables like ' Gtid_executed_compressio N_period '; +----------------------------------+-------+| variable_name | Value |+----------------------------------+-------+| Gtid_executed_compression_period | 1000 |+----------------------------------+-------+1 row in Set (0.01 sec) [email protected] 11:59: [(None)]> 

There is one more thing to note: The way mysql.gtid_executed is recorded is very much related to whether the log binary logs are opened from the library. If slave turns on the binary log, the table will only be updated when the binary log is cut or MySQL shuts down normally, otherwise it will be updated in real time. It is also important to note that the Gtid information is not logged to the table when MySQL is closed unexpectedly, and is written to this table from Binlog when MySQL resumes.

When the Binlog is turned on, and the Exectued_gtids_compression_period value is not used, the binlog rotation of MySQL will also cause automatic compression of the mysql.gtid_executed table.

"20180608" MySQL5.7 new Table mysql.gtid_executed