mysql binlog记录insert update delete的记录方式与flink sql不一样

最新推荐文章于 2025-04-07 15:23:23 发布

*星星之火*

最新推荐文章于 2025-04-07 15:23:23 发布

阅读量1.9k

点赞数

分类专栏： flink 文章标签： flink mysql

本文链接：https://blog.youkuaiyun.com/spark_dev/article/details/124299898

版权

flink 专栏收录该内容

38 篇文章

订阅专栏

系列文章目录

文章目录

系列文章目录
前言
一、建表语句
二、binlog信息
总结

前言

查看insert,update,delete在mysql binlog中是如何进行记录的
在flink sql中，update会转化为一条delete和一条insert，故想看看在mysql binlog的记录方式

一、建表语句

构造insert, update,delete 场景进行测试

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test values(1,11,'星星之火');
update test set name='星星'  where id=1;
delete from test where id=1;

二、binlog信息

使用命令查binlog:

[root@hadoop103 mysql]# mysqlbinlog -v --base64-output=decode-rows mysql-bin.000002

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
# at 8899204
#220420 15:47:25 server id 1  end_log_pos 8899269 CRC32 0x9eb77eea      Anonymous_GTID  last_committed=14326    sequence_number=14327   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 8899269
#220420 15:47:25 server id 1  end_log_pos 8899347 CRC32 0x4668adc8      Query   thread_id=69258 exec_time=0     error_code=0
SET TIMESTAMP=1650440845/*!*/;
BEGIN
/*!*/;
# at 8899347
#220420 15:47:25 server id 1  end_log_pos 8899404 CRC32 0xa1553fa2      Table_map: `xxzh_stock`.`test` mapped to number 265
# at 8899404
#220420 15:47:25 server id 1  end_log_pos 8899466 CRC32 0xadefe641      Write_rows: table id 265 flags: STMT_END_F
### INSERT INTO `xxzh_stock`.`test`
### SET
###   @1=1
###   @2=11
###   @3='星星之火'
# at 8899466
#220420 15:47:25 server id 1  end_log_pos 8899497 CRC32 0xef6b3913      Xid = 13347790
COMMIT/*!*/;
# at 8899497
#220420 15:47:25 server id 1  end_log_pos 8899562 CRC32 0xdd949283      Anonymous_GTID  last_committed=14327    sequence_number=14328   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 8899562
#220420 15:47:25 server id 1  end_log_pos 8899640 CRC32 0x384e2f34      Query   thread_id=69258 exec_time=0     error_code=0
SET TIMESTAMP=1650440845/*!*/;
BEGIN
/*!*/;
# at 8899640
#220420 15:47:25 server id 1  end_log_pos 8899697 CRC32 0x50755998      Table_map: `xxzh_stock`.`test` mapped to number 265
# at 8899697
#220420 15:47:25 server id 1  end_log_pos 8899781 CRC32 0x65d5e7ac      Update_rows: table id 265 flags: STMT_END_F
### UPDATE `xxzh_stock`.`test`
### WHERE
###   @1=1
###   @2=11
###   @3='星星之火'
### SET
###   @1=1
###   @2=11
###   @3='星星'
# at 8899781
#220420 15:47:25 server id 1  end_log_pos 8899812 CRC32 0xa8f1b87f      Xid = 13347791
COMMIT/*!*/;
# at 8899812
#220420 15:47:25 server id 1  end_log_pos 8899877 CRC32 0x00de2b56      Anonymous_GTID  last_committed=14328    sequence_number=14329   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 8899877
#220420 15:47:25 server id 1  end_log_pos 8899955 CRC32 0x85923293      Query   thread_id=69258 exec_time=0     error_code=0
SET TIMESTAMP=1650440845/*!*/;
BEGIN
/*!*/;
# at 8899955
#220420 15:47:25 server id 1  end_log_pos 8900012 CRC32 0x402f0426      Table_map: `xxzh_stock`.`test` mapped to number 265
# at 8900012
#220420 15:47:25 server id 1  end_log_pos 8900068 CRC32 0xd81ab133      Delete_rows: table id 265 flags: STMT_END_F
### DELETE FROM `xxzh_stock`.`test`
### WHERE
###   @1=1
###   @2=11
###   @3='星星'
# at 8900068
#220420 15:47:25 server id 1  end_log_pos 8900099 CRC32 0x0755e4b8      Xid = 13347792
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*/;

insert 语句：

### INSERT INTO `xxzh_stock`.`test`
### SET
###   @1=1
###   @2=11
###   @3='星星之火'

update语句：


### UPDATE `xxzh_stock`.`test`
### WHERE
###   @1=1
###   @2=11
###   @3='星星之火'
### SET
###   @1=1
###   @2=11
###   @3='星星'

delete语句：

### DELETE FROM `xxzh_stock`.`test`
### WHERE
###   @1=1
###   @2=11
###   @3='星星'

总结

mysql binglog中 insert ,update,delete 都是直接用sql语义进行记录。和flink-sql的方式不一样。