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

MySQL binlog 格式简析

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

关于 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在传输到从库的过程中丢失，会出现什么问题呢？后面有时间再具体说明这个问题。

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
来顶一下
返回首页
返回首页
上一篇: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号
※学习、工作、生活信息平台※
本网大部分资源来源于会员上传，除本网组织的资源外，版权归原作者所有，如有侵犯版权，请立刻和本网联系并提供证据，本网将在三个工作日内改正。