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

The original level is limited.

Test version 5.7.14

Set up GTID_MODE=ON

ON (3): Both new and replicated transactions must be GTID transactions (GTID things are generated, and slave can only apply GTID things)

Set binlog format to row mode

Do the following

Mysql > insert into test values (1Jue 2)

Query OK, 1 row affected (0.01sec)

Mysql > insert into test values (2pr 3)

Query OK, 1 row affected (0.01sec)

Mysql > delete from test

Query OK, 2 rows affected (0.01sec)

Mysql > select * from test

Empty set (0.00 sec)

First of all, I found the binlog of this operation through my own tool infobin. If you want to get this tool, you can refer to the end of the article.

Briefly explain where Pos: is the current position corresponding to the mysqlbinlog # at 504 where the N_pos is the end position.

End_log_pos of mysqlbinlog

> Gtid Event:Pos:504 (0X1f8) N_pos:569 (0X239) Time:1496993578 Event_size:65 (bytes)

Gtid:89dfa8a4-cb13-11e6-b54-0c29a879a3:2

-- > Query Event:Pos:569 (0X239) N_Pos:641 (0X281) Time:1496993578 Event_size:72 (bytes)

Exe_time:0 Use_db:test Statment (35b-trun): BEGIN / *! Trx beginnings / Gno:2

-> Map Event:Pos641 (0X281) N_pos:689 (0X2b1) Time:1496993578 Event_size:48 (bytes)

TABLE_ID:142 DB_NAME:test TABLE_NAME:test Gno:2

-> Insert Event:Pos:689 (0X2b1) N_pos:733 (0X2dd) Time:1496993578 Event_size:44 (bytes)

Dml on table: test.test table_id:142 Gno:2

> Xid Event:Pos:733 (0X2dd) N_Pos:764 (0X2fc) Time:1496993578 Event_size:31 (bytes)

COMMIT; / *! Trx end*/ Gno:2-notice here that it ends with N_Pos and the beginning of the next event

> Gtid Event:Pos:764 (0X2fc) N_pos:829 (0X33d) Time:1496993581 Event_size:65 (bytes)

Gtid:89dfa8a4-cb13-11e6-b54-0c29a879a3:3

-- > Query Event:Pos:829 (0X33d) N_Pos:901 (0X385) Time:1496993581 Event_size:72 (bytes)

Exe_time:0 Use_db:test Statment (35b-trun): BEGIN / *! Trx beginnings / Gno:3

-> Map Event:Pos901 (0X385) N_pos:949 (0X3b5) Time:1496993581 Event_size:48 (bytes)

TABLE_ID:142 DB_NAME:test TABLE_NAME:test Gno:3

-> Insert Event:Pos:949 (0X3b5) N_pos:993 (0X3e1) Time:1496993581 Event_size:44 (bytes)

Dml on table: test.test table_id:142 Gno:3

> Xid Event:Pos:993 (0X3e1) N_Pos:1024 (0X400) Time:1496993581 Event_size:31 (bytes)

COMMIT; / *! Trx end*/ Gno:3-notice here that it ends with N_Pos and the beginning of the next event

> Gtid Event:Pos:1024 (0X400) N_pos:1089 (0X441) Time:1496993584 Event_size:65 (bytes)

Gtid:89dfa8a4-cb13-11e6-b54-0c29a879a3:4

-- > Query Event:Pos:1089 (0X441) N_Pos:1161 (0X489) Time:1496993584 Event_size:72 (bytes)

Exe_time:0 Use_db:test Statment (35b-trun): BEGIN / *! Trx beginnings / Gno:4

-> Map Event:Pos1161 (0X489) N_pos:1209 (0X4b9) Time:1496993584 Event_size:48 (bytes)

TABLE_ID:142 DB_NAME:test TABLE_NAME:test Gno:4

-> Delete Event:Pos:1209 (0X4b9) N_pos:1262 (0X4ee) Time:1496993584 Event_size:53 (bytes)

Dml on table: test.test table_id:142 Gno:4

