最近遇到两个问题都用到了日志挖掘，一个oracle环境突然ogg挂掉了，开发说两面的表结构不一致，分析少了字段但是表的所有字段都是建表时建的不会是漏发版本的问题，要找出来谁弄的，祭出神器logminer，追溯到是有另外的dba从别的环境复制了表结构，另一个有个生产环境mysql有人误删掉表了，还没有备份，但好在所有的binlog日志都有保存，祭出binlog2sql恢复数据，数据太多有点慢最后用别的方式处理了
在此学习下ob的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_releasecd build_debug##编译 oblogminermake 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
解决：需要安装 m 4的包
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