2025年6月6日 星期五 乙巳(蛇)年 三月初十 设为首页 加入收藏
rss
掌上城东
  • 进入网站首页面

    掌上城东书院
    定制您的专属资源库
  • m.cdsy.xyz

    掌上城东书院
    随时随地，想看就看
关注我们
内容服务
旗下网站

高级搜索
帮助
加入我们

首页

您当前的位置：首页 > 计算机 > 软件应用 > 数据库 > MySQL

MySQL binlog 格式简析

时间：09-30来源：作者：点击数：28

关于 binlog 是什么，以及作用、格式等基本信息，可以查看上一篇文章关于MySQL binlog二进制日志。

本文主要介绍增、删、改操作时，具体记录的binlog格式。

binlog 格式有三种：statement, row,以及mixed，下文基于row的格式进行说明。

首先，使用flush logs命令，新建binlog 文件。

  • mysql >show master status\G

  • *************************** 1. row ***************************

  •              File: mysql-bin.000005

  •          Position: 194

  •      Binlog_Do_DB:

  •  Binlog_Ignore_DB:

  • Executed_Gtid_Set: ac1f1133-a1a5-11eb-8677-0a580a54ec79:1-2

  • 1 row in set (0.00 sec)

  • 

  • mysql >flush logs;

  • Query OK, 0 rows affected (0.00 sec)

  • 

  • mysql >show master status\G

  • *************************** 1. row ***************************

  •              File: mysql-bin.000006

  •          Position: 194

  •      Binlog_Do_DB:

  •  Binlog_Ignore_DB:

  • Executed_Gtid_Set: ac1f1133-a1a5-11eb-8677-0a580a54ec79:1-2

  • 1 row in set (0.00 sec)

  •

查看当前服务的 server uuid

  • mysql >select @@server_uuid;

  • +--------------------------------------+

  • | @@server_uuid                        |

  • +--------------------------------------+

  • | ab53b640-a1a5-11eb-936b-0a580abd9262 |

  • +--------------------------------------+

  • 1 row in set (0.00 sec)

  •

创建db

创建db school：

  • create database school;

  •

接着查看binlog。

查看binlog，不能使用vim 等文本编辑工具，需要使用mysql或 mysqlbinlog解析后查看。

  • # mysqlbinlog -vvv mysql-bin.000006

  • /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

  • /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

  • DELIMITER /*!*/;

  • # at 4

  • #211226 22:02:43 server id 189146098  end_log_pos 123 CRC32 0xf2ce8fbb 	Start: binlog v 4, server v 5.7.26-29-log created 211226 22:02:43

  • # Warning: this binlog is either in use or was not closed properly.

  • BINLOG '

  • A3bIYQ/yI0YLdwAAAHsAAAABAAQANS43LjI2LTI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

  • AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

  • AbuPzvI=

  • '/*!*/;

  • # at 123

  • #211226 22:02:43 server id 189146098  end_log_pos 194 CRC32 0x0b053ce1 	Previous-GTIDs

  • # ac1f1133-a1a5-11eb-8677-0a580a54ec79:1-2

  • # at 194

  • #211226 22:11:40 server id 189146098  end_log_pos 259 CRC32 0x4c0611d1 	GTID	last_committed=0	sequence_number=1	rbr_only=no

  • SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:1'/*!*/;

  • # at 259

  • #211226 22:11:40 server id 189146098  end_log_pos 359 CRC32 0x43e3e3ea 	Query	thread_id=97708	exec_time=0	error_code=0

  • SET TIMESTAMP=1640527900/*!*/;

  • SET @@session.pseudo_thread_id=97708/*!*/;

  • SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

  • SET @@session.sql_mode=1073741824/*!*/;

  • SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

  • /*!\C utf8 *//*!*/;

  • SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=224/*!*/;

  • SET @@session.lc_time_names=0/*!*/;

  • SET @@session.collation_database=DEFAULT/*!*/;

  • create database school

  • /*!*/;

  • 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*/;

  •

从上面的解析结果可以得到以下信息：

# at 4 - 123 文件开头的第一个event FORMAT_DESCRIPTION_EVENT，定义了binlog格式版本等信息。

# at 123 - 194 声明了前面定义的GTID(event类型 Previous-GTIDs)，这里是