> Xid Event:Pos:1262 (0X4ee) N_Pos:1293 (0X50d) Time:1496993584 Event_size:31 (bytes)

COMMIT; / *! Trx end*/ Gno:4-notice here that it ends with N_Pos and the beginning of the next event

Obviously, there are three things here.

1,504 to 764 is a thing, the tool shows that the event is Insert Event, in the table test.test

2.764 to 1024 is a thing, the tool shows that the event is Insert Event, in the table test.test

3. 1024 to 1293 is a thing. The tool shows that the event is Delete Event, which is shown in the table test.test.

This is what I do, this tool is mainly through the analysis of binlog event to find things, of course, mysqlbinlog can also be just a bit unintuitive output.

Be sure to pay attention to the beginning and end of something when passing mysqlbinlog analysis.

(mysqlbinlog testsla.000003-vv-- start-postions=504-- stop-postions=1024-- base64-output=decode-rows view does not display binary content through the base64 algorithm)

(mysqlbinlog testsla.000003-vv-- start-postions=504-- stop-postions=1024 view displaying binary content through base64 algorithm)

Let's analyze the above through mysqlbinlog.

# at 473

# 170609 15:20:45 server id 933310 end_log_pos 504 CRC32 0x609296d7 Xid = 933310

Note that the end of the last thing here is called xid event.

# at 504-this is the starting point of thing 1, not called gtid event

# 170609 15:32:58 server id 933310 end_log_pos 569 CRC32 0xf7eebfc7 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '89dfa8a4-cb13-11e6-b504-000c29a879a3

# at 569-this event is query event

# 170609 15:32:58 server id 933310 end_log_pos 641 CRC32 0xb4caa78c Query thread_id=4 exec_time=0 error_code=0

SET timestamp 1496993578

BEGIN

/ *! * /

# at 641-this event is map event

# 170609 15:32:58 server id 933310 end_log_pos 689 CRC32 0xb055655f Table_map: `test`.`test` mapped to number

# at 689-this event is insert event

# 170609 15:32:58 server id 933310 end_log_pos 733 CRC32 0xd907a353 Write_rows: table id 142 flags: STMT_END_F

# INSERT INTO `test`.`test`

# SET

# @ 1room1 / * INT meta=0 nullable=1 is_null=0 * /

# @ 2room2 / * INT meta=0 nullable=1 is_null=0 * /

# at 733-this event is xid event

# 170609 15:32:58 server id 933310 end_log_pos 764 CRC32 0x9dbe0a6b Xid = 323

Here is the end of a thing called xid event, but note that it is not 733 but the starting position of the next event 764 or the end_log_pos of xid event, otherwise it will be rolled back.

# at 764

# 170609 15:33:01 server id 933310 end_log_pos 829 CRC32 0x82aac64c GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '89dfa8a4-cb13-11e6-b504-000c29a879a3 bank /

So we think that the binlog of a thing is 504 to 764.

This will happen if it is written as 733.

Mysqlbinlog testsla.000003-start-position=504-stop-position=733-vv-base64-output=decode-rows

.

# at 689

# 170609 15:32:58 server id 933310 end_log_pos 733 CRC32 0xd907a353 Write_rows: table id 142 flags: STMT_END_F

# INSERT INTO `test`.`test`

# SET

# @ 1room1 / * INT meta=0 nullable=1 is_null=0 * /

# @ 2room2 / * INT meta=0 nullable=1 is_null=0 * /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /-- obviously there is no xid event, no commit, but mysqlbinlog has a rollback at home and rolls back.

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETI

Okay, here we briefly introduce the process of a thing binlog event, as shown by my tool.

> Gtid Event: things begin

-> Query Event:begin

-> Map Event: table mapping

-> Insert Event: authentic data binary format

> Xid Event: commit

This is a thing that needs to be experienced by event, which does not enable GTID,GTID EVENT in 5.7. it is still called anonymous gtid event and NONYMOUS_GTID_LOG_EVENT, and in 5.6,

Most gtid event changes, because 5. 6 parallel slave does not rely on binlog group commit.

If you want to learn about these event and infobin tools, please refer to the article:

