Skip to content

Instantly share code, notes, and snippets.

@tom--bo

tom--bo/007.md

Created September 13, 2019 00:11
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save tom--bo/635c0abbe0e92a2d05acb160ee21d00b to your computer and use it in GitHub Desktop.
Save tom--bo/635c0abbe0e92a2d05acb160ee21d00b to your computer and use it in GitHub Desktop.
Download ZIP
mybinlog_samples
Raw
007.md

content

mybinlog: https://github.com/tom--bo/mybinlog のoutput例

stetement based loggingでauto_incrementなカラムありでクエリ実行

query

set binlog_format = statement;
CREATE TABLE `tbl2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` int NOT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);
insert into tbl2 (id, c1, c2) values (1, 1, 1);
insert into tbl2 (c1, c2) values (2, 2); # auto_incrementに任せる
insert into tbl2 (c1, c2) values (3, NULL);

begin;
insert into tbl2 (c1, c2) values (4, 4);
insert into tbl2 (c1, c2) values (5, NULL);
commit;

update tbl2 set c1 = 100, c2 = 100 where id = 1;
update tbl2 set c1 = 100, c2 = 100 where id = 100; # 空打ち

output

/path/to/mybinlog % go run  *.go -p samples/binlog.000007
Read [ samples/binlog.000007 ] ...
totalCount:  34
successCount:  34
errorCount:  0
unknownCount:  0
-- Event count --
             ROTATE_EVENT: 1
 FORMAT_DESCRIPTION_EVENT: 1
              QUERY_EVENT: 15
             INTVAR_EVENT: 4
 ANONYMOUS_GTID_LOG_EVENT: 7
 PREVIOUS_GTIDS_LOG_EVENT: 1
                XID_EVENT: 5

2019-09-12 18:04:26 --------
[Header]
  EventType   : FORMAT_DESCRIPTION_EVENT
  ServerID    : 1
  Eventlength : 120
  NextPosition: 124
  Flags       : 0

[Body]
  BinlogEvent: 4
  ServerVersion: 8.0.17
  CreateTimeStamp: 1970-01-01 09:00:00 +0900 JST
  HeaderLength: 19
  PostHeaderLength: [0 13 0 8 0 0 0 0 4 0 4 0 0 0 96 0 4 26 8 0 0 0 8 8 8 2 0 0 0 10 10 10 42 42 0 18 52 0 10 1 105 77 52 16]

----------------------------

2019-09-12 18:04:26 --------
[Header]
  EventType   : PREVIOUS_GTIDS_LOG_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 155
  Flags       : 128

[Body]

----------------------------

2019-09-12 18:04:30 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 234
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:04:30 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 212
  NextPosition: 446
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 46
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: CREATE TABLE `tbl2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` int NOT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

----------------------------

2019-09-12 18:04:42 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 525
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:04:42 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 80
  NextPosition: 605
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 35
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:04:42 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 121
  NextPosition: 726
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 35
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: insert into tbl2 (id, c1, c2) values (1, 1, 1)

----------------------------

2019-09-12 18:04:42 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 757
  Flags       : 0

[Body]
  XID: 45

----------------------------

2019-09-12 18:04:42 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 836
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:04:42 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 80
  NextPosition: 916
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 35
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:04:42 --------
[Header]
  EventType   : INTVAR_EVENT
  ServerID    : 1
  Eventlength : 32
  NextPosition: 948
  Flags       : 0

[Body]
  Opt1: INSERT_ID_EVENT
  Value: 2

----------------------------

2019-09-12 18:04:42 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 114
  NextPosition: 1062
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 35
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: insert into tbl2 (c1, c2) values (2, 2)

----------------------------

2019-09-12 18:04:42 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1093
  Flags       : 0

[Body]
  XID: 46

----------------------------

2019-09-12 18:04:44 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 1172
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:04:44 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 80
  NextPosition: 1252
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 35
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:04:44 --------
[Header]
  EventType   : INTVAR_EVENT
  ServerID    : 1
  Eventlength : 32
  NextPosition: 1284
  Flags       : 0

[Body]
  Opt1: INSERT_ID_EVENT
  Value: 3

----------------------------

2019-09-12 18:04:44 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 117
  NextPosition: 1401
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 35
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: insert into tbl2 (c1, c2) values (3, NULL)

----------------------------

2019-09-12 18:04:44 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1432
  Flags       : 0

[Body]
  XID: 47

----------------------------

2019-09-12 18:04:50 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 1511
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:04:48 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 80
  NextPosition: 1591
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 35
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:04:48 --------
[Header]
  EventType   : INTVAR_EVENT
  ServerID    : 1
  Eventlength : 32
  NextPosition: 1623
  Flags       : 0

