OGG同步异构数据库-表字段变更重新读取异构文件测试验证

OGG同步异构数据库-表字段变更重新读取异构文件测试验证

删除前源和目标端的同步情况：

配置文件信息：
源端：
GGSCI (ITSMdoc-236-63) 4> view param etest

extract etest
setenv (MYSQL_HOME=“/data/mysql-5.7.26”)
tranlogoptions altlogdest /data/mysql-5.7.26/itsm-binlog.index
sourcedb testdb@localhost:13306, userid ogg, password Bai_yun123
DISCARDFILE ./dirrpt/etest.dsc,append,megabytes 1024
DISCARDROLLOVER AT 05:30 ON MONDAY
exttrail ./dirdat/e6

table testdb.t_user;

GGSCI (ITSMdoc-236-63) 5> view param ptest

extract ptest
rmthost 10.10.236.193, mgrport 7839
rmttrail /ogg213/dirdat/e6
passthru

table testdb.t_user;

目标端：
GGSCI (ITSMbak-236-193) 4> view param rtest

replicat rtest

sourcedefs ./dirdef/defmatter.prm OVERRIDE

userid ogg@cdc,password Bai_yun123
reperror default, discard
discardfile ./dirrpt/rtest.dsc, append, megabytes 4096
DISCARDROLLOVER AT 05:30 ON MONDAY
APPLYNOOPUPDATES

MAP testdb.t_user target testdb.t_user;

同步进程状态：
GGSCI (ITSMdoc-236-63) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING ETEST 00:00:00 00:00:04
EXTRACT RUNNING PTEST 00:00:00 00:00:06

目标端：
GGSCI (ITSMbak-236-193) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING RTEST 00:00:00 00:00:08

原字段信息：t_user
id int(11)
c_user_id varchar(36)
c_name varchar(22)
c_province_id int(11)
c_city_id int(11)
create_time datetime

源端删除表字段：

ALTER TABLE t_user DROP c_city_id;

源端测试插入数据：

insert into t_user values(1,‘57c29816-93fc-11ee-a2c1-0cda411d520c’,‘WLOCtmcL62KrMqEQ3AEj’,203,‘2023-12-06 13:57:30’);

select * from t_user tu
where id=1;

再次查看同步进程，进程abended了。
GGSCI (ITSMdoc-236-63) 9> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT ABENDED ETEST 00:00:00 00:00:43

GGSCI (ITSMdoc-236-63) 10> info etest

Extract ETEST Last Started 2023-12-06 14:20 Status ABENDED
Checkpoint Lag 00:00:00 (updated 00:01:59 ago)
VAM Read Checkpoint 2023-12-22 11:30:40.000000
Log Number: 144
Record Offset: 184825773

隔了一个多小时再次查看进程：竟然神奇般的可以了。期间什么操作都没修改。
GGSCI (ITSMbak-236-193) 16> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING RTEST 00:00:00 00:00:01

目标端查看数据也确实同步过来了。只不过删除的字段为NULL值。

源端再次插入数据，测试验证。
insert into t_user values(2,‘57c29816-93fc-11ee-a2c1-0cda411d520c’,‘WLOCtmcL62KrMqEQ3AEj’,203,‘2023-12-06 13:57:30’);

目标端再次查看，数据同步过来了。

目标端参数：
GGSCI (ITSMbak-236-193) 18> view param rtest

replicat rtest

sourcedefs ./dirdef/defmatter.prm OVERRIDE

userid ogg@cdc,password Bai_yun123
reperror default, discard
discardfile ./dirrpt/rtest.dsc, append, megabytes 4096
DISCARDROLLOVER AT 05:30 ON MONDAY
APPLYNOOPUPDATES

MAP testdb.t_user target testdb.t_user;

override. 此参数的作用，ogg 进行元数据核对时，使用定义文件中的内容， 而不是使用OGG REPLICAT 默认的trail中的meta信息。没有此参数，replicat 进程 仍会默认读取trail中的meta信息，发现实际数据长度大于meta中定义的长度，就会报错。

但是def文件也没有重新定义，为啥就自动同步了呢？没搞懂。

继续测试

源端添加字段：
ALTER TABLE t_user add c_city_id2 int(11);

源端测试插入数据
insert into t_user values(3,‘57c29816-93fc-11ee-a2c1-0cda411d520c’,‘WLOCtmcL62KrMqEQ3AEj’,203,‘2023-12-06 13:57:30’,208);
insert into t_user values(4,‘57c29816-93fc-11ee-a2c1-0cda411d520c’,‘WLOCtmcL62KrMqEQ3AEj’,203,‘2023-12-06 13:57:30’,208);
insert into t_user values(5,‘57c29816-93fc-11ee-a2c1-0cda411d520c’,‘WLOCtmcL62KrMqEQ3AEj’,203,‘2023-12-06 13:57:30’,208);

再次查看进程信息，进程状态abended了。
GGSCI (ITSMdoc-236-63) 22> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT ABENDED ETEST 00:00:00 00:01:36

过5分钟再次刷新，进程又自动起来了。
GGSCI (ITSMdoc-236-63) 24> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING ETEST 00:00:00 00:00:03

目标端查看数据，数据确实又同步进来了。只是没有新增加的字段

目标端停止进程：
GGSCI (ITSMbak-236-193 as ogg@cdc) 35> stop rtest

Sending STOP request to Replicat group RTEST …
Request processed.

源端更新def文件并传送到目标端：
./defgen paramfile dirprm/deftest.prm

目标端重启进程：
GGSCI (ITSMbak-236-193) 2> start rtest

Sending START request to Manager …
Replicat group RTEST starting.

GGSCI (ITSMbak-236-193) 5> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING RTEST 00:00:00 00:00:05

验证数据同步，数据正常同步：