Http://blog.itpub.net/7728585/viewspace-2133188/ parsing MYSQL BINLOG binary format (1)-- preparation

Http://blog.itpub.net/7728585/viewspace-2133189/ parsing MYSQL BINLOG binary format (2)-- FORMAT_DESCRIPTION_EVENT

Http://blog.itpub.net/7728585/viewspace-2133321/ parsing MYSQL BINLOG binary format (3)-- QUERY_EVENT

Http://blog.itpub.net/7728585/viewspace-2133429/ parsing MYSQL BINLOG binary format (4)-- TABLE_MAP_EVENT

Http://blog.itpub.net/7728585/viewspace-2133463/ parsing MYSQL BINLOG binary format (5)-- WRITE_ROW_EVENT

Http://blog.itpub.net/7728585/viewspace-2133469/ parsing MYSQL BINLOG binary format (6)-- UPDATE_ROW_EVENT/DELETE_ROW_EVENT

Http://blog.itpub.net/7728585/viewspace-2133502/ parsing MYSQL BINLOG binary format (7)-- Xid_log_event/XID_EVENT

Http://blog.itpub.net/7728585/viewspace-2133506/ parses MYSQL BINLOG binary format (8)-- GTID_LOG_EVENT/ANONYMOUS_GTID_LOG_EVENT and others

Http://blog.itpub.net/7728585/viewspace-2133534/ parses MYSQL BINLOG binary format (9)-- infobin parses binlog help documents

Http://blog.itpub.net/7728585/viewspace-2133537/ parses MYSQL BINLOG binary format (10)-- question and answer

Finally, let's take a look at one thing about the sentence pattern, which is rarely used by people, but let's make a comparison.

> Gtid Event:Pos:194 (0Xc2) N_pos:259 (0X103) Time:1496998879 Event_size:65 (bytes)

Gtid:89dfa8a4-cb13-11e6-b54-0c29a879a3:5

-- > Query Event:Pos:259 (0X103) N_Pos:338 (0X152) Time:1496998879 Event_size:79 (bytes)

Exe_time:0 Use_db:test Statment (35b-trun): BEGIN / *! Trx beginnings / Gno:5

-- > Query Event:Pos:338 (0X152) N_Pos:428 (0X1ac) Time:1496998879 Event_size:90 (bytes)

Exe_time:0 Use_db:test Statment (35b-trun): delete from test Gno:5

> Xid Event:Pos:428 (0X1ac) N_Pos:459 (0X1cb) Time:1496998879 Event_size:31 (bytes)

COMMIT; / *! Trx end*/ Gno:5

And

> GTID EVENT: things begin

-- > Query Event: begin

-- > Query Event: true statement

> Xid Event: commit

Without Map Event and Insert Event, of course, recording statements saves space.

Here is the mysqlbinlog output

# at 194-start with GTID EVENT

# 170609 17:01:19 server id 933310 end_log_pos 259 CRC32 0x6a408c33 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '89dfa8a4-cb13-11e6-b504-000c29a879a3

# at 259-- Query Event BEGIN

# 170609 17:01:19 server id 933310 end_log_pos 338 CRC32 0x9b25b2af Query thread_id=2 exec_time=0 error_code=0

SET timestamp 1496998879

SET @ @ session.pseudoplastics thread readreadable cards idler / session.

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards modewords 1075838976 Universe

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C utf8 * /! * /

SET @ @ session.session setting setting clientbook 83 recording session. Collationalization connection83 session session. Collationalization serveration serverwriting 83Universe

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

BEGIN

/ *! * /

# at 338-Query Event authentic statement

# 170609 17:01:19 server id 933310 end_log_pos 428 CRC32 0x4e4230f8 Query thread_id=2 exec_time=0 error_code=0

Use `test` / *! * /

SET timestamp 1496998879

Delete from test

/ *! * /

# at 428-XID EVENT ends

# 170609 17:01:19 server id 933310 end_log_pos 459 CRC32 0x38079d60 Xid = 159th

Com _ MIT _ blank /

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.