前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >Mysql binlog解析

Mysql binlog解析

作者头像
mingjie
发布于 2022-05-12 09:36:10
5.2K0
发布于 2022-05-12 09:36:10
举报
文章被收录于专栏：Postgresql源码分析Postgresql源码分析

mysql binlog解析

5.6为例

1 binlog解析（fmt=row）

logbin打开，找到路径

代码语言：javascript
复制
mysql> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------------------------------+
| Variable_name                   | Value                                               |
+---------------------------------+-----------------------------------------------------+
| log_bin                         | ON                                                  |
| log_bin_basename                | databases/data/mydata5470/mysql/mysql-bin           |
| log_bin_index                   | databases/data/mydata5470/mysql/master-log-bin.index|
| log_bin_trust_function_creators | ON                                                  |
| log_bin_use_v1_row_events       | ON                                                  |
| sql_log_bin                     | ON                                                  |
+---------------------------------+-----------------------------------------------------+

mysql>  show variables like '%binlog_row_image%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+

切日志记录当前binlog位置

代码语言：javascript
复制
mysql> flush logs;
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000238
         Position: 12345678
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 123-123-123-123,456-456-456-456

mysql> show master logs;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000104 |        194 |
+------------------+------------+

构造测试数据

代码语言：javascript
复制
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table t1 (id int primary key,info varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test.t1 values(1,'a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(2,'b');
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set info='c' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from t1 where id=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+----+------+
| id | info |
+----+------+
|  2 | c    |
+----+------+

直接查看内容

（注意没有看到原始SQL）

代码语言：javascript
复制
mysql> show binlog events in 'mysql-bin.000104';
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                    |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000104 |    4 | Format_desc    |   6700070 |         123 | Server ver: 5.7.27-log, Binlog ver: 4                                   |
| mysql-bin.000104 |  123 | Previous_gtids |   6700070 |         194 | 09fb76ae-d2dd-11e9-a611-00163f00f11a:1-2073488                          |
| mysql-bin.000104 |  194 | Gtid           |   6700070 |         259 | SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073489' |
| mysql-bin.000104 |  259 | Query          |   6700070 |         353 | create database test                                                    |
| mysql-bin.000104 |  353 | Gtid           |   6700070 |         418 | SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073490' |
| mysql-bin.000104 |  418 | Query          |   6700070 |         545 | use `test`; create table t1 (id int primary key,info varchar(32))       |
| mysql-bin.000104 |  545 | Gtid           |   6700070 |         610 | SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073491' |
| mysql-bin.000104 |  610 | Query          |   6700070 |         682 | BEGIN                                                                   |
| mysql-bin.000104 |  682 | Table_map      |   6700070 |         730 | table_id: 404 (test.t1)                                                 |
| mysql-bin.000104 |  730 | Write_rows     |   6700070 |         772 | table_id: 404 flags: STMT_END_F                                         |
| mysql-bin.000104 |  772 | Xid            |   6700070 |         803 | COMMIT /* xid=13009150 */                                               |
| mysql-bin.000104 |  803 | Gtid           |   6700070 |         868 | SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073492' |
| mysql-bin.000104 |  868 | Query          |   6700070 |         940 | BEGIN                                                                   |
| mysql-bin.000104 |  940 | Table_map      |   6700070 |         988 | table_id: 404 (test.t1)                                                 |
| mysql-bin.000104 |  988 | Write_rows     |   6700070 |        1030 | table_id: 404 flags: STMT_END_F                                         |
| mysql-bin.000104 | 1030 | Xid            |   6700070 |        1061 | COMMIT /* xid=13009151 */                                               |
| mysql-bin.000104 | 1061 | Gtid           |   6700070 |        1126 | SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073493' |
| mysql-bin.000104 | 1126 | Query          |   6700070 |        1198 | BEGIN                                                                   |
| mysql-bin.000104 | 1198 | Table_map      |   6700070 |        1246 | table_id: 404 (test.t1)                                                 |
| mysql-bin.000104 | 1246 | Update_rows    |   6700070 |        1296 | table_id: 404 flags: STMT_END_F                                         |
| mysql-bin.000104 | 1296 | Xid            |   6700070 |        1327 | COMMIT /* xid=13009153 */                                               |
| mysql-bin.000104 | 1327 | Gtid           |   6700070 |        1392 | SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073494' |
| mysql-bin.000104 | 1392 | Query          |   6700070 |        1464 | BEGIN                                                                   |
| mysql-bin.000104 | 1464 | Table_map      |   6700070 |        1512 | table_id: 404 (test.t1)                                                 |
| mysql-bin.000104 | 1512 | Delete_rows    |   6700070 |        1554 | table_id: 404 flags: STMT_END_F                                         |
| mysql-bin.000104 | 1554 | Xid            |   6700070 |        1585 | COMMIT /* xid=13009154 */                                               |
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------+

解析原始内容（无法执行）

代码语言：javascript
复制
$ mysqlbinlog mysql-bin.000104

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191218 14:33:10 server id 6700070  end_log_pos 123 CRC32 0x142ce0a9 	Start: binlog v 4, server v 5.7.27-log created 191218 14:33:10
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
Jsj5XQ8mPGYAdwAAAHsAAAABAAQANS43LjI3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AangLBQ=
'/*!*/;
# at 123
#191218 14:33:10 server id 6700070  end_log_pos 194 CRC32 0x60254ef1 	Previous-GTIDs
# 09fb76ae-d2dd-11e9-a611-00163f00f11a:1-2073488
# at 194
#191218 14:33:47 server id 6700070  end_log_pos 259 CRC32 0x8328abd1 	GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073489'/*!*/;
# at 259
#191218 14:33:47 server id 6700070  end_log_pos 353 CRC32 0x87ad0591 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650827/*!*/;
SET @@session.pseudo_thread_id=3055425/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test
/*!*/;
# at 353
#191218 14:34:24 server id 6700070  end_log_pos 418 CRC32 0x7ff1085c 	GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073490'/*!*/;
# at 418
#191218 14:34:24 server id 6700070  end_log_pos 545 CRC32 0xe24a80ef 	Query	thread_id=3055425	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1576650864/*!*/;
create table t1 (id int primary key,info varchar(32))
/*!*/;
# at 545
#191218 14:34:31 server id 6700070  end_log_pos 610 CRC32 0x3a3f5558 	GTID	last_committed=2	sequence_number=3	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073491'/*!*/;
# at 610
#191218 14:34:31 server id 6700070  end_log_pos 682 CRC32 0xdbb72271 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650871/*!*/;
BEGIN
/*!*/;
# at 682
#191218 14:34:31 server id 6700070  end_log_pos 730 CRC32 0xc325bfc9 	Table_map: `test`.`t1` mapped to number 404
# at 730
#191218 14:34:31 server id 6700070  end_log_pos 772 CRC32 0x6723da9f 	Write_rows: table id 404 flags: STMT_END_F

BINLOG '
d8j5XRMmPGYAMAAAANoCAAAAAJQBAAAAAAEABHRlc3QAAnQxAAIDDwKAAALJvyXD
d8j5XR4mPGYAKgAAAAQDAAAAAJQBAAAAAAEAAgAC//wBAAAAAWGf2iNn
'/*!*/;
# at 772
#191218 14:34:31 server id 6700070  end_log_pos 803 CRC32 0x95c983e8 	Xid = 13009150
COMMIT/*!*/;
# at 803
#191218 14:34:37 server id 6700070  end_log_pos 868 CRC32 0x170dafd1 	GTID	last_committed=3	sequence_number=4	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073492'/*!*/;
# at 868
#191218 14:34:37 server id 6700070  end_log_pos 940 CRC32 0x436edc71 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650877/*!*/;
BEGIN
/*!*/;
# at 940
#191218 14:34:37 server id 6700070  end_log_pos 988 CRC32 0x07759bb2 	Table_map: `test`.`t1` mapped to number 404
# at 988
#191218 14:34:37 server id 6700070  end_log_pos 1030 CRC32 0xaec1efb2 	Write_rows: table id 404 flags: STMT_END_F

BINLOG '
fcj5XRMmPGYAMAAAANwDAAAAAJQBAAAAAAEABHRlc3QAAnQxAAIDDwKAAAKym3UH
fcj5XR4mPGYAKgAAAAYEAAAAAJQBAAAAAAEAAgAC//wCAAAAAWKy78Gu
'/*!*/;
# at 1030
#191218 14:34:37 server id 6700070  end_log_pos 1061 CRC32 0xc3482e90 	Xid = 13009151
COMMIT/*!*/;
# at 1061
#191218 14:34:48 server id 6700070  end_log_pos 1126 CRC32 0x45606b5d 	GTID	last_committed=4	sequence_number=5	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073493'/*!*/;
# at 1126
#191218 14:34:48 server id 6700070  end_log_pos 1198 CRC32 0xb89f326c 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650888/*!*/;
BEGIN
/*!*/;
# at 1198
#191218 14:34:48 server id 6700070  end_log_pos 1246 CRC32 0xe3c1eef5 	Table_map: `test`.`t1` mapped to number 404
# at 1246
#191218 14:34:48 server id 6700070  end_log_pos 1296 CRC32 0x32f2f98b 	Update_rows: table id 404 flags: STMT_END_F

BINLOG '
iMj5XRMmPGYAMAAAAN4EAAAAAJQBAAAAAAEABHRlc3QAAnQxAAIDDwKAAAL17sHj
iMj5XR8mPGYAMgAAABAFAAAAAJQBAAAAAAEAAgAC///8AgAAAAFi/AIAAAABY4v58jI=
'/*!*/;
# at 1296
#191218 14:34:48 server id 6700070  end_log_pos 1327 CRC32 0x51ba56c7 	Xid = 13009153
COMMIT/*!*/;
# at 1327
#191218 14:34:53 server id 6700070  end_log_pos 1392 CRC32 0x88c29411 	GTID	last_committed=5	sequence_number=6	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073494'/*!*/;
# at 1392
#191218 14:34:53 server id 6700070  end_log_pos 1464 CRC32 0x1221f492 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650893/*!*/;
BEGIN
/*!*/;
# at 1464
#191218 14:34:53 server id 6700070  end_log_pos 1512 CRC32 0x928b3780 	Table_map: `test`.`t1` mapped to number 404
# at 1512
#191218 14:34:53 server id 6700070  end_log_pos 1554 CRC32 0x924772b2 	Delete_rows: table id 404 flags: STMT_END_F

BINLOG '
jcj5XRMmPGYAMAAAAOgFAAAAAJQBAAAAAAEABHRlc3QAAnQxAAIDDwKAAAKAN4uS
jcj5XSAmPGYAKgAAABIGAAAAAJQBAAAAAAEAAgAC//wBAAAAAWGyckeS
'/*!*/;
# at 1554
#191218 14:34:53 server id 6700070  end_log_pos 1585 CRC32 0xc01b2402 	Xid = 13009154
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

初步解析内容（仍然无法执行）

代码语言：javascript
复制
$ mysqlbinlog mysql-bin.000104 -vv --base64-output=decode-rows
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191218 14:33:10 server id 6700070  end_log_pos 123 CRC32 0x142ce0a9 	Start: binlog v 4, server v 5.7.27-log created 191218 14:33:10
# Warning: this binlog is either in use or was not closed properly.
# at 123
#191218 14:33:10 server id 6700070  end_log_pos 194 CRC32 0x60254ef1 	Previous-GTIDs
# 09fb76ae-d2dd-11e9-a611-00163f00f11a:1-2073488
# at 194
#191218 14:33:47 server id 6700070  end_log_pos 259 CRC32 0x8328abd1 	GTID	last_committed=0	sequence_number=1	rbr_only=no
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073489'/*!*/;
# at 259
#191218 14:33:47 server id 6700070  end_log_pos 353 CRC32 0x87ad0591 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650827/*!*/;
SET @@session.pseudo_thread_id=3055425/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test
/*!*/;
# at 353
#191218 14:34:24 server id 6700070  end_log_pos 418 CRC32 0x7ff1085c 	GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073490'/*!*/;
# at 418
#191218 14:34:24 server id 6700070  end_log_pos 545 CRC32 0xe24a80ef 	Query	thread_id=3055425	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1576650864/*!*/;
create table t1 (id int primary key,info varchar(32))
/*!*/;
# at 545
#191218 14:34:31 server id 6700070  end_log_pos 610 CRC32 0x3a3f5558 	GTID	last_committed=2	sequence_number=3	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073491'/*!*/;
# at 610
#191218 14:34:31 server id 6700070  end_log_pos 682 CRC32 0xdbb72271 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650871/*!*/;
BEGIN
/*!*/;
# at 682
#191218 14:34:31 server id 6700070  end_log_pos 730 CRC32 0xc325bfc9 	Table_map: `test`.`t1` mapped to number 404
# at 730
#191218 14:34:31 server id 6700070  end_log_pos 772 CRC32 0x6723da9f 	Write_rows: table id 404 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
# at 772
#191218 14:34:31 server id 6700070  end_log_pos 803 CRC32 0x95c983e8 	Xid = 13009150
COMMIT/*!*/;
# at 803
#191218 14:34:37 server id 6700070  end_log_pos 868 CRC32 0x170dafd1 	GTID	last_committed=3	sequence_number=4	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073492'/*!*/;
# at 868
#191218 14:34:37 server id 6700070  end_log_pos 940 CRC32 0x436edc71 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650877/*!*/;
BEGIN
/*!*/;
# at 940
#191218 14:34:37 server id 6700070  end_log_pos 988 CRC32 0x07759bb2 	Table_map: `test`.`t1` mapped to number 404
# at 988
#191218 14:34:37 server id 6700070  end_log_pos 1030 CRC32 0xaec1efb2 	Write_rows: table id 404 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
# at 1030
#191218 14:34:37 server id 6700070  end_log_pos 1061 CRC32 0xc3482e90 	Xid = 13009151
COMMIT/*!*/;
# at 1061
#191218 14:34:48 server id 6700070  end_log_pos 1126 CRC32 0x45606b5d 	GTID	last_committed=4	sequence_number=5	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073493'/*!*/;
# at 1126
#191218 14:34:48 server id 6700070  end_log_pos 1198 CRC32 0xb89f326c 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650888/*!*/;
BEGIN
/*!*/;
# at 1198
#191218 14:34:48 server id 6700070  end_log_pos 1246 CRC32 0xe3c1eef5 	Table_map: `test`.`t1` mapped to number 404
# at 1246
#191218 14:34:48 server id 6700070  end_log_pos 1296 CRC32 0x32f2f98b 	Update_rows: table id 404 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='c' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
# at 1296
#191218 14:34:48 server id 6700070  end_log_pos 1327 CRC32 0x51ba56c7 	Xid = 13009153
COMMIT/*!*/;
# at 1327
#191218 14:34:53 server id 6700070  end_log_pos 1392 CRC32 0x88c29411 	GTID	last_committed=5	sequence_number=6	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073494'/*!*/;
# at 1392
#191218 14:34:53 server id 6700070  end_log_pos 1464 CRC32 0x1221f492 	Query	thread_id=3055425	exec_time=0	error_code=0
SET TIMESTAMP=1576650893/*!*/;
BEGIN
/*!*/;
# at 1464
#191218 14:34:53 server id 6700070  end_log_pos 1512 CRC32 0x928b3780 	Table_map: `test`.`t1` mapped to number 404
# at 1512
#191218 14:34:53 server id 6700070  end_log_pos 1554 CRC32 0x924772b2 	Delete_rows: table id 404 flags: STMT_END_F
### DELETE FROM `test`.`t1`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
# at 1554
#191218 14:34:53 server id 6700070  end_log_pos 1585 CRC32 0xc01b2402 	Xid = 13009154
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
  • 对于STATEMENT格式的binlog，所有的DML操作都记录在QUERY_EVENT中；
  • 而对于ROW格式的binlog，所有的DML操作都记录在ROWS_EVENT中；
    • ROWS_EVENT分为三种：WRITE_ROWS_EVENT，UPDATE_ROWS_EVENT，DELETE_ROWS_EVENT，分别对应insert，update和delete操作。
    • 对于insert操作，WRITE_ROWS_EVENT包含了要插入的数据 对于update操作，UPDATE_ROWS_EVENT不仅包含了修改后的数据，还包含了修改前的值。 对于delete操作，仅仅需要指定删除的主键（在没有主键的情况下，会给定所有列）

事实上，在ROW格式的binlog文件中， 每个ROWS_EVENT事件前都会有一个TABLE_MAP_EVENT，用于描述表的内部id和结构定义。

即便上述两个insert操作，一个没有执行use test操作，都不影响TABLE_MAP_EVENT的内容，这也会导致基于ROW格式下的库级别的复制和基于STATEMENT格式下库级别的复制的复制规则不一致。

解析带有可执行SQL（需要检索出SQL）

代码语言：javascript
复制
mysql> set binlog_rows_query_log_events=1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test.t1 values(3,'c');
Query OK, 1 row affected (0.01 sec)

mysql> update t1 set info='d' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.00 sec)

对比下效果

代码语言：javascript
复制
mysql> show binlog events in 'mysql-bin.000104' from 1585;
+------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type  | Server_id | End_log_pos | Info                                                                    |
+------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------------+
| mysql-bin.000104 | 1585 | Gtid        |   6700070 |        1650 | SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073495' |
| mysql-bin.000104 | 1650 | Query       |   6700070 |        1722 | BEGIN                                                                   |
| mysql-bin.000104 | 1722 | Rows_query  |   6700070 |        1779 | # insert into test.t1 values(3,'c')                                     |
| mysql-bin.000104 | 1779 | Table_map   |   6700070 |        1827 | table_id: 404 (test.t1)                                                 |
| mysql-bin.000104 | 1827 | Write_rows  |   6700070 |        1869 | table_id: 404 flags: STMT_END_F                                         |
| mysql-bin.000104 | 1869 | Xid         |   6700070 |        1900 | COMMIT /* xid=13009171 */                                               |
| mysql-bin.000104 | 1900 | Gtid        |   6700070 |        1965 | SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073496' |
| mysql-bin.000104 | 1965 | Query       |   6700070 |        2037 | BEGIN                                                                   |
| mysql-bin.000104 | 2037 | Rows_query  |   6700070 |        2094 | # update t1 set info='d' where id=3                                     |
| mysql-bin.000104 | 2094 | Table_map   |   6700070 |        2142 | table_id: 404 (test.t1)                                                 |
| mysql-bin.000104 | 2142 | Update_rows |   6700070 |        2192 | table_id: 404 flags: STMT_END_F                                         |
| mysql-bin.000104 | 2192 | Xid         |   6700070 |        2223 | COMMIT /* xid=13009172 */                                               |
| mysql-bin.000104 | 2223 | Gtid        |   6700070 |        2288 | SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073497' |
| mysql-bin.000104 | 2288 | Query       |   6700070 |        2360 | BEGIN                                                                   |
| mysql-bin.000104 | 2360 | Rows_query  |   6700070 |        2409 | # delete from t1 where id=3                                             |
| mysql-bin.000104 | 2409 | Table_map   |   6700070 |        2457 | table_id: 404 (test.t1)                                                 |
| mysql-bin.000104 | 2457 | Delete_rows |   6700070 |        2499 | table_id: 404 flags: STMT_END_F                                         |
| mysql-bin.000104 | 2499 | Xid         |   6700070 |        2530 | COMMIT /* xid=13009173 */                                               |
+------------------+------+-------------+-----------+-------------+-------------------------------------------------------------------------+

工具解析

代码语言：javascript
复制
$ mysqlbinlog mysql-bin.000104 -vv --base64-output=decode-rows
...
...
...

BEGIN
/*!*/;
# at 1722
#191218 14:58:31 server id 6700070  end_log_pos 1779 CRC32 0x936712e1 	Rows_query
# insert into test.t1 values(3,'c')
# at 1779
#191218 14:58:31 server id 6700070  end_log_pos 1827 CRC32 0x092db65d 	Table_map: `test`.`t1` mapped to number 404
# at 1827
#191218 14:58:31 server id 6700070  end_log_pos 1869 CRC32 0x20c0458b 	Write_rows: table id 404 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='c' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
# at 1869
#191218 14:58:31 server id 6700070  end_log_pos 1900 CRC32 0xdd6bb173 	Xid = 13009171
COMMIT/*!*/;
# at 1900
#191218 14:58:39 server id 6700070  end_log_pos 1965 CRC32 0xbb198803 	GTID	last_committed=7	sequence_number=8	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073496'/*!*/;
# at 1965
#191218 14:58:39 server id 6700070  end_log_pos 2037 CRC32 0x2792d209 	Query	thread_id=3055426	exec_time=0	error_code=0
SET TIMESTAMP=1576652319/*!*/;
BEGIN
/*!*/;
# at 2037
#191218 14:58:39 server id 6700070  end_log_pos 2094 CRC32 0x56bb8dc0 	Rows_query
# update t1 set info='d' where id=3
# at 2094
#191218 14:58:39 server id 6700070  end_log_pos 2142 CRC32 0xaae4609b 	Table_map: `test`.`t1` mapped to number 404
# at 2142
#191218 14:58:39 server id 6700070  end_log_pos 2192 CRC32 0x7652c217 	Update_rows: table id 404 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='c' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
# at 2192
#191218 14:58:39 server id 6700070  end_log_pos 2223 CRC32 0x8cf7861d 	Xid = 13009172
COMMIT/*!*/;
# at 2223
#191218 14:58:46 server id 6700070  end_log_pos 2288 CRC32 0xb5159267 	GTID	last_committed=8	sequence_number=9	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '09fb76ae-d2dd-11e9-a611-00163f00f11a:2073497'/*!*/;
# at 2288
#191218 14:58:46 server id 6700070  end_log_pos 2360 CRC32 0xadf74254 	Query	thread_id=3055426	exec_time=0	error_code=0
SET TIMESTAMP=1576652326/*!*/;
BEGIN
/*!*/;
# at 2360
#191218 14:58:46 server id 6700070  end_log_pos 2409 CRC32 0x703bdd48 	Rows_query
# delete from t1 where id=3
# at 2409
#191218 14:58:46 server id 6700070  end_log_pos 2457 CRC32 0x85433eb5 	Table_map: `test`.`t1` mapped to number 404
# at 2457
#191218 14:58:46 server id 6700070  end_log_pos 2499 CRC32 0xf4f4ddd0 	Delete_rows: table id 404 flags: STMT_END_F
### DELETE FROM `test`.`t1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
# at 2499
#191218 14:58:46 server id 6700070  end_log_pos 2530 CRC32 0x251be898 	Xid = 13009173
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

只关注SQL

代码语言：javascript
复制
$ mysqlbinlog mysql-bin.000104 -vv --base64-output=decode-rows | grep -E 'insert|update|delete'
# insert into test.t1 values(3,'c')
# update t1 set info='d' where id=3
# delete from t1 where id=3

第三方工具解析（直接得到可执行SQL）

https://github.com/danfengcao/binlog2sql

必要参数

代码语言：javascript
复制
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

必要权限

代码语言：javascript
复制
select, super/replication client, replication slave

建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO

效果

代码语言：javascript
复制
$ python binlog2sql.py -h127.0.0.1 -P5470 -uroot -proot -dtest -t t1 --start-file='mysql-bin.000104'
USE test;
create database test;
USE test;
create table t1 (id int primary key,info varchar(32));
INSERT INTO `test`.`t1`(`info`, `id`) VALUES ('a', 1); #start 610 end 772 time 2019-12-18 14:34:31
INSERT INTO `test`.`t1`(`info`, `id`) VALUES ('b', 2); #start 868 end 1030 time 2019-12-18 14:34:37
UPDATE `test`.`t1` SET `info`='c', `id`=2 WHERE `info`='b' AND `id`=2 LIMIT 1; #start 1126 end 1296 time 2019-12-18 14:34:48
DELETE FROM `test`.`t1` WHERE `info`='a' AND `id`=1 LIMIT 1; #start 1392 end 1554 time 2019-12-18 14:34:53
INSERT INTO `test`.`t1`(`info`, `id`) VALUES ('c', 3); #start 1650 end 1869 time 2019-12-18 14:58:31
UPDATE `test`.`t1` SET `info`='d', `id`=3 WHERE `info`='c' AND `id`=3 LIMIT 1; #start 1965 end 2192 time 2019-12-18 14:58:39
DELETE FROM `test`.`t1` WHERE `info`='d' AND `id`=3 LIMIT 1; #start 2288 end 2499 time 2019-12-18 14:58:46

2 binlog_format含义

https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

3 命令速记

13.7.5.1 SHOW BINARY LOGS Statement

13.7.5.2 SHOW BINLOG EVENTS Statement

13.7.5.23 SHOW MASTER STATUS Statement

13.7.5.32 SHOW RELAYLOG EVENTS Statement

13.7.5.33 SHOW SLAVE HOSTS Statement

13.7.5.34 SHOW SLAVE STATUS Statement

本文参与?腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020-03-30，如有侵权请联系 cloudcommunity@tencent.com 删除
数据库
云数据库 SQL Server
sql
https
网络安全

本文分享自 作者个人站点/博客?前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与?腾讯云自媒体同步曝光计划? ，欢迎热爱写作的你一起参与！

数据库
云数据库 SQL Server
sql
https
网络安全
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
目录
  • mysql binlog解析
    • 1 binlog解析（fmt=row）
      • 构造测试数据
      • 直接查看内容
      • 解析原始内容（无法执行）
      • 初步解析内容（仍然无法执行）
      • 解析带有可执行SQL（需要检索出SQL）
      • 第三方工具解析（直接得到可执行SQL）
    • 2 binlog_format含义
      • 3 命令速记
      相关产品与服务
      云数据库 SQL Server
      腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
      产品介绍产品文档
      云数据库采购特惠，高性能、高可靠、高安全，超值优惠等你享！
      领券

      腾讯云开发者

      扫码关注腾讯云开发者

      扫码关注腾讯云开发者

      领取腾讯云代金券

      热门产品

      热门推荐

      更多推荐

      Copyright ? 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有?

      深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059?深公网安备号 44030502008569

      腾讯云计算（北京）有限责任公司 京ICP证150476号 | ?京ICP备11018762号 | 京公网安备号11010802020287

      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

      Copyright ? 2013 - 2024 Tencent Cloud.

      All Rights Reserved. 腾讯云 版权所有

      登录 后参与评论
      http://www.vxiaotou.com