Shulou(Shulou.com)06/01 Report--

Address of the brief book:

Http://www.jianshu.com/p/1f4f9c07ce0b

The role of the mysql.gtid_ execute table and the changes to Previous gtid Event are described together because of the basis discussed in later articles. This part uses my own native binlog parsing tool infobin written in C language.

Download Baidu cloud disk as follows:

Http://pan.baidu.com/s/1jHIWUN0

1. Gtid event

Why describe what Gtid event is first? Because it will be used later, in fact, its core element is a shape such as:

31704d8a-da74-11e7-b6bf-52540a7d243:100009

A Gtid is at the beginning of the whole thing event, which is used to describe what the Gtid of this thing is. Of course, it also encapsulates last_commit/sequence_number in order to support MTS. So the event for viewing the complete thing of a single insert statement using the infobin tool includes the following:

> Gtid Event:Pos:234 (0Xea) N_pos:299 (0X12b) Time:1513135186 Event_size:65 (bytes) Gtid:31704d8a-da74-11e7-b6bf-52540a7d243:100009 last_committed=0 sequence_number=1-- > Query Event:Pos:299 (0X12b) N_Pos:371 (0X173) Time:1513135186 Event_size:72 (bytes) Exe_time:0 Use_db:test Statment (35b-trun): BEGIN / *! Trx beginstones / Gno:100009---- > Map Event:Pos371 (0X173) N_pos 415 (0X19f) Time:1513135186 Event_size:44 (bytes) TABLE_ID:108 DB_NAME:test TABLE_NAME:a Gno:100009- > Insert Event:Pos:415 (0X19f) N_pos:455 (0X1c7) Time:1513135186 Event_size:40 (bytes) Dml on table: test.a table_id:108 Gno:100009 > Xid Event:Pos:455 (0X1c7) N_Pos:486 (0X1e6) Time:1513135186 Event_size:31 (bytes) COMMIT / *! Trx end*/ Gno:100009

Of course, you can also use mysqlbinlog for analysis, but the format is slightly less friendly.

II. The function of gtid_ execute table

This part is the key point in the key point, and it is also what I have been puzzling before. Please read it carefully.

The official document describes the gtid_ executed table as follows

Beginning with MySQL 5.7.5, GTIDs are stored in a table named gtid_executed, in the mysqldatabase. A row in this table contains, for each GTID or set of GTIDs that it represents, the UUID of theoriginating server, and the starting and ending transaction IDs of the set; for a row referencing only asingle GTID, these last two values are the same.

That is to say, the gtid_ executed table is a tool for Gtid persistence. As described earlier, the get_executed_gtids/get_lost_gtids/get_gtids_only_in_table/get_previous_gtids_logged in Gtid_state is stored in memory, so it needs to be initialized after the database restart, so it needs to read the media of Gtid persistence. We can find that gtid_executed is an innodb table. The table statement is as follows And we can change it manually, but don't do this:

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=utf8 STATS_PERSISTENT=0

So isn't it okay to have no gtid_executed table before 5.7.5? In fact, in addition to the gtid_ executed table, we also have a medium for Gtid persistence, which is Gtid event in binlog. So summarize the Gtid persistence media:

Gtid event in gtid_executed table binlog

So why do you need the gtid_ executed table when you have binlog's gtid event for persistence? This is actually an optimization after 5.7.5. In turn, we can consider that if we use Gtid as the slave library in 5.6, if the slave library does not open binlog and set log_slave_updates=ture at the same time, then there is no way to persist the Gtid things that have been executed from the library. Let's take a paragraph of 5.6 official documentation for one of the steps in building a Gtid slave library:

Step 3: Restart both servers with GTIDs enabled. To enable binary logging with globaltransaction identifiers, each server must be started with GTID mode, binary logging, slave updatelogging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition,you should prevent unwanted or accidental updates from being performed on either server by startingboth in read-only mode. This means that both servers must be started with (at least) the options shownin the following invocation of mysqld_safe:shell > mysqld_safe-gtid_mode=ON-log-bin-log-slave-updates-enforce-gtid-consistency &

Enabling binlog and setting log_slave_updates=ture will inevitably cause a problem. In fact, most of the time we do not need to cascade slave from the library, setting log_slave_updates=ture will cause additional space and performance overhead. Naturally, in this case, we need another Gtid persistence medium, not Gtid event in binlog. In order to solve this problem, the gtid_executed table in 5.7came into being. However, does the gtid_ executed table need to be updated in real time? Obviously, when binlog is not enabled on the slave side or binlog is enabled and log_slave_updates=ture is not set, it needs to be updated in real time, because the Gtid executed by thread must be persisted, and it does not need to be updated in real time on the main database because of the existence of binlog's Gtid event. This different treatment can also reduce the burden and improve performance.

At the same time, there is also a relevant description in the official document, which is divided into whether to start the binlog description, but the description is not the most detailed. So I will describe this part in detail later.

III. Changes in Previous gtid Event

A Previous gtid Event is a collection (including deleted binlog) that is included at the beginning of each binlog to describe all the Gtid contained in all previous Gtid, such as:

Da267088-9c22-11e7-ab56-5254008768e3:1-32

If Gtid is not enabled in 5.6, then binlog will not include this Previous gtid Event, but it will also include this Previous gtid Event if it is not enabled in 5.7. in fact, the change in this point is also of great significance. To put it simply, it provides the basis for quickly scanning binlog (binlog_gtid_simple_recovery=ture) to get the correct Gtid collection, otherwise it will scan a large number of binlog, thus wasting IPrevious gtid Event O performance. This is a very serious problem in 5.6, as described in the official document 5.7:

When binlog_gtid_simple_recovery=TRUE, which is the default in MySQL 5.7.7 andlater, the server iterates only the oldest and the newest binary log files and the values ofgtid_purged and gtid_executed are computed based only on Previous_gtids_log_eventor Gtid_log_event found in these files. This ensures only two binary log files are iterated duringserver restart or when binary logs are being purged

Of course, this part will also be described in detail later, here is just a brief mention. So let's confirm this through the mysqlbinlog and infobin tools, respectively.

5.6. 26 do not enable Gtid

Mysqlbinlog:

*! 50530 SET @ @ SESSION.PSEUDOVLAVEMODEX 1 / SET 50003 @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / * / # at 4 "171211 16:20:10 server id 20155 end_log_pos 120 CRC32 0x12617db7 Start: binlog v 4, server v 5.6.26-74.0-log created 171211 16 end_log_pos 20 CRC32 0x696752cb Query thread_id=30 1" Warning: this binlog is either in use or was not closed properly.# at 120 "171211 16:20:14 server id 20155 end_log_pos 192 CRC32 0x696752cb Query thread_id=30

Infobin:

-Detail now- > Format description log Event:Pos:4 (0X4) N_pos:120 (0X78) Time:1512980410 Event_size:116 (bytes)-- > Query Event:Pos:120 (0X78) N_Pos:192 (0Xc0) Time:1512980414 Event_size:72 (bytes) Exe_time:0 Use_db:test Statment (35b-trun): BEGIN / *! Trx beginstones / Gno:0-- Map Event:Pos192 (0Xc0) N_pos:241 (0Xf1) Time:1512980414 Event_size:49 (bytes) TABLE_ID:91 DB_NAME:test TABLE_NAME:testpo Gno:0- > Insert Event:Pos:241 (0Xf1) N_pos:281 (0X119) Time:1512980414 Event_size:40 (bytes) Dml on table: test.testpo table_id:91 Gno:0 > Xid Event:Pos:281 (0X119) N_Pos:312 (0X138) Time:1512980414 Event_size:31 (bytes) COMMIT / *! Trx end*/ Gno:0

We did not find Previous gtid Event, which is 5.6. if Gtid is not enabled, it does not contain Previous gtid Event.

5.7.14

Mysqlbinlog:

/ *! 50530 SET @ @ SESSION.PSEUDONG SLAVEMODEP / 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / * / # at 4 "171211 16:26:49 server id 1 end_log_pos 123 CRC32 0xf9a36298 Start: binlog v 4, server v 5.7.14-7-debug-log created 171211 1626 26 CRC32 0xf9a36298 Start 4" Warning: this binlog is either in use or was not closed properly.# at 123 "171211 16:26:49 server id 1 end_log_pos 194 CRC32 0x5865633f * * Previous-GTIDs**# da267088-9c22-11e7-ab56-5254008768e3:1-3 hours at 194

Infobin:

-Detail now- > Format description log Event:Pos:4 (0X4) N_pos:123 (0X7b) Time:1512980809 Event_size:119 (bytes) > Previous gtid Event:Pos:123 (0X7b) N_pos:194 (0Xc2) Time:1512980809 Event_size:71 (bytes) > Anonymous gtid Event:Pos:194 (0Xc2) N_pos:259 (0X103) Time:1512980814 Event_size:65 (bytes) Gtid:Anonymous ( Gno=0) last_committed=0 sequence_number=1-- > Query Event:Pos:259 (0X103) N_Pos:331 (0X14b) Time:1512980814 Event_size:72 (bytes) Exe_time:0 Use_db:test Statment (35b-trun): BEGIN / *! Trx beginnings / Gno:0---- > Map Event:Pos331 (0X14b) N_pos:380 (0X17c) Time:1512980814 Event_size:49 (bytes) TABLE_ID:154 DB_NAME:test TABLE_NAME:testpo Gno:0- -> Insert Event:Pos:380 (0X17c) N_pos:420 (0X1a4) Time:1512980814 Event_size:40 (bytes) Dml on table: test.testpo table_id:154 Gno:0 > Xid Event:Pos:420 (0X1a4) N_Pos:451 (0X1c3) Time:1512980814 Event_size:31 (bytes) COMMIT / *! Trx end*/ Gno:0

We clearly see that Previous gtid Event is included here, and of course we also find that Anonymous gtid Event is also a change in 5.7. even if we don't start Gtid, everything contains an Anonymous gtid Event, although there is no Gtid, but it still contains last_committed/sequence_number.

IV. Summary of this section

At the end of this section, you can at least learn:

1. What is Gtid event. What important elements it contains. 2. Why the gtid_ execute table is needed and its function. 3. What changes have taken place in Previous gtid Event. 4. Simply understand what the change in Previous gtid Event means.

Author Wechat:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.