[Body]
  Opt1: INSERT_ID_EVENT
  Value: 4

----------------------------

2019-09-12 18:04:48 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 114
  NextPosition: 1737
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 35
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: insert into tbl2 (c1, c2) values (4, 4)

----------------------------

2019-09-12 18:04:48 --------
[Header]
  EventType   : INTVAR_EVENT
  ServerID    : 1
  Eventlength : 32
  NextPosition: 1769
  Flags       : 0

[Body]
  Opt1: INSERT_ID_EVENT
  Value: 5

----------------------------

2019-09-12 18:04:48 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 117
  NextPosition: 1886
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 35
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: insert into tbl2 (c1, c2) values (5, NULL)

----------------------------

2019-09-12 18:04:50 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1917
  Flags       : 0

[Body]
  XID: 49

----------------------------

2019-09-12 18:04:55 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 1996
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:04:55 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 89
  NextPosition: 2085
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 44
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:04:55 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 131
  NextPosition: 2216
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 44
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: update tbl2 set c1 = 100, c2 = 100 where id = 1

----------------------------

2019-09-12 18:04:55 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 2247
  Flags       : 0

[Body]
  XID: 52

----------------------------

2019-09-12 18:04:56 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 2326
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:04:56 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 89
  NextPosition: 2415
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 44
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:04:56 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 133
  NextPosition: 2548
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 44
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: update tbl2 set c1 = 100, c2 = 100 where id = 100

----------------------------

2019-09-12 18:04:56 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 90
  NextPosition: 2638
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 44
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: COMMIT

----------------------------

2019-09-12 18:05:01 --------
[Header]
  EventType   : ROTATE_EVENT
  ServerID    : 1
  Eventlength : 44
  NextPosition: 2682
  Flags       : 0

[Body]
  NextPos: 4
  NextName: binlog.000008

----------------------------
Raw
008.md

content

mybinlog: https://github.com/tom--bo/mybinlog のoutput例

row based loggingで以下に設定

  • binlog_format = row
  • binlog_row_image = minimal

query

set binlog_format = row;
set binlog_row_image = minimal;
CREATE TABLE `tbl3` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` int NOT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);
insert into tbl3 (id, c1, c2) values (1, 1, 1);
insert into tbl3 (c1, c2) values (2, 2); # auto_incrementに任せる
insert into tbl3 (c1, c2) values (3, NULL);

begin;
insert into tbl3 (c1, c2) values (4, 4);
insert into tbl3 (c1, c2) values (5, NULL);
commit;

update tbl3 set c1 = 100, c2 = 100 where id = 1;
update tbl3 set c1 = 100, c2 = 100 where id = 100; # 空打ち

output

/path/to/mybinlog % go run  *.go -p samples/binlog.000008
Read [ samples/binlog.000008 ] ...
totalCount:  32
successCount:  32
errorCount:  0
unknownCount:  0
-- Event count --
        WRITE_ROWS_EVENT2: 5
 FORMAT_DESCRIPTION_EVENT: 1
 ANONYMOUS_GTID_LOG_EVENT: 6
              QUERY_EVENT: 6
       UPDATE_ROWS_EVENT2: 1
             ROTATE_EVENT: 1
                XID_EVENT: 5
 PREVIOUS_GTIDS_LOG_EVENT: 1
          TABLE_MAP_EVENT: 6

2019-09-12 18:05:01 --------
[Header]
  EventType   : FORMAT_DESCRIPTION_EVENT
  ServerID    : 1
  Eventlength : 120
  NextPosition: 124
  Flags       : 0

[Body]
  BinlogEvent: 4
  ServerVersion: 8.0.17
  CreateTimeStamp: 1970-01-01 09:00:00 +0900 JST
  HeaderLength: 19
  PostHeaderLength: [0 13 0 8 0 0 0 0 4 0 4 0 0 0 96 0 4 26 8 0 0 0 8 8 8 2 0 0 0 10 10 10 42 42 0 18 52 0 10 1 241 160 241 39]

----------------------------

2019-09-12 18:05:01 --------
[Header]
  EventType   : PREVIOUS_GTIDS_LOG_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 155
  Flags       : 128

[Body]

----------------------------

2019-09-12 18:05:57 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 234
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:05:57 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 212
  NextPosition: 446
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 46
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: CREATE TABLE `tbl3` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` int NOT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 525
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 74
  NextPosition: 599
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 29
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 650
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl3
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 48
  NextPosition: 698
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 00000111
  IsNullAfter: 00000000
  AfterImage: [1 0 0 0 1 0 0 0 1 0 0 0]
  AfterNumOfCol: [7 0 1 0 0 0 1 0 0 0 1 0 0 0 155 240 106 104]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 729
  Flags       : 0

