专栏首页mysql-dbamysql binlog日志事件解析
原创

mysql binlog日志事件解析

二进制日志(binary log)是mysql的一种日志记录了mysql中的数据变更操作,二进制日志主要有以下作用:

1.复制

2.数据恢复

3.日志审计

二进制日志有日志文件和索引文件组成,索引文件(index)记录尚未被清理的二进制日志列表

[root@ck1 logs]# ll
total 16
-rw-r----- 1 mysql mysql  242 Jun  9 11:36 mysql-bin.000003
-rw-r----- 1 mysql mysql 1805 Jun  9 11:39 mysql-bin.000004
-rw-r----- 1 mysql mysql  195 Jun  9 11:39 mysql-bin.000005
-rw-r----- 1 mysql mysql  132 Jun  9 11:39 mysql-bin.index

如下我们用mysqlbinlog来解析二进制,并进行说明:

进行相关的增删改查操作(日志格式row格式并开启的gtid)

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000004 |      195 |              |                  | 3d1b92a0-b919-11eb-87db-56c8a95977d1:1-27 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> create database testdb;
Query OK, 1 row affected (0.01 sec)
mysql> use testdb;
Database changed
mysql> create table test(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test(name) values ('binlog'),('rowlog');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update test set name='binlog_new' where name='binlog';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from test where name='binlog_new';
Query OK, 1 row affected (0.01 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000005 |      195 |              |                  | 3d1b92a0-b919-11eb-87db-56c8a95977d1:1-32 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)

解析:
[root@ck1 logs]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000004 > 4.sql

每个二进制日志文件以4个字节的魔术数开始,后面包含各种用于表示mysql数据的变更事件

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;

设置系统变量

# at 4
#210609 11:36:17 server id 2223306  end_log_pos 124 CRC32 0x0fd60233    Start: binlog v 4, server v 8.0.18 created 210609 11:36:17

# at 4 为事件在二进制日志文件中的偏移量,单位字节

#21060911:36:17 该事件的写入时间

server id 2223306 产生该时间的mysql的server id

end_log_pos 124 表示该事件结束的偏移量124,下一个事件起始偏移量为124

binlog v 4 二进制日志结构的版本为v4

server v 8.0.18 mysql的版本为8.0.18

created 21060911:36:17 表示该二进制日志的创建时间(start表示该事件的类型为Format_description_event为第一个事件,该事件的创建时间和二进制日志文件创建时间一致)

二进制日志的第一个事件是Format_description_event类型事件,记录二进制日志的版本,数据库版本,文件创建时间(binlog v 4, server v 8.0.18 created 21060911:36:17)

# at 124
#210609 11:36:17 server id 2223306  end_log_pos 195 CRC32 0xe68c4ab6    Previous-GTIDs
# 3d1b92a0-b919-11eb-87db-56c8a95977d1:1-27

Previous-GTIDs表示该事件为Previous_gtid_log_event类型事件,在mysql切换新的二进制日志文件时会写入此事件,用于记录创建该日志文件之前执行的全局事务id的集合

# at 386
#210609 11:37:44 server id 2223306  end_log_pos 463 CRC32 0x18bc8e52    GTID    last_committed=1 
 sequence_number=2       rbr_only=no     original_committed_timestamp=1623209864107514   
 immediate_commit_timestamp=1623209864107514     transaction_length=238
# original_commit_timestamp=1623209864107514 (2021-06-09 11:37:44.107514 CST)
# immediate_commit_timestamp=1623209864107514 (2021-06-09 11:37:44.107514 CST)
/*!80001 SET @@session.original_commit_timestamp=1623209864107514*//*!*/;
/*!80014 SET @@session.original_server_version=80018*//*!*/;
/*!80014 SET @@session.immediate_server_version=80018*//*!*/;
SET @@SESSION.GTID_NEXT= '3d1b92a0-b919-11eb-87db-56c8a95977d1:29'/*!*/;

GTID 表示该事件类型为Gtid_log_event,该事件记录了事务的Gtid

last_committed=1,表示该二进制日志文件中最大的已提交事务的sequence_number,当有多个事务同时提交时,这些事务的最大sequence_number是一样的,也就是last_committed一样,这是为并行复制设计的

sequence_number=2 单个二进制日志文件中sequence_number是从1开始,每个事务递增1,表示事务的顺序

SET @@SESSION.GTID_NEXT 表示在会话级别设置该事务的GTID

# at 463
#210609 11:37:44 server id 2223306  end_log_pos 624 CRC32 0xb3e84d8d    Query   thread_id=11    
exec_time=0     error_code=0    Xid = 20
use `testdb`/*!*/;
SET TIMESTAMP=1623209864/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table test(id int primary key auto_increment,name varchar(20))
/*!*/;

Query 表示该事件为Query_log_event事件

thread_id=11 执行该语句的线程id

exec_time=0 执行该语句的时间

error_code=0 执行该语句返回的错误码

SET TIMESTAMP= 设置事件时间戳

# at 703
#210609 11:38:30 server id 2223306  end_log_pos 780 CRC32 0x506c8335    Query   thread_id=11    
exec_time=0     error_code=0
SET TIMESTAMP=1623209910/*!*/;
BEGIN
/*!*/;

Query 表示该事件为Query_log_event事件

# at 780
#210609 11:38:30 server id 2223306  end_log_pos 855 CRC32 0x5beb0b5a    Rows_query
# insert into test(name) values ('binlog'),('rowlog')

Rows_query表示事件为Rows_query_log_event类型,记录数据变更的原始语句,需要设置binlog_rows_query_log_events = on才会记录该事件

# at 855
#210609 11:38:30 server id 2223306  end_log_pos 913 CRC32 0x28356463    Table_map: `testdb`.`test` 
mapped to number 87

Table_map:表示该事件为Table_map_event类型事件,记录发生变更操作的表结构

# at 913
#210609 11:38:30 server id 2223306  end_log_pos 972 CRC32 0xf2220a01    Write_rows: table id 87 
flags: STMT_END_F
### INSERT INTO `testdb`.`test`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='binlog' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `testdb`.`test`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='rowlog' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

Write_rows:表示该事件的类型为Write_rows_log_event 对应数据的insert操作

table id 87 :insert插入表的tableid 87,通过此id从上个事件table_map获取表的结构信息

flags: STMT_END_F:STMT_END_F表示当前最后一个insert事件

下面的insert语句时插入的具体内容

@1表示插入的第一个字段,注释中表示字段类型,元数据,是否为空

# at 972
#210609 11:38:30 server id 2223306  end_log_pos 1003 CRC32 0xf2d2f493   Xid = 21
COMMIT/*!*/;

Xid:表示次事件类型为Xid_event 表示事务提交

Xid =21:该事务的xid为21,在mysql异常恢复阶段,mysql会解析redo日志中处于prepare状态的事务,得到xid,然后根据xid在二进制日志中查找,如果找到匹配的xid则对事务进行重新持久化,否则回滚事务

# at 1303
#210609 11:39:06 server id 2223306  end_log_pos 1367 CRC32 0xeaabcd3a   Update_rows: table id 87
 flags: STMT_END_F
### UPDATE `testdb`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='binlog' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='binlog_new' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

Update_rows:表示该事件的类型为Update_rows_log_event 记录数据的update操作

# at 1676
#210609 11:39:34 server id 2223306  end_log_pos 1727 CRC32 0xa82ecc61   Delete_rows: table id 87
 flags: STMT_END_F
### DELETE FROM `testdb`.`test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='binlog_new' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

Delete_rows:表示该事件为Delete_rows_log_event,记录delete操作的实际数据

mysql事件总结:

Format_description_event:记录二进制日志的版本,数据库版本,文件创建时间

Previous_gtid_log_event:创建该日志文件之前执行的全局事务id的集合

Gtid_log_event:该事件记录了事务的Gtid

Query_log_event:记录执行语句,一般为DDL

Rows_query_log_event:记录数据变更的原始语句

Table_map_event:记录发生变更操作的表结构

Write_rows_log_event :对应数据的insert操作

Xid:表示事务提交,后期用于mysql崩溃恢复

Update_rows_log_event :记录数据的update操作

Delete_rows_log_event:记录delete操作的实际数据

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • mysql解析binlog日志

    binlog日志用于记录所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句。语句以“事件”的形式保存,它描述数据更改。因为...

    ytkah
  • 探究 Canal EventParser 的设计与实现奥妙

    首先我们先从官方文档来看 EventParser 的整体设计,其架构设计图如下所示:

    丁威
  • MySQL复制(二) - 详聊binlog日志

    ​有了binlog日志,我们可以实现主从架构,可以用canal、maxwell等工具实现将MySQL数据同步到大数据环境;同时可以对binlog进行解析,可以实...

    懒熊
  • MySQL binlog

    【转载请注明出处】:https://cloud.tencent.com/developer/article/1632663

    后端老鸟
  • Mysql数据库之Binlog日志使用总结

    binlog二进制日志对于mysql数据库的重要性有多大,在此就不多说了。下面根据本人的日常操作经历,并结合网上参考资料,对binlog日志使用做一梳理: 一、...

    洗尽了浮华
  • 带你解析MySQL binlog

    我们都知道,binlog可以说是MySQL中比较重要的日志了,在日常学习及运维过程中,也经常会遇到。不清楚你对binlog了解多少呢?本篇文章将从binlog作...

    MySQL技术
  • 【Mysql 实战】问题分析利器之 binlog

    文章链接:https://mp.weixin.qq.com/s/JOhdZE6ctDI0y53G_qXR8w

    程序员架构进阶
  • mysql数据恢复 转

    binlog 基本认识     MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,...

    wuweixiang
  • 线上发生死锁异常了,该怎么办

    MySQL 死锁异常是我们经常会遇到的线上异常类别,一旦线上业务日间复杂,各种业务操作之间往往会产生锁冲突,有些会导致死锁异常。这种死锁异常一般要在特定时间特定...

    程序员历小冰
  • MySQL Binlog实用攻略

    本文简要介绍 binlog 原理及其在恢复、复制中的使用方法,更多深入分析可参考 mysql 官方文档。

    腾讯数据库技术
  • “mysqlbinlog”工具做binlog server靠谱吗?

    玩过binlog server的同学都知道,它使用mysqlbinlog命令以daemon进程的方式模拟一个slave的IO线程与主库连接,可以很方便地即时同...

    沃趣科技
  • mysql的一则事务锁的问题Lock wait timeout exceeded.m

    出现这个问题感觉还是挺疑惑的,因为测试环境已经稳定运行了几个月的时间,一直没有出现过mysql事务锁的问题,于是开始着手查问题。

    开发架构二三事
  • Mysql之binlog日志说明及利用binlog日志恢复数据操作记录

    众所周知,binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlo...

    洗尽了浮华
  • 腾讯云 CDB : 深入解析 MySQL binlog

    本文简要介绍 binlog 原理及其在恢复、复制中的使用方法。

    腾讯云数据库 TencentDB
  • 两行代码修复了解析MySQL8.x binlog错位的问题!!

    作者个人研发的在高并发场景下,提供的简单、稳定、可扩展的延迟消息队列框架,具有精准的定时任务和延迟队列处理功能。自开源半年多以来,已成功为十几家中小型企业提供了...

    冰河
  • Mysql binlog 之阿里canal 1、What is Canal?2、工作原理3、Canal使用场景代码集成方式:

    canal [kə'næl],中文翻译为 水道/管道/沟渠/运河,主要用途是用于 MySQL 数据库增量日志数据的订阅、消费和解析,是阿里巴巴开发并开源的,采用...

    用户5927264
  • MySQL 日志系统之 redo log 和 binlog

    之前我们了解了一条查询语句的执行流程,并介绍了执行过程中涉及的处理模块。一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块,最后到达存储引...

    武培轩
  • MySQL死锁系列-线上死锁问题排查思路

    MySQL 死锁异常是我们经常会遇到的线上异常类别,一旦线上业务日间复杂,各种业务操作之间往往会产生锁冲突,有些会导致死锁异常。这种死锁异常一般要在特定时间特定...

    程序员历小冰
  • 重新学习MySQL数据库10:MySQL里的那些日志们

    本文是微信公众号【Java技术江湖】的《重新学习MySQL数据库》其中一篇,本文部分内容来源于网络,为了把本文主题讲得清晰透彻,也整合了很多我认为不错的技术博客...

    Java技术江湖

扫码关注云+社区

领取腾讯云代金券