mysql_binlog格式分析，快速了解binlog日志格式

最新推荐文章于 2024-05-04 13:51:18 发布
最新推荐文章于 2024-05-04 13:51:18 发布
阅读量557 收藏 8
点赞数 8
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jioulongzi/article/details/135095678
版权

mysql binlog 日志解析及分析流程

在mysql中，binlog记录了所有记录操作(insert,update,delete)当然也包括表变更等操作
本篇文章将详细讲解binlog格式以及常见的问题，为广大道友快速入门！

以下流程执行环境为:mysql8

1. 确认是否打开binlog

-- 查询是否打开binlog
show variables like 'log_bin%';
-- 查询log列表
show master logs;
-- 查询当前使用的binlog是哪一个
show master status;

2. 解析日志文件

此步骤需要注意, mysqlbinlog 版本和数据库要一致，否则可能有些莫名其妙的错误

# 解析特定日期范围
mysqlbinlog --no-defaults -vvv --database=test  --base64-output=decode-rows -v --start-datetime='2023-11-19 13:28:01' --stop-datetime='2023-12-29 13:28:03'  E:\phpstudy_pro\Extensions\MySQL8.0.12\data\binlog.000027>D:\test\111.txt

# 解析整个日志文件
mysqlbinlog  --no-defaults -vvv --database=test  --base64-output=decode-rows  E:\phpstudy_pro\Extensions\MySQL8.0.12\data\binlog.000027 > D:\test\1.txt

3. 分析日志

一个完整的日志文件有很多数据，但是都是由以下部分组成

1. 执行update 的 binlog日志 sql
-- 执行的sql
UPDATE `test`.`t_user` SET `name` = '4444', `phone` = '22' WHERE `id` = 1;

这里可以看出来,日志大概格式为:

  • 日志一般起始的地方为: # at xxxx
  • 接下来为分配的线程id和初始化操作
  • COMMIT代表当前sql或事务结束
  • 虽然更新条件只有id=1但是binlog日志将会使用所有数据当作where条件

完整日志如下:

# at 1118568
#231219 14:15:33 server id 1  end_log_pos 1118643 CRC32 0xcefdd078 	Anonymous_GTID	last_committed=60	sequence_number=61	rbr_only=yes	original_committed_timestamp=1702966533187510	immediate_commit_timestamp=1702966533187510	transaction_length=371
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1702966533187510 (2023-12-19 14:15:33.187510 中国标准时间)
# immediate_commit_timestamp=1702966533187510 (2023-12-19 14:15:33.187510 中国标准时间)
/*!80001 SET @@session.original_commit_timestamp=1702966533187510*//*!*/;
/*!80014 SET @@session.original_server_version=999999*//*!*/;
/*!80014 SET @@session.immediate_server_version=999999*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1118643
#231219 14:15:33 server id 1  end_log_pos 1118727 CRC32 0xc674f08c 	Query	thread_id=2524	exec_time=0	error_code=0
SET TIMESTAMP=1702966533/*!*/;
BEGIN
/*!*/;
# at 1118727
#231219 14:15:33 server id 1  end_log_pos 1118788 CRC32 0x1b5d03ab 	Table_map: `test`.`t_user` mapped to number 5021
# at 1118788
#231219 14:15:33 server id 1  end_log_pos 1118854 CRC32 0xf1ca05fa 	Update_rows: table id 5021 flags: STMT_END_F
### UPDATE `test`.`t_user`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='1233' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @3='22' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='4444' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @3='22' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
# at 1118854
#231219 14:15:33 server id 1  end_log_pos 1118939 CRC32 0x02cb3a70 	Query	thread_id=2524	exec_time=0	error_code=0
SET TIMESTAMP=1702966533/*!*/;
COMMIT
/*!*/;
2. 创建table的binlog日志
--  创建表的sql
CREATE TABLE `t_account` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `account` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

从binlog日志可以看出这里没有嵌套事务，仔细想想也正常。 创建表是不需要事务的。

binlog日志如下：

# at 1118939
#231219 15:10:34 server id 1  end_log_pos 1119014 CRC32 0xc3c825c3 	Anonymous_GTID	last_committed=61	sequence_number=62	rbr_only=no	original_committed_timestamp=1702969834875690	immediate_commit_timestamp=1702969834875690	transaction_length=283
# original_commit_timestamp=1702969834875690 (2023-12-19 15:10:34.875690 中国标准时间)
# immediate_commit_timestamp=1702969834875690 (2023-12-19 15:10:34.875690 中国标准时间)
/*!80001 SET @@session.original_commit_timestamp=1702969834875690*//*!*/;
/*!80014 SET @@session.original_server_version=999999*//*!*/;
/*!80014 SET @@session.immediate_server_version=999999*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1119014
#231219 15:10:34 server id 1  end_log_pos 1119222 CRC32 0x9f7b1631 	Query	thread_id=2528	exec_time=0	error_code=0
SET TIMESTAMP=1702969834/*!*/;
CREATE TABLE `test`.`t_account`  (

  `id` int(0) NOT NULL AUTO_INCREMENT,

  `account` varchar(255) NULL,

  PRIMARY KEY (`id`)

)
/*!*/;
3. 执行一个事务，里面执行3条sql:
-- 执行的sql
start TRANSACTION
INSERT INTO `test`.`t_user`(`id`, `name`, `phone`) VALUES (3, 'www3', '22');
update t_user set name = 'rrr3' where id = 3;
insert into t_account values(null, 'test3');
commit

从binlog中可以看出，事务执行也是遵循上述模板，所有的sql只有一个commit;

完整binlog：

# at 1120650
#231219 15:34:23 server id 1  end_log_pos 1120725 CRC32 0x1e836084 	Anonymous_GTID	last_committed=67	sequence_number=68	rbr_only=yes	original_committed_timestamp=1702971263546547	immediate_commit_timestamp=1702971263546547	transaction_length=527
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1702971263546547 (2023-12-19 15:34:23.546547 中国标准时间)
# immediate_commit_timestamp=1702971263546547 (2023-12-19 15:34:23.546547 中国标准时间)
/*!80001 SET @@session.original_commit_timestamp=1702971263546547*//*!*/;
/*!80014 SET @@session.original_server_version=999999*//*!*/;
/*!80014 SET @@session.immediate_server_version=999999*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1120725
#231219 15:34:12 server id 1  end_log_pos 1120800 CRC32 0x893a38dd 	Query	thread_id=2537	exec_time=0	error_code=0
SET TIMESTAMP=1702971252/*!*/;
BEGIN
/*!*/;
# at 1120800
#231219 15:34:12 server id 1  end_log_pos 1120861 CRC32 0x4b4b94d5 	Table_map: `test`.`t_user` mapped to number 5027
# at 1120861
#231219 15:34:12 server id 1  end_log_pos 1120911 CRC32 0xc726c115 	Write_rows: table id 5027 flags: STMT_END_F
### INSERT INTO `test`.`t_user`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='www3' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @3='22' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
# at 1120911
#231219 15:34:13 server id 1  end_log_pos 1120972 CRC32 0x78e88a6d 	Table_map: `test`.`t_user` mapped to number 5027
# at 1120972
#231219 15:34:13 server id 1  end_log_pos 1121038 CRC32 0xbc8c37d9 	Update_rows: table id 5027 flags: STMT_END_F
### UPDATE `test`.`t_user`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='www3' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @3='22' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='rrr3' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
###   @3='22' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
# at 1121038
#231219 15:34:15 server id 1  end_log_pos 1121099 CRC32 0xfc5925ba 	Table_map: `test`.`t_account` mapped to number 5026
# at 1121099
#231219 15:34:15 server id 1  end_log_pos 1121146 CRC32 0xffd24deb 	Write_rows: table id 5026 flags: STMT_END_F
### INSERT INTO `test`.`t_account`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='test3' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
# at 1121146
#231219 15:34:23 server id 1  end_log_pos 1121177 CRC32 0x390cb94e 	Xid = 126382
COMMIT/*!*/;

4. sql执行源头跟踪分析

首先查询当前执行的是否有当前线程，如果有的话则可以直接定位到发起者的ip, 用户等信息

select * from  information_schema.processlist where Id=#id

但更多的时候，sql发起者可能已经执行完毕并且断开连接，而binlog只能定位到当时是由哪个线程执行的，

mysql并不支持查询历史链接分配信息，所以需要自己来设计完成。

以下操作可以保存连接分配日志并：

  1. 首先需要创建审计库以及表sql
create dabase  db_monitor;
use db_monitor;
CREATE TABLE `accesslog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) DEFAULT NULL,
  `log_time` datetime DEFAULT NULL,
  `localname` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `matchname` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  1. 在mysqlinit链接中增加以下参数

修改my.cnf配置文件以下位置,修改后记得重启mysql。

# 原配置
init_connect='SET NAMES utf8'

# 修改为以下内容,主要是在默认配置前增加插入语句
init_connect='insert into db_monitor.accesslog(thread_id,log_time,localname,matchname) values(connection_id(),now(),user(),current_user());SET NAMES utf8'
  1. 创建用户需要授权用户审计库权限
    需要注意两点, root用户登录不会记录审计日志
 -- 创建用户test密码123456,并运行从任意IP链接
 create user 'test'@'%' identified by '123456';
 
 -- 赋予test用户 test,db_monitor 库的所有权限
 grant all privileges on test.* to 'test'@'%';
 grant all privileges on db_monitor.* to 'test'@'%';
 
 -- 只赋予查询权限
 grant select on db_monitor.* to 'test'@'%';
  1. 测试审计功能

使用test链接进数据库，然后查看审计表。此时会有 thread_id 分配信息。 自此以后，就可以根据bin_log中的thread_id来进行定位了

值得一提的是,mysql中的thread_id 是根据连接数单调递增的，不是线程池循环利用，但是重启后会从1开始

jioulongzi
关注
  • 8
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PHP分析Mysql的Binlog日志并恢复数据
10-29
由于mysqlbinlog命令下无法筛选出某一个表的数据所以只能把日志文件全部导出
了解mysql三大日志：binlog、redolog、undolog
·
07-13 1735
MySQL是目前业界最流行的数据库系统。日志是 mysql 数据库的重要组成部分，记录着数据库运行期间各种状态信息。 mysql 日志主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。作为开发，我们重点需要关注的是二进制日志( binlog )和事务日志(包括redo log 和 undo log )。 ...
MySQL binlog日志详解 查询和数据恢复 细节 ops范围数据恢复 时间节点范围 MySQL数据恢复 数据库备份 主从复制 增量恢复
Benjamin CSDN博客
06-30 1163
MySQL binlog日志详解 查询和数据恢复 细节 按时间点 按操作id顺序范围 数据恢复 数据库备份 主从复制 增量恢复 MySQL Binlog是二进制格式的日志文件，二进制日志记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中。用来记录Mysql内部对数据库的改动（只记录对数据的修改操作）。可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、Mysql的复制（主主数据库的复制、主从数据库的复制）。
死磕数据库系列（十一）：MySQL 日志文件解析（类型、作用）
民工哥的博客
02-21 1227
点关注公众号，回复“1024”获取2TB学习资源！Mysql 日志是什么？所谓日志，就是一种将行为动作记录到一个地方，这个地方可以是文件，文本等可存储的载体。Mysql日志就是记录整个mysql从启动，运行，到结束的整个生命周期下的行为。日志类型MySQL 中有七种日志文件，分别是：二进制日志（binlog）重做日志（redo log）回滚日志（undo log）错误日志（errorlog）慢查询...
Mysql之binlog日志浅析
月生的静心苑
08-01 4045
MySQL的binlog日志有三种格式，分别是Statement格式、Row格式和Mixed格式。Statement格式Statement格式是最简单的binlog格式，记录的是执行的SQL语句，可以通过解析SQL语句来恢复数据。这种格式的优点是简单、易于理解和分析，缺点是可能会出现数据不一致的情况，因为同一个SQL语句在不同的环境下可能会产生不同的结果。Row格式Row格式是记录每一行数据的变化，包括插入、删除和更新操作。
MySQL binlog 日志解析
hzp666的博客
07-08 7431
一 、binlog 简介binlog 是 MySQL Server 层记录的二进制日志文件，用于记录 MySQL 的数据更新或者潜在更新（比如 DELETE 语句执行删除而实际并没有符合条件的数据），select 或 show 等不会修改数据的操作则不会记录在 binlog 中。通常在 binlog_format = ROW 的环境下，我们可以通过 binlog 获取历史的 SQL 执行记录，前提是必须开启 binlog_rows_query_log_events 参数（默认关闭，建议开启），该参数可以通过
Binlog日志详解
tom的博客
05-08 1399
binlog日志详解
详解MySQL非常重要的日志—bin log
m0_73311735的博客
11-19 6703
bin log想必大家多多少少都有听过，它是MySQL中一个非常重要的日志，所以各位架构师们，如果有不了解的，一定要好好学习了，因为它涉及到数据库层面的主从复制、高可用等设计。本文讲解了MySQL中的一个非常重要的日志bin log，它主要用来做数据恢复和同步的，所以作为程序员的我们，还是很有必要对它有一个深入的认识。
MySQL的binlog日志
weixin_30321449的博客
12-02 490
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。 一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景: 其一：M...
一文读懂MySQL的binLog日志
Java之路艰难，要以高内聚低耦合的目标去设计
12-14 9671
一文读懂MySQL的binLog日志
mysql binlog日志恢复【亲测有效，有例子实测】
03-22
二进制日志（binnary log）以事件形式记录了...主从复制：mysql replication在master端开启binlog,master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。 数据恢复：通过mysqlbinlog工具来恢复数据。
MySQL读取Binlog日志常见的3种错误
09-09
主要介绍了MySQL读取Binlog日志常见的3种错误，非常不错，具有一定的参考借鉴价值 ,需要的朋友可以参考下
分析 MySQL 的 binlog 日志的工具
06-03
作为一个MySQL DBA，查看分析binlog是日常工作的一部分，不知道你是否遇到过这样的需求：一个时间段，各个表的dml统计情况
MySQL 自动清理binlog日志的方法
09-09
主要介绍了MySQL 自动清理binlog日志的方法,需要的朋友可以参考下
『MySQL 实战 45 讲』20 - 幻读是什么，幻读有什么问题？
最新发布
Gavinjou大笨象的博客
05-04 768
3. 当执行 select * from t where d=5 for update 的时候，6 个记录加上了行锁，还同时加了 7 个间隙锁，这样就确保无法再插入新的记录。
MySQL —— 表的基本操作
学习C++的小陈同学
04-30 403
本篇整理了和表结构相关的各种基本操作（增删查改等等）
MySQL 之 主从复制
我见青山多妩媚,料青山见我应如是
04-28 567
复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行，从而使得从库和主库的数据保持同步。MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。
【MySQL】驱动表、被驱动表详解。—— 性能优化。
我梦
05-01 574
【MySQL】驱动表、被驱动表详解。—— 性能优化。
【MySQL】1.安装与配置
2301_79719531的博客
04-28 834
文件的安全性问题。文件不利于数据查询和管理。文件不利于存储海量数据。文件在程序中控制不方便。数据库存储介质：磁盘+内存。为了解决上述问题，专家们设计出更加利于管理数据的软件——数据库，他能更加有效的管理数据。数据库可以提供远程服务，即通过远程连接来使用数据库，因此也称为数据库服务器。
mysql binlog解析 c_MySQL binlog格式解析
05-24
MySQL的二进制日志（binlog）是一种记录MySQL服务器上所有修改的二进制文件，用于数据恢复、主从同步等操作。binlog的格式有多种，其中最常用的是ROW格式和STATEMENT格式。 c_MySQL binlog格式解析是一个工具，可以解析MySQL binlog文件中的内容并将其转换为易于理解的文本格式。它可以解析ROW和STATEMENT格式的binlog文件，并提供了一些有用的功能，如过滤特定的事件、只输出特定的列等。 使用c_MySQL binlog格式解析工具，你可以更好地了解MySQL binlog的结构和内容，为数据恢复和主从同步等操作提供更好的支持。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值