[Body]
  XID: 58

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 808
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 74
  NextPosition: 882
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 29
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 933
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl3
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 48
  NextPosition: 981
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 00000111
  IsNullAfter: 00000000
  AfterImage: [2 0 0 0 2 0 0 0 2 0 0 0]
  AfterNumOfCol: [7 0 2 0 0 0 2 0 0 0 2 0 0 0 114 145 178 232]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1012
  Flags       : 0

[Body]
  XID: 59

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 1091
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 74
  NextPosition: 1165
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 29
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 1216
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl3
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 44
  NextPosition: 1260
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 00000111
  IsNullAfter: 00000100
  AfterImage: [3 0 0 0 3 0 0 0]
  AfterNumOfCol: [7 4 3 0 0 0 3 0 0 0 73 219 169 249]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1291
  Flags       : 0

[Body]
  XID: 60

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 1370
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 74
  NextPosition: 1444
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 29
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 1495
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl3
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 48
  NextPosition: 1543
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 00000111
  IsNullAfter: 00000000
  AfterImage: [4 0 0 0 4 0 0 0 4 0 0 0]
  AfterNumOfCol: [7 0 4 0 0 0 4 0 0 0 4 0 0 0 21 24 241 203]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 1594
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl3
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 44
  NextPosition: 1638
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 00000111
  IsNullAfter: 00000100
  AfterImage: [5 0 0 0 5 0 0 0]
  AfterNumOfCol: [7 4 5 0 0 0 5 0 0 0 30 118 75 142]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1669
  Flags       : 0

[Body]
  XID: 62

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 1748
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 83
  NextPosition: 1831
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 38
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 1882
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl3
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : UPDATE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 50
  NextPosition: 1932
  Flags       : 0

[Body]
  TableID: 91
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedBefore: 00000001
  IsUsedAfter: 00000110
  IsNullBefore: 00000000
  IsNullAfter: 00000001
  BeforeAndAfterImage: [0 0 0 0 100 0 0 0 100 0 0 0]
  AfterNumOfCol: [1 6 0 1 0 0 0 0 100 0 0 0 100 0 0 0 159 251 163 103]

----------------------------

2019-09-12 18:06:04 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1963
  Flags       : 0

[Body]
  XID: 65

----------------------------

2019-09-12 18:06:14 --------
[Header]
  EventType   : ROTATE_EVENT
  ServerID    : 1
  Eventlength : 44
  NextPosition: 2007
  Flags       : 0

[Body]
  NextPos: 4
  NextName: binlog.000009

----------------------------
Raw
009.md

content

mybinlog: https://github.com/tom--bo/mybinlog のoutput例

row based loggingで以下に設定

  • binlog_format = row
  • binlog_row_image = full

query

set binlog_format = row;
set binlog_row_image = full;
CREATE TABLE `tbl4` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` int NOT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);
insert into tbl4 (id, c1, c2) values (1, 1, 1);
insert into tbl4 (c1, c2) values (2, 2); # auto_incrementに任せる
insert into tbl4 (c1, c2) values (3, NULL);

begin;
insert into tbl4 (c1, c2) values (4, 4);
insert into tbl4 (c1, c2) values (5, NULL);
commit;

update tbl4 set c1 = 100, c2 = 100 where id = 1;
update tbl4 set c1 = 100, c2 = 100 where id = 100; # 空打ち

output

/path/to/mybinlog % go run  *.go -p samples/binlog.000009
Read [ samples/binlog.000009 ] ...
totalCount:  32
successCount:  32
errorCount:  0
unknownCount:  0
-- Event count --
              QUERY_EVENT: 6
                XID_EVENT: 5
               STOP_EVENT: 1
        WRITE_ROWS_EVENT2: 5
 FORMAT_DESCRIPTION_EVENT: 1
       UPDATE_ROWS_EVENT2: 1
 PREVIOUS_GTIDS_LOG_EVENT: 1
 ANONYMOUS_GTID_LOG_EVENT: 6
          TABLE_MAP_EVENT: 6

2019-09-12 18:06:14 --------
[Header]
  EventType   : FORMAT_DESCRIPTION_EVENT
  ServerID    : 1
  Eventlength : 120
  NextPosition: 124
  Flags       : 0

[Body]
  BinlogEvent: 4
  ServerVersion: 8.0.17
  CreateTimeStamp: 1970-01-01 09:00:00 +0900 JST
  HeaderLength: 19
  PostHeaderLength: [0 13 0 8 0 0 0 0 4 0 4 0 0 0 96 0 4 26 8 0 0 0 8 8 8 2 0 0 0 10 10 10 42 42 0 18 52 0 10 1 9 251 35 207]

----------------------------

2019-09-12 18:06:14 --------
[Header]
  EventType   : PREVIOUS_GTIDS_LOG_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 155
  Flags       : 128

[Body]

----------------------------

2019-09-12 18:07:35 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 234
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:07:35 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 212
  NextPosition: 446
  Flags       : 0

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 46
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: CREATE TABLE `tbl4` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` int NOT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
)

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 525
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 74
  NextPosition: 599
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 29
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 650
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl4
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 48
  NextPosition: 698
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 11111111
  IsNullAfter: 00000000
  AfterImage: [1 0 0 0 1 0 0 0 1 0 0 0]
  AfterNumOfCol: [255 0 1 0 0 0 1 0 0 0 1 0 0 0 1 238 158 139]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 729
  Flags       : 0