ac1f1133-a1a5-11eb-8677-0a580a54ec79:1-2。每个文件的开头都有一个PREVIOUS_GTIDS_LOG_EVENT

# at 194 - 259 定义当前的GTID(event类型GTID)。

# at 259 - 359 是创建db的SQL(event类型Query)。

创建表

建表SQL:

  • CREATE TABLE `student` (

  •   `id` int(11) NOT NULL AUTO_INCREMENT,

  •   `name` varchar(45) NOT NULL,

  •   `age` tinyint(4) NOT NULL DEFAULT '0',

  •   PRIMARY KEY (`id`),

  •   UNIQUE KEY `name_UNIQUE` (`name`)

  • ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;

  •

对应的binlog

  • # at 359

  • #211226 22:25:53 server id 189146098  end_log_pos 424 CRC32 0xb659ddb1 	GTID	last_committed=1	sequence_number=2	rbr_only=no

  • SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:2'/*!*/;

  • # at 424

  • #211226 22:25:53 server id 189146098  end_log_pos 753 CRC32 0xa0440382 	Query	thread_id=97708	exec_time=0	error_code=0

  • use `school`/*!*/;

  • SET TIMESTAMP=1640528753/*!*/;

  • CREATE TABLE `student` (

  •   `id` int(11) NOT NULL AUTO_INCREMENT,

  •   `name` varchar(45) NOT NULL,

  •   `age` tinyint(4) NOT NULL DEFAULT '0',

  •   PRIMARY KEY (`id`),

  •   UNIQUE KEY `name_UNIQUE` (`name`)

  • ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4

  • /*!*/;

  • 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*/;

  •

从上面的解析结果可以得到以下信息：

# at 359 - 424 定义当前的GTID(event类型GTID)。

# at 424 - 753 定义创建表SQL(event类型Query)。

插入数据

插入语句SQL：

  • insert into student(name, age) values('Kirk', 12);

  •

对应的binlog

  • # at 753

  • #211226 22:35:12 server id 189146098  end_log_pos 818 CRC32 0x42b8da60 	GTID	last_committed=2	sequence_number=3	rbr_only=yes

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

  • SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:3'/*!*/;

  • # at 818

  • #211226 22:35:12 server id 189146098  end_log_pos 892 CRC32 0xd00763cc 	Query	thread_id=97708	exec_time=0	error_code=0

  • SET TIMESTAMP=1640529312/*!*/;

  • BEGIN

  • /*!*/;

  • # at 892

  • #211226 22:35:12 server id 189146098  end_log_pos 965 CRC32 0xf39d9aaf 	Rows_query

  • # insert into student(name, age) values('Kirk', 12)

  • # at 965

  • #211226 22:35:12 server id 189146098  end_log_pos 1021 CRC32 0xd5989628 	Table_map: `school`.`student` mapped to number 109

  • # at 1021

  • #211226 22:35:12 server id 189146098  end_log_pos 1067 CRC32 0x87226373 	Write_rows: table id 109 flags: STMT_END_F

  • 

  • BINLOG '

  • oH3IYR3yI0YLSQAAAMUDAACAADFpbnNlcnQgaW50byBzdHVkZW50KG5hbWUsIGFnZSkgdmFsdWVz

  • KCdLaXJrJywgMTIpr5qd8w==

  • oH3IYRPyI0YLOAAAAP0DAAAAAG0AAAAAAAEABnNjaG9vbAAHc3R1ZGVudAADAw8BArQAACiWmNU=

  • oH3IYR7yI0YLLgAAACsEAAAAAG0AAAAAAAEAAgAD//gJAAAABEtpcmsMc2Mihw==

  • '/*!*/;

  • ### INSERT INTO `school`.`student`

  • ### SET

  • ###   @1=9 /* INT meta=0 nullable=0 is_null=0 */

  • ###   @2='Kirk' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */

  • ###   @3=12 /* TINYINT meta=0 nullable=0 is_null=0 */

  • # at 1067

  • #211226 22:35:12 server id 189146098  end_log_pos 1098 CRC32 0x216f2a1f 	Xid = 4030264

  • 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*/;

  •

从上面的解析结果可以得到以下信息：

# at 753 - 818 定义当前的GTID(event类型GTID)。

# at 818 - 892 声名事务的开始语句BEGIN(event类型Query)。

# at 892 - 965 定义insert语句(event类型Rows_query)。

# at 965 - 1021 定义表映射(event类型Table_map)。

# at 1021 - 1067 定义insert ROWS_EVENT(event类型Write_rows)。

# at 1067 - 1098 定义事务的结束语句COMMIT(event类型Xid)。

对于ROW格式的binlog，所有的DML语句都是记录在ROWS_EVENT中。

ROWS_EVENT分为三种：

  • WRITE_ROWS_EVENT，对应insert操作，包含了要插入的数据
  • UPDATE_ROWS_EVENT，对应update操作，包括了修改前、后的数据
  • DELETE_ROWS_EVENT，对应delete操作，包括要删除的数据

对于QUERY_EVENT事件，是以文本形式记录DML操作的。而对于ROWS_EVENT事件，不是以文本格式记录的，这也是会使用mysqlbinlog解析的原因。

更新数据

更新SQL:

  • >update student set age=13 where name='Kirk';

  •

对应的binlog

  • # at 1098

  • #211226 23:33:03 server id 189146098  end_log_pos 1163 CRC32 0xb8d0196a 	GTID	last_committed=3	sequence_number=4	rbr_only=yes

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

  • SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:4'/*!*/;

  • # at 1163

  • #211226 23:33:03 server id 189146098  end_log_pos 1237 CRC32 0x961aeabd 	Query	thread_id=97708	exec_time=0	error_code=0

  • SET TIMESTAMP=1640532783/*!*/;

  • BEGIN

  • /*!*/;

  • # at 1237

  • #211226 23:33:03 server id 189146098  end_log_pos 1304 CRC32 0xde123000 	Rows_query

  • # update student set age=13 where name='Kirk'

  • # at 1304

  • #211226 23:33:03 server id 189146098  end_log_pos 1360 CRC32 0x78aa0f0d 	Table_map: `school`.`student` mapped to number 109

  • # at 1360

  • #211226 23:33:03 server id 189146098  end_log_pos 1418 CRC32 0x9f7989f9 	Update_rows: table id 109 flags: STMT_END_F

  • 

  • BINLOG '

  • L4vIYR3yI0YLQwAAABgFAACAACt1cGRhdGUgc3R1ZGVudCBzZXQgYWdlPTEzIHdoZXJlIG5hbWU9

  • J0tpcmsnADAS3g==

  • L4vIYRPyI0YLOAAAAFAFAAAAAG0AAAAAAAEABnNjaG9vbAAHc3R1ZGVudAADAw8BArQAAA0Pqng=

  • L4vIYR/yI0YLOgAAAIoFAAAAAG0AAAAAAAEAAgAD///4CQAAAARLaXJrDPgJAAAABEtpcmsN+Yl5

  • nw==

  • '/*!*/;

  • ### UPDATE `school`.`student`

  • ### WHERE

  • ###   @1=9 /* INT meta=0 nullable=0 is_null=0 */

  • ###   @2='Kirk' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */

  • ###   @3=12 /* TINYINT meta=0 nullable=0 is_null=0 */

  • ### SET

  • ###   @1=9 /* INT meta=0 nullable=0 is_null=0 */

  • ###   @2='Kirk' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */

  • ###   @3=13 /* TINYINT meta=0 nullable=0 is_null=0 */

  • # at 1418

  • #211226 23:33:03 server id 189146098  end_log_pos 1449 CRC32 0x0b69970c 	Xid = 4069613

  • 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*/;

  •

从上面的解析结果可以得到以下信息：

# at 1098 - 1163 定义当前的GTID(event类型GTID)。

# at 1163 - 1237 声名事务的开始语句BEGIN(event类型Query)。

# at 1237 - 1304 定义update语句(event类型Rows_query)。

# at 1304 - 1360 定义表映射(event类型Table_map)。

# at 1360 - 1418 定义update ROWS_EVENT(event类型Write_rows)。

# at 1418 - 1449 定义事务的结束语句COMMIT(event类型Xid)。

删除数据

删除SQL:

  • mysql > delete from student where name='Kirk';

  •

对应的binlg

  • # at 1449

  • #211226 23:43:40 server id 189146098  end_log_pos 1514 CRC32 0x1247a93c 	GTID	last_committed=4	sequence_number=5	rbr_only=yes

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

  • SET @@SESSION.GTID_NEXT= 'ab53b640-a1a5-11eb-936b-0a580abd9262:5'/*!*/;

  • # at 1514

  • #211226 23:43:40 server id 189146098  end_log_pos 1588 CRC32 0x7bf526b3 	Query	thread_id=97708	exec_time=0	error_code=0

  • SET TIMESTAMP=1640533420/*!*/;

  • BEGIN

  • /*!*/;

  • # at 1588

  • #211226 23:43:40 server id 189146098  end_log_pos 1649 CRC32 0x559aca06 	Rows_query

  • # delete from student where name='Kirk'

  • # at 1649

  • #211226 23:43:40 server id 189146098  end_log_pos 1705 CRC32 0x5d32bd8c 	Table_map: `school`.`student` mapped to number 109

  • # at 1705

  • #211226 23:43:40 server id 189146098  end_log_pos 1751 CRC32 0xed427cf3 	Delete_rows: table id 109 flags: STMT_END_F

  • 

  • BINLOG '

  • rI3IYR3yI0YLPQAAAHEGAACAACVkZWxldGUgZnJvbSBzdHVkZW50IHdoZXJlIG5hbWU9J0tpcmsn

  • BsqaVQ==

  • rI3IYRPyI0YLOAAAAKkGAAAAAG0AAAAAAAEABnNjaG9vbAAHc3R1ZGVudAADAw8BArQAAIy9Ml0=

  • rI3IYSDyI0YLLgAAANcGAAAAAG0AAAAAAAEAAgAD//gJAAAABEtpcmsN83xC7Q==

  • '/*!*/;

  • ### DELETE FROM `school`.`student`

  • ### WHERE

  • ###   @1=9 /* INT meta=0 nullable=0 is_null=0 */

  • ###   @2='Kirk' /* VARSTRING(180) meta=180 nullable=0 is_null=0 */

  • ###   @3=13 /* TINYINT meta=0 nullable=0 is_null=0 */

  • # at 1751

  • #211226 23:43:40 server id 189146098  end_log_pos 1782 CRC32 0x3e95500a 	Xid = 4076811

  • 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*/;

  •

从上面的解析结果可以得到以下信息：

# at 1449 - 1514 定义当前的GTID(event类型GTID)。

# at 1514 - 1588 声名事务的开始语句BEGIN(event类型Query)。

# at 1588 - 1649 定义delete语句(event类型Rows_query)。

# at 1649 - 1705 定义表映射(event类型Table_map)。

# at 1705 - 1751 定义delete ROWS_EVENT(event类型Delete_rows)。

# at 1751 - 1782 定义事务的结束语句COMMIT(event类型Xid)。

以上，本文主要介绍了基本的增、删、改操作对应的binlog格式。

一个事务是由很多event组成的，其中开始event是BEGIN，结束event是COMMIT(event 类型XID)。

在基于主从复制的架构中，如果COMMIT在传输到从库的过程中丢失，会出现什么问题呢？后面有时间再具体说明这个问题。

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
1
来顶一下
返回首页
返回首页
上一篇:MySQL中的 空值（null）和空字符（‘‘） 下一篇:MySQL中的加密函数
推荐内容
Git的安装与卸载详细流程（非常详细，亲测可用）
Git的安装与卸载详细		Photoshop非常实用的批处理技巧
Photoshop非常实用的
高速PCB布局布线规范
高速PCB布局布线规范		解决windows+nginx下 PHP-CGI 进程崩溃问题
解决windows+nginx下
相关内容
栏目更新
栏目热门
本栏推荐
关于我们 | 联系我们 | 用户协议 | 广告服务 | 申请链接 | 网站地图 | 版权声明 | 用户留言 | 招聘信息 | 帮助中心 | 社区讨论 | 下载服务
中国文明网传播文明
Copyright ©2019-2024 CDSY Corporation, All Rights Reserved
城东书院® CDSY.XYZ 版权所有 湘ICP备19021508号-1 公安网备案湘公网安备 43102202000103号
※学习、工作、生活信息平台※
无意即是有缘，偶然即是必然。不问客从何来，本站为缘而开。
本网大部分资源来源于会员上传，除本网组织的资源外，版权归原作者所有，如有侵犯版权，请立刻和本网联系并提供证据，本网将在三个工作日内改正。
美工素材
免费外链
一起解密
城东书院
美工之家