建网站要花费多少钱,wordpress文章页打不开了,专业 网站设计公司,房产中介做网站本文转载于OceanBase博客博主#xff0c;作者为 banjin。本文观点仅代表作者观点。 背景
近期遇到了两个需要运用日志挖掘技术解决的问题。一个是Oracle环境下的ogg突然出问题了#xff0c;开发指出两个环境的表结构存在差异#xff0c;缺失了某些字段。但是经过分析#… 本文转载于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_debugrelease进入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 targetcommunity
二、oblogminer使用
1、完整解析
##默认解析出的格式为csv
/usr/local/bin/oblogminer -c 192.168.150.116:2881 -u rootob_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 rootob_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 rootob_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 rootob_mysql -p oracle123 -s 2024-10-29 14:55:19 -o file:///home/admin/logm_out/UNDO_ONLY -f UNDO_ONLY
tips:undo分析出来的sql是按照执行顺序排列的如果是过程是单纯insertdeleteupdate某一项可以直接执行如果多类操作执行可能会报错注意甄辩
##原始操作
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 namelisi;
select now();
delete from banjin_flash;
select now();##解析出的undo
DELETE FROM test.banjin_flash WHERE id1 LIMIT 1;
DELETE FROM test.banjin_flash WHERE id2 LIMIT 1;
DELETE FROM test.banjin_flash WHERE id3 LIMIT 1;
DELETE FROM test.banjin_flash WHERE id4 LIMIT 1;
UPDATE test.banjin_flash SET id2, namelisi, dizhi上海 WHERE id2 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 rootob_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 rootob_mysql -p oracle123 -s 2024-10-29 14:55:19 -o file:///home/admin/logm_out/FULL -l ob_mysql.test.banjin_flash1三、oblogminer实操
实施分析
[rootob-1 admin]# /usr/local/bin/oblogminer -c 192.168.150.116:2881 -u rootob_mysql -p oracle123 -s 2024-10-29 14:55:19 -o file:///home/admin/logm_out
succ to open, filenameoblogminer.log, fd3, wf_fd2
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
分析结果 [rootob-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
[rootob-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 id1 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 id2 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 id3 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 id4 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 id2, namelisi, dizhi湖南 WHERE id2 LIMIT 1;,UPDATE test.banjin_flash SET id2, namelisi, dizhi上海 WHERE i
d2 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 id1 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 id2 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 id3 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 id4 LIMIT 1;,INSERT INTO test.banjin_flash (id, name, dizhi) VALUES (4, zhaoliu, 河北);,1结果介绍官网的比较详细可以移步官网
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001429572