[Body]
  XID: 72

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 808
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 74
  NextPosition: 882
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 29
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 933
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl4
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 48
  NextPosition: 981
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 11111111
  IsNullAfter: 00000000
  AfterImage: [2 0 0 0 2 0 0 0 2 0 0 0]
  AfterNumOfCol: [255 0 2 0 0 0 2 0 0 0 2 0 0 0 232 143 70 11]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1012
  Flags       : 0

[Body]
  XID: 73

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 1091
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 74
  NextPosition: 1165
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 29
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 1216
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl4
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 44
  NextPosition: 1260
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 11111111
  IsNullAfter: 00000100
  AfterImage: [3 0 0 0 3 0 0 0]
  AfterNumOfCol: [255 4 3 0 0 0 3 0 0 0 153 116 196 130]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1291
  Flags       : 0

[Body]
  XID: 74

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 1370
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 74
  NextPosition: 1444
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 29
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 1495
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl4
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 48
  NextPosition: 1543
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 11111111
  IsNullAfter: 00000000
  AfterImage: [4 0 0 0 4 0 0 0 4 0 0 0]
  AfterNumOfCol: [255 0 4 0 0 0 4 0 0 0 4 0 0 0 143 6 5 40]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 1594
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl4
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : WRITE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 44
  NextPosition: 1638
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedAfter: 11111111
  IsNullAfter: 00000100
  AfterImage: [5 0 0 0 5 0 0 0]
  AfterNumOfCol: [255 4 5 0 0 0 5 0 0 0 206 217 38 245]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1669
  Flags       : 0

[Body]
  XID: 76

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : ANONYMOUS_GTID_LOG_EVENT
  ServerID    : 1
  Eventlength : 79
  NextPosition: 1748
  Flags       : 0

[Body]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : QUERY_EVENT
  ServerID    : 1
  Eventlength : 83
  NextPosition: 1831
  Flags       : 8

[Body]
  ThreadID: 8
  ExecutionTime: 0
  DBNameLen: 3
  ErrorCode: 0
  StatusVarLen: 38
  StatusVariables: {[] []  0 0 0 0 0   0 0 []}
  DatabaseName: db1
  SQLStatement: BEGIN

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : TABLE_MAP_EVENT
  ServerID    : 1
  Eventlength : 51
  NextPosition: 1882
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0]
  DBNameLen: 3
  DBName: db1
  TableNameLen: 4
  TableName: tbl4
  NumOfCol: 3
  ColType: [LONG LONG LONG]
  MetaBlockLen: 0
  MetaBlock: []
  NullColumns: 00000100

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : UPDATE_ROWS_EVENT2
  ServerID    : 1
  Eventlength : 62
  NextPosition: 1944
  Flags       : 0

[Body]
  TableID: 92
  ReservedByte: [0 0 2 0]
  NumOfCol: 3
  IsUsedBefore: 11111111
  IsUsedAfter: 11111111
  IsNullBefore: 00000000
  IsNullAfter: 00000001
  BeforeAndAfterImage: [0 0 0 1 0 0 0 1 0 0 0 0 1 0 0 0 100 0 0 0 100 0 0 0]
  AfterNumOfCol: [255 255 0 1 0 0 0 1 0 0 0 1 0 0 0 0 1 0 0 0 100 0 0 0 100 0 0 0 236 211 86 134]

----------------------------

2019-09-12 18:07:46 --------
[Header]
  EventType   : XID_EVENT
  ServerID    : 1
  Eventlength : 31
  NextPosition: 1975
  Flags       : 0

[Body]
  XID: 79

----------------------------

2019-09-12 18:08:12 --------
[Header]
  EventType   : STOP_EVENT
  ServerID    : 1
  Eventlength : 23
  NextPosition: 1998
  Flags       : 0

[Body]

----------------------------
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment