Parsing MySQL Binlog--(5) Rows_event:write_rows_event, up

1. Introduction

For statement format binlog, all SQL statements that have been deleted and changed are recorded in Query_event, while the row format Binlog records changes to the database in rows_event format. Rows_event is divided into 3 kinds: write_rows_event, update_rows_event, delete_rows_event, respectively, corresponding to insert, UPDATE, DELETE. Write_rows_event contains the data to be inserted; Update_rows_event contains not only the modified value of the row, but also the pre-modified value; delete_rows_event simply records the primary key value of the deleted row.
2. Define the format

3, Case explanation

Mysql> Show Binlog events in "mysql-bin.000002" +------------------+------+-------------+-----------+----------- --+-----------------------------------------------------------------+| Log_name | Pos | Event_type | server_id | End_log_pos | Info |+------------------+------+-------------+-----------+---- ---------+-----------------------------------------------------------------+|    mysql-bin.000002 | 4 |        Format_desc |         11 | 120 | Server ver:5.6.26-debug-log, Binlog ver:4 | |  mysql-bin.000002 | 120 |        Query |         11 | 191 | BEGIN | |  mysql-bin.000002 | 191 |        Table_map |         11 | 236 | Table_id:70 (YZS.T1) | |  mysql-bin.000002 | 236 |        Write_rows |         11 | 280 | Table_id:70 Flags:stmt_end_f | | mysql-bin.000002 | 280 |        Xid |         11 | 311 | COMMIT/* xid=9 */| |  mysql-bin.000002 | 311 |        Query |         11 | 382 | BEGIN | |  mysql-bin.000002 | 382 |        Table_map |         11 | 427 | Table_id:70 (YZS.T1) | |  mysql-bin.000002 | 427 |        Write_rows |         11 | 471 | Table_id:70 Flags:stmt_end_f | |  mysql-bin.000002 | 471 |        Xid |         11 | 502 | COMMIT/* xid=37 */| |  mysql-bin.000002 | 502 |        Query |         11 | 573 | BEGIN | |  mysql-bin.000002 | 573 |        Table_map |         11 | 618 | Table_id:70 (YZS.T1) | |  mysql-bin.000002 | 618 |        Write_rows |         11 | 662 | TABLE_ID:70 Flags: Stmt_end_f | |  mysql-bin.000002 | 662 |        Xid |         11 | 693 | COMMIT/* xid=46 */| |  mysql-bin.000002 | 693 |        Query |         11 | 764 | BEGIN | |  mysql-bin.000002 | 764 |        Table_map |         11 | 809 | Table_id:70 (YZS.T1) | |  mysql-bin.000002 | 809 |        Write_rows |         11 | 853 | Table_id:70 Flags:stmt_end_f | |  mysql-bin.000002 | 853 |        Xid |         11 | 884 | COMMIT/* xid=47 */| |  mysql-bin.000002 | 884 |        Query |         11 | 955 | BEGIN | |  mysql-bin.000002 | 955 |        Table_map |        11 | 1000 | Table_id:70 (YZS.T1) | | mysql-bin.000002 | 1000 |        Write_rows |        11 | 1044 | Table_id:70 Flags:stmt_end_f | | mysql-bin.000002 | 1044 |        Xid |        11 | 1075 | COMMIT/* xid=56 */| | mysql-bin.000002 | 1075 |        Query |        11 | 1199 | Use ' Yzs '; CREATE TABLE t2 (id1 int primary key,id2 int,id3 int) | | mysql-bin.000002 | 1199 |        Query |        11 | 1270 | BEGIN | | mysql-bin.000002 | 1270 |        Table_map |        11 | 1316 | table_id:71 (yzs.t2) | | mysql-bin.000002 | 1316 |        Write_rows |        11 | 1364 | table_id:71 Flags:stmt_end_f | | mysql-bin.000002 | 1364 |        Xid |        11 | 1395 | COMMIT/* xid=59 */| | mysql-bin.000002 | 1395 |        Query |        11 | 1466 |                   BEGIN                                        || mysql-bin.000002 | 1466 |        Table_map |        11 | 1512 | table_id:71 (yzs.t2) | | mysql-bin.000002 | 1512 |        Delete_rows |        11 | 1560 | table_id:71 Flags:stmt_end_f | | mysql-bin.000002 | 1560 |        Xid |        11 | 1591 | COMMIT/* xid=60 */| | mysql-bin.000002 | 1591 |        Query |        11 | 1662 | BEGIN | | mysql-bin.000002 | 1662 |        Table_map |        11 | 1708 | table_id:71 (yzs.t2) | | mysql-bin.000002 | 1708 |        Write_rows |        11 | 1756 | table_id:71 Flags:stmt_end_f | | mysql-bin.000002 | 1756 |        Xid |        11 | 1787 | COMMIT/* xid=63 */|+------------------+------+-------------+-----------+-------------+-----------------------------------------------------------------+34 Rows in Set (0.00 sec)

3.1 Insert
Mysqlbinlog tools See the log:

# at 1316  #180324 23:03:59 server id 11  end_log_pos 1364 CRC32 0xb23b2d4f    Write_rows: table id 71 flags: STMT_END_F  BINLOG ‘  zzu3WhMLAAAALgAAACQFAAAAAEcAAAAAAAEAA3l6cwACdDIAAwMDAwAGU9yKKQ==  zzu3Wh4LAAAAMAAAAFQFAAAAAEcAAAAAAAEAAgAD//gBAAAAAQAAAAEAAABPLTuy  ‘/*!*/;  ### INSERT INTO `yzs`.`t2`  ### SET  ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */  ###   @2=1 /* INT meta=0 nullable=1 is_null=0 */  ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */  # at 1364  

------Public Event Header-----

1) timestamp:cf 3b B7 5a,4 bytes

2) event_type:1e,1 bytes, write_rows_event = 30

3) server-id:0b 00 00 00, 4 bytes is 11

4) event_size:30 00 00 00, 4 bytes, that is, the total size of the event is 48 bytes

5) Next-log pos:54 05 00 00, 4 bytes, that is, the next log position is a 1364 byte location

6) flag:00 00

-------Private Event Header------

7) Table id:47 00 00 00 00 00, 6 bytes, or 71

8) flag:01 00, 2 bytes, can contain the following information. Whether the event is the last event of the statement, whether a FOREIGN key constraint check is required, whether a two-level index for INNODB requires a unique check, whether the event contains a complete row of data, which means that all columns are overwritten.

9) var-size Header len:02 00

Rw_v_extrainfo_tag, M_extra_row_data: No

---------Event Body---------

11) Number of columns: 03, or 3 columns

COLUMNS-PRESENT-BITMAP:FF, which contains data for all columns

Null-bitmap:f8, i.e. 1111 1000, or 3 columns are NOT NULL

14) column values: 01 00 00 00 01 00 00 00 01 00 00 00, that is, the value of 3 columns is 1,1,1

CHECKSUM:4F 2d 3b B2

2.2 Delete

# at 1512  #180325  0:19:45 server id 11  end_log_pos 1560 CRC32 0xc6b58a5e    Delete_rows: table id 71 flags: STMT_END_F  BINLOG ‘  kU23WhMLAAAALgAAAOgFAAAAAEcAAAAAAAEAA3l6cwACdDIAAwMDAwAG0hGsLw==  kU23WiALAAAAMAAAABgGAAAAAEcAAAAAAAEAAgAD//gBAAAAAQAAAAEAAABeirXG  ‘/*!*/;  ### DELETE FROM `yzs`.`t2`  ### WHERE  ###   @1=1 /* INT meta=0 nullable=0 is_null=0 */  ###   @2=1 /* INT meta=0 nullable=1 is_null=0 */  ###   @3=1 /* INT meta=0 nullable=1 is_null=0 */  # at 1560  

Similar to the type of insert, this only looks at the last saved value:

F8 01 00 00 00 01 00 00 00 01 00 00 00

That is, the row value of the delete is stored.

Update similar, no longer use the case to explain, interested can be resolved in accordance with the format.

Parsing MySQL Binlog--(5) Rows_event:write_rows_event, up