mysql gtid binlog_gtid复制binlog日志解读

查看日志列表mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 154 |

+------------------+-----------+

1 row in set (0.00 sec)

查看日志详情mysql> show binlog events in 'mysql-bin.000001';

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| mysql-bin.000001 | 4 | Format_desc | 101 | 123 | Server ver: 5.7.29-log, Binlog ver: 4 |

| mysql-bin.000001 | 123 | Previous_gtids | 101 | 154 | |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

2 rows in set (0.00 sec)

gtid日志结构

查看具体内容user@ubuntu18041:~/sandboxes/multi_msb_mysql-5_7_29/node1/data$ mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004

......

#200509 16:39:09 server id 101 end_log_pos 1109 CRC32 0x687a46e4 GTID last_committed=4 sequence_number=5 rbr_only=no

SET @@SESSION.GTID_NEXT= '00015630-1111-1111-1111-111111111111:5'/*!*/;

# at 1109

#200509 16:39:09 server id 101 end_log_pos 1257 CRC32 0x32b29304 Query thread_id=16 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1589013549/*!*/;

create table office_dogs (id int(11), name varchar(255), primary key (id))

/*!*/;

# at 1257

#200509 16:39:28 server id 101 end_log_pos 1322 CRC32 0xf0dde736 GTID last_committed=5 sequence_number=6 rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= '00015630-1111-1111-1111-111111111111:6'/*!*/;

# at 1322

#200509 16:39:28 server id 101 end_log_pos 1394 CRC32 0x346c855f Query thread_id=16 exec_time=0 error_code=0

SET TIMESTAMP=1589013568/*!*/;

BEGIN

/*!*/;

# at 1394

#200509 16:39:28 server id 101 end_log_pos 1451 CRC32 0x04346b63 Table_map: `test`.`office_dogs` mapped to number 108

# at 1451

#200509 16:39:28 server id 101 end_log_pos 1496 CRC32 0x8b66efaf Write_rows: table id 108 flags: STMT_END_F

### INSERT INTO `test`.`office_dogs`

### SET

### @1=12

### @2='Carl'

# at 1496

#200509 16:39:28 server id 101 end_log_pos 1527 CRC32 0x0d0ac4dd Xid = 80

COMMIT/*!*/;

# at 1527

#200509 16:46:54 server id 101 end_log_pos 1592 CRC32 0xd834980d GTID last_committed=6 sequence_number=7 rbr_only=yes

每个事务都是以gtid开头。多条记录组成了一个完整的gtid事务，第一条记录是gtid，然后是事务的数据记录，有Query/Tablemap/Write_rows等。每条记录又都有多行，第一行包含了事件开始时间/server id/结束位置等，最后一行是结束位置# at。

参考资料: