当前位置： 首页 > news >正文

使用OceanBase的Oblogminer进行日志挖掘的实践

news 2025/6/14 8:24:53

本文转载于OceanBase博客博主，作者为 banjin。本文观点仅代表作者观点。

背景

近期遇到了两个需要运用日志挖掘技术解决的问题。一个是Oracle环境下的ogg突然出问题了，开发指出两个环境的表结构存在差异，缺失了某些字段。但是经过分析，所有字段都是在建表时创建的，因此排除了版本遗漏的可能性。为了找出问题的根源，用了LogMiner这一神器，最终追溯到是另一位DBA从其他环境复制了表结构导致的。另一个问题是，在一个生产环境的MySQL数据库中，有人误删了表且没有备份。幸运的是，所有的binlog日志都被保存了下来。我尝试使用binlog2sql来恢复数据，但由于数据量庞大，处理速度较慢，最终采用了其他方法完成了数据恢复。

因此，学习下OceanBase的oblogminer，技术就像灾备，可以不用但是不能没有，也是体现dba兜底的能力。

一、oblogminer安装

先从安装说起，oblogminer支持yum安装，OceanBase Utils 安装包安装、源码编译安装

附上官网文档： https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001429573

我使用的源码编译安装，整体比较简单，附上安装步骤

##安装必要的包，官方文档没有，建议官方文档也加入一下
yum install git cpio make glibc-devel glibc-headers binutils m4##拉取 OceanBase 数据库的开源代码到本地。
git clone https://github.com/oceanbase/oceanbase.git##切换分支，编译版本要与数据库版本相同，当前不同版本不兼容
git checkout 4.2.x##根据需要选择编译模式编译 oblogminer。
Debug 模式或者release
执行 build.sh 脚本，并指定调试模式对应模式
bash build.sh debug --init进入编译目录，Debug模式进入build_debug，release进入build_release
cd build_debug##编译 oblogminer
make oblogminer##复制编译产物到 /usr/local/bin 目录下
sudo cp src/logservice/logminer/oblogminer /usr/local/bin

遇到问题：

1、ld.lld: error: cannot open Scrt1.o: No such file or directory

解决：需要安装m4的包

yum install git cpio make glibc-devel glibc-headers binutils m4
##如果m4已经安装，还是遇到类似问题可以执行如下：cd src/sql/parser
##手动执行一下
bash gen_parser.sh

2、[101906][dep_create.sh][ERROR] wget http://mirrors.aliyun.com/oceanbase/community/stable/el/7/x86_64//obshell-4.2.4.2-12024102115.el7.x86_64.rpm

[101906][dep_create.sh][ERROR] Failed to init rpm deps

cp deps/init/oceanbase.el7.x86_64.deps  deps/init/oceanbase.el7.x86_64.deps_bak
vi deps/init/oceanbase.el7.x86_64.deps
##注释掉或者改成可以下载的版本
。。。。
[tools-deps]
devdeps-oblogmsg-1.1-52024052811.el7.x86_64.rpm
devdeps-rocksdb-6.22.1.1-52022100420.el7.x86_64.rpm
obstack-2.0.4-172024070513.el7.x86_64.rpm
#obshell-4.2.4.2-12024102115.el7.x86_64.rpm target=community

二、oblogminer使用

1、完整解析

##默认解析出的格式为csv
/usr/local/bin/oblogminer -c "192.168.150.116:2881" -u "root@ob_mysql" -p "oracle123" -s "2024-10-29 14:55:19" -o "file:///home/admin/logm_out/FULL"

2、完整解析为json

##适用于程序分析json更直观
/usr/local/bin/oblogminer -c "192.168.150.116:2881" -u "root@ob_mysql" -p "oracle123" -s "2024-10-29 14:55:19" -o "file:///home/admin/logm_out/FULL" -f JSON

3、只解析redo的sql

##适用于数据重做
/usr/local/bin/oblogminer -c "192.168.150.116:2881" -u "root@ob_mysql" -p "oracle123" -s "2024-10-29 14:55:19" -o "file:///home/admin/logm_out/REDO_ONLY" -f REDO_ONLY

4、只解析undo的sql

##适用于回滚数据
/usr/local/bin/oblogminer -c "192.168.150.116:2881" -u "root@ob_mysql" -p "oracle123" -s "2024-10-29 14:55:19" -o "file:///home/admin/logm_out/UNDO_ONLY" -f UNDO_ONLY

tips:undo分析出来的sql是按照执行顺序排列的，如果是过程是单纯insert，delete，update某一项可以直接执行，如果多类操作执行可能会报错，注意甄辩

##原始操作
insert into banjin_flash values (1,'zhangsan','北京');
insert into banjin_flash values (2,'lisi','上海');
insert into banjin_flash values (3,'wangwu','天津');
insert into banjin_flash values (4,'zhaoliu','河北');select now();
update banjin_flash set dizhi = '湖南' where name='lisi';
select now();
delete from banjin_flash;
select now();##解析出的undo
DELETE FROM `test`.`banjin_flash` WHERE `id`=1 LIMIT 1;
DELETE FROM `test`.`banjin_flash` WHERE `id`=2 LIMIT 1;
DELETE FROM `test`.`banjin_flash` WHERE `id`=3 LIMIT 1;
DELETE FROM `test`.`banjin_flash` WHERE `id`=4 LIMIT 1;
UPDATE `test`.`banjin_flash` SET `id`=2, `name`='lisi', `dizhi`='上海' WHERE `id`=2 LIMIT 1;
INSERT INTO `test`.`banjin_flash` (`id`, `name`, `dizhi`) VALUES (1, 'zhangsan', '北京');
INSERT INTO `test`.`banjin_flash` (`id`, `name`, `dizhi`) VALUES (2, 'lisi', '湖南');
INSERT INTO `test`.`banjin_flash` (`id`, `name`, `dizhi`) VALUES (3, 'wangwu', '天津');
INSERT INTO `test`.`banjin_flash` (`id`, `name`, `dizhi`) VALUES (4, 'zhaoliu', '河北');
##如果直接执行，update会报错，表的最终也

5、从归档日志的解析

/usr/local/bin/oblogminer -a "file:///data_ob/archive/"  -s "2024-10-29 14:55:19" -o "file:///home/admin/logm_out/FULL"

6、指定时间段的解析

/usr/local/bin/oblogminer -c "192.168.150.116:2881" -u "root@ob_mysql" -p "oracle123" -s "2024-10-29 14:55:19" --e "2024-10-29 14:55:20" -o "file:///home/admin/logm_out/FULL"

7、带表过滤的解析

/usr/local/bin/oblogminer -c "192.168.150.116:2881" -u "root@ob_mysql" -p "oracle123" -s "2024-10-29 14:55:19"  -o "file:///home/admin/logm_out/FULL"  -l ob_mysql.test.banjin_flash1

三、oblogminer实操

实施分析

[root@ob-1 admin]# /usr/local/bin/oblogminer -c "192.168.150.116:2881" -u "root@ob_mysql" -p "oracle123" -s "2024-10-29 14:55:19" -o "file:///home/admin/logm_out"
succ to open, filename=oblogminer.log, fd=3, wf_fd=2
ObLogMinerAnalyzer init...
ObLogMinerAnalyzer finished to init
ObLogMiner init succeed
ObLogMinerAnalyzer starts
2024-10-29 14:58:06 [>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> ]  99.5%, written records:    23, current rps: 2024-10-29 14:58:07 [>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>] 100.0%, written records:    23, current rps:    23, average rps:    23
ObLogMinerAnalyzer completed process
ObLogMinerAnalyzer took 3.99s to process
ObLogMinerAnalyzer exit...
ObLogMinerAnalyzer destroyed
ObLogMiner destroyed

分析结果


[root@ob-1 logm_out]# ls -lrt
total 16
-rw-------. 1 root root  351 Oct 29 14:58 CONFIG
-rw-------. 1 root root 4075 Oct 29 14:58 0.csv
drwx------. 2 root root   20 Oct 29 14:58 META
-rw-------. 1 root root   54 Oct 29 14:58 CHECKPOINT
-rw-------. 1 root root   36 Oct 29 14:58 COMMIT_INDEX
[root@ob-1 logm_out]# more 0.csv 
TENANT_ID,TRANS_ID,PRIMARY_KEY,TENANT_NAME,DATABASE_NAME,TABLE_NAME,OPERATION,OPERATION_CODE,COMMIT_SCN,COMMIT_TIMESTAMP,SQL_REDO,SQL_UNDO,ORG_
CLUSTER_ID
1002,0,"","ob_mysql","test","","DDL",4,1730184919426370000,"2024-10-29 14:55:19.426370","create table banjin_flash (id int ,name varchar(10),di
zhi varchar(10),primary key (id))","/* NO SQL_UNDO GENERATED */",1
1002,6003336,"","","","","BEGIN",5,1730184919458345000,"2024-10-29 14:55:19.458345","","",1
1002,6003336,"id","ob_mysql","test","banjin_flash","INSERT",1,1730184919458345000,"2024-10-29 14:55:19.458345","INSERT INTO `test`.`banjin_flas
h` (`id`, `name`, `dizhi`) VALUES (1, 'zhangsan', '北京');","DELETE FROM `test`.`banjin_flash` WHERE `id`=1 LIMIT 1;",1
1002,6003336,"","","","","COMMIT",6,1730184919458345000,"2024-10-29 14:55:19.458345","","",1
1002,6003337,"","","","","BEGIN",5,1730184919459468000,"2024-10-29 14:55:19.459468","","",1
1002,6003337,"id","ob_mysql","test","banjin_flash","INSERT",1,1730184919459468000,"2024-10-29 14:55:19.459468","INSERT INTO `test`.`banjin_flas
h` (`id`, `name`, `dizhi`) VALUES (2, 'lisi', '上海');","DELETE FROM `test`.`banjin_flash` WHERE `id`=2 LIMIT 1;",1
1002,6003337,"","","","","COMMIT",6,1730184919459468000,"2024-10-29 14:55:19.459468","","",1
1002,6003339,"","","","","BEGIN",5,1730184919460527000,"2024-10-29 14:55:19.460527","","",1
1002,6003339,"id","ob_mysql","test","banjin_flash","INSERT",1,1730184919460527000,"2024-10-29 14:55:19.460527","INSERT INTO `test`.`banjin_flas
h` (`id`, `name`, `dizhi`) VALUES (3, 'wangwu', '天津');","DELETE FROM `test`.`banjin_flash` WHERE `id`=3 LIMIT 1;",1
1002,6003339,"","","","","COMMIT",6,1730184919460527000,"2024-10-29 14:55:19.460527","","",1
1002,6003341,"","","","","BEGIN",5,1730184919462918000,"2024-10-29 14:55:19.462918","","",1
1002,6003341,"id","ob_mysql","test","banjin_flash","INSERT",1,1730184919462918000,"2024-10-29 14:55:19.462918","INSERT INTO `test`.`banjin_flas
h` (`id`, `name`, `dizhi`) VALUES (4, 'zhaoliu', '河北');","DELETE FROM `test`.`banjin_flash` WHERE `id`=4 LIMIT 1;",1
1002,6003341,"","","","","COMMIT",6,1730184919462918000,"2024-10-29 14:55:19.462918","","",1
1002,6003342,"","","","","BEGIN",5,1730184919527086000,"2024-10-29 14:55:19.527086","","",1
1002,6003342,"id","ob_mysql","test","banjin_flash","UPDATE",2,1730184919527086000,"2024-10-29 14:55:19.527086","UPDATE `test`.`banjin_flash` SE
T `id`=2, `name`='lisi', `dizhi`='湖南' WHERE `id`=2 LIMIT 1;","UPDATE `test`.`banjin_flash` SET `id`=2, `name`='lisi', `dizhi`='上海' WHERE `i
d`=2 LIMIT 1;",1
1002,6003342,"","","","","COMMIT",6,1730184919527086000,"2024-10-29 14:55:19.527086","","",1
1002,6003343,"","","","","BEGIN",5,1730184919570190000,"2024-10-29 14:55:19.570190","","",1
1002,6003343,"id","ob_mysql","test","banjin_flash","DELETE",3,1730184919570190000,"2024-10-29 14:55:19.570190","DELETE FROM `test`.`banjin_flas
h` WHERE `id`=1 LIMIT 1;","INSERT INTO `test`.`banjin_flash` (`id`, `name`, `dizhi`) VALUES (1, 'zhangsan', '北京');",1
1002,6003343,"id","ob_mysql","test","banjin_flash","DELETE",3,1730184919570190000,"2024-10-29 14:55:19.570190","DELETE FROM `test`.`banjin_flas
h` WHERE `id`=2 LIMIT 1;","INSERT INTO `test`.`banjin_flash` (`id`, `name`, `dizhi`) VALUES (2, 'lisi', '湖南');",1
1002,6003343,"id","ob_mysql","test","banjin_flash","DELETE",3,1730184919570190000,"2024-10-29 14:55:19.570190","DELETE FROM `test`.`banjin_flas
h` WHERE `id`=3 LIMIT 1;","INSERT INTO `test`.`banjin_flash` (`id`, `name`, `dizhi`) VALUES (3, 'wangwu', '天津');",1
1002,6003343,"id","ob_mysql","test","banjin_flash","DELETE",3,1730184919570190000,"2024-10-29 14:55:19.570190","DELETE FROM `test`.`banjin_flas
h` WHERE `id`=4 LIMIT 1;","INSERT INTO `test`.`banjin_flash` (`id`, `name`, `dizhi`) VALUES (4, 'zhaoliu', '河北');",1

结果介绍官网的比较详细，可以移步官网：

https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001429572

http://www.xdnf.cn/news/1023625.html

相关文章：

  • Mysql 函数concat、concat_ws和group_concat
  • MySQL的对表对整库备份脚本
  • Elasticsearch 常用命令（未完成）
  • python中的文件操作处理：文本文件的处理、二进制文件的处理
  • 心之眼 豪华中文 免安 离线运行版
  • 大模型记忆相关(MemoryOs)
  • kafka Tool (Offset Explorer)使用SASL Plaintext进行身份验证
  • cinematic-gaussians
  • 【RAG+读代码】学术文档解析工具Nougat
  • DeepSeek 引领前端开发变革：AI 助力学习与工作新路径
  • 基于STM32手势识别智能家居系统
  • 抖音AI数字人对口型软件LatentSync最新版整合包，音频驱动口型讲话
  • echarts图封装 自动切换 大屏 swiper 切换里面放echarts图,注意不要开循环 否则出不来
  • 图像处理算法的学习笔记
  • SpringBoot的Web应用开发——Web缓存利器Redis的应用！
  • 【UEFI系列】PEI阶段讲解
  • 生产环境LVM存储降级方案
  • Python训练营---DAY53
  • Git 前后端 Java Vue 项目的 .gitignore 配置分享
  • Linux环境下安装和使用RAPIDS平台的cudf和cuml - pip 安装方法
  • java集合（八） ---- Vector 类
  • 电磁铁性能检测所需工具
  • DataGrip 安装和连接Mysql
  • Eslint、Prettier、.vscode 配置
  • 阳台光伏新风口!安科瑞ADL200N-CT/D16-WF防逆流电表精准护航分布式发电
  • NLP学习路线图（四十三）：零样本学习
  • 分布式爬虫系统设计与实现：跨节点MySQL存储方案
  • 导出支付宝账单步骤
  • 3款工具打造递进图：快速入门与个性化定制的实用指南
  • 帆软报表超级链接将查询控件的参数传递到子页面查询控件上