使用goldengate adapter将数据抽取成文件
环境:
角色 ip 部署db 部署ogg
源端 192.168.1.134 mysql5.7 ogg for mysql:12.2
目的端 192.168.1.135 无 ogg adapeter:12.2.0.1
######################源端#########################
1.添加mgr
GGSCI (host134) 25> view params mgr
port 7809
DYNAMICPORTLIST 7900-7950
--autostart mp *
--autorestart ep *, retries 5, waitminutes 3
--purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 5
purgeoldextracts ./dirdat/ep*,usecheckpoints, minkeepdays 1
2.添加抽取进程并启动(这里不抽取ddl)
GGSCI (host134) 23> edit params extep
extract extep
sourcedb db_hxl@192.168.1.134:3306 userid goldengate password mysql
exttrail ./dirdat/ep
discardfile ./dirrpt/extep.dsc,append
TranLogOptions AltLogDest /opt/mysql5727/mysqllog/binlog/binlog.index
--ddl include mapped
--DDL &
--EXCLUDE INSTR 'ANALYZE TABLE' &
--INCLUDE INSTR 'CREATE INDEX' &
--INCLUDE INSTR 'CREATE TABLE' &
--INCLUDE INSTR 'ALTER TABLE' &
--INCLUDE INSTR 'DROP INDEX'
--DDLOPTIONS ADDTRANDATA REPORT
--ddl include all
gettruncates
getupdates
table db_hxl.metric;
GGSCI (localhost.localdomain) 10> add extract extep,tranlog,begin now
EXTRACT added.
GGSCI (localhost.localdomain) 11> add exttrail ./dirdat/ep,extract extep
3.添加投递进程并启动
GGSCI (localhost) 15> edit param dpep
extract dpep
passthru
sourcedb db_hxl@192.168.1.134:3306 userid goldengate password mysql
rmthost 192.168.1.135,mgrport 7809,compress
rmttrail ./dirdat/ep
dynamicresolution
numfiles 3000
table db_hxl.metric;
GGSCI (localhost) 16> add extract dpep ,exttrailsource ./dirdat/ep
GGSCI (localhost) 17> add rmttrail ./dirdat/ep,extract dpep
4.源端生成def文件
GGSCI (localhost) 23> edit param defgen
defsfile ./dirdef/db_hxl.def
sourcedb db_hxl@192.168.1.134:3306 userid goldengate password mysql
table db_hxl.metric;
[root@host134 goldengate12c_for_mysql]# pwd
/u01/goldengate12c_for_mysql
[root@localhost goldengate_mysql]# ./defgen paramfile ./dirprm/defgen.prm
...
Definitions generated for 1 table in ./dirdef/db_hxl.def.
5.拷贝db_hxl.def文件到远程机器
scp /u01/goldengate12c_for_mysql/dirdef/db_hxl.def root@192.168.1.135:/u01/ogg12c_for_adapter/dirdef/
################################目的库#################################
1.添加mgr进程
GGSCI (host135) 33> view params mgr
port 7809
DYNAMICPORTLIST 7900-7950
--autostart ep *
--autorestart ep *, retries 5, waitminutes 3
purgeoldextracts ./dirdat/*, usecheckpoints, minkeepdays 3
ACCESSRULE, PROG *, IPADDR 192.168.1.134, ALLOW
2.拷贝样本文件到dirprm目录
cp /u01/ogg12c_for_adapter/AdapterExamples/file-writer/ffue.properties /u01/ogg12c_for_adapter/dirprm/
3.添加抽取进程并启动
GGSCI> edit param ffwriter
EXTRACT ffwriter
SOURCEDEFS ./dirdef/db_hxl.def
CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU INCLUDEUPDATEBEFORES, PARAMS "./dirprm/ffue.properties"
TABLE db_hxl.metric;
ADD EXTRACT ffwriter, EXTTRAILSOURCE ./dirdat/ep
################################验证########################################
验证
主库写入数据,省略...,待写入完成后在目的库查看文件目录
cd /u01/ogg12c_for_adapter/dirout
[root@host135 dirout]# cat pump_db_hxl_metric_2023-03-16_14-45-13_00000_data.dsv|wc -l
99999
该目录下生成很多文件
[root@host135 dirout]# ls -al
total 57952
drwxr-x--- 2 94110 42424 4096 Mar 16 14:50 .
drwxrwxr-x 21 94110 42424 4096 Mar 16 14:45 ..
-rw-r----- 1 root root 378 Mar 16 14:50 db_hxl.metric_data.control
-rw-r----- 1 root root 29657323 Mar 16 14:45 pump_db_hxl_metric_2023-03-16_14-45-13_00000_data.dsv
-rw-r----- 1 root root 5338087 Mar 16 14:49 pump_db_hxl_metric_2023-03-16_14-49-24_00001_data.dsv
-rw-r----- 1 root root 5338299 Mar 16 14:49 pump_db_hxl_metric_2023-03-16_14-49-36_00002_data.dsv
-rw-r----- 1 root root 6227893 Mar 16 14:49 pump_db_hxl_metric_2023-03-16_14-49-48_00003_data.dsv
-rw-r----- 1 root root 11270143 Mar 16 14:50 pump_db_hxl_metric_2023-03-16_14-49-59_00004_data.dsv
-rw-r----- 1 root root 1482448 Mar 16 14:50 pump_db_hxl_metric_2023-03-16_14-50-22_00005_data.dsv
-rwxr-x--- 1 94110 42424 38 Mar 12 2013 README.txt
文件配置参数
[root@host134 dirprm]# more ffue.properties
#------------------------
#LOGGING OPTIONS
#------------------------
goldengate.log.logname=ffwriter
goldengate.log.level=INFO
goldengate.log.modules=LOGMALLOC
goldengate.log.level.LOGMALLOC=ERROR
goldengate.log.tostdout=false
goldengate.log.tofile=true
#------------------------
#FLAT FILE WRITER OPTIONS
#------------------------
goldengate.flatfilewriter.writers=dsvwriter
goldengate.userexit.chkptprefix=ffwriter_
#------------------------
# dsvwriter options
#------------------------
dsvwriter.mode=DSV
dsvwriter.rawchars=false
dsvwriter.includebefores=false
dsvwriter.includecolnames=false
dsvwriter.omitvalues=false
dsvwriter.diffsonly=false
dsvwriter.omitplaceholders=false
#dsvwriter.files.onepertable=false
dsvwriter.files.prefix=csv
dsvwriter.files.data.rootdir=./dirout
dsvwriter.files.data.ext=_data.dsv
dsvwriter.files.data.tmpext=_data.dsv.temp
dsvwriter.files.data.rollover.time=10
#dsvwriter.files.data.rollover.size=
dsvwriter.files.data.norecords.timeout=10
dsvwriter.files.control.use=true
dsvwriter.files.control.ext=_data.control
dsvwriter.files.control.rootdir=./dirout
dsvwriter.dsv.nullindicator.chars=<NULL>
dsvwriter.dsv.fielddelim.chars=@ ##自定义分割符号
dsvwriter.dsv.linedelim.chars=\n
dsvwriter.dsv.quotes.chars=dsvwriter.dsv.quotes.escaped.chars=""
dsvwriter.metacols= ##去掉position,txind,opcode,timestamp,schema,table
dsvwriter.metacols.txind.fixedlen=1
dsvwriter.metacols.txind.begin.chars=B
dsvwriter.metacols.txind.middle.chars=M
dsvwriter.metacols.txind.end.chars=E
dsvwriter.files.formatstring=pump_%s_%t_%d_%05n
#------------------------
# ldvwriter options
#------------------------
ldvwriter.mode=LDV
ldvwriter.rawchars=true
ldvwriter.includebefores=false
ldvwriter.includecolnames=false
ldvwriter.files.onepertable=false
ldvwriter.files.data.rootdir=./dirout
ldvwriter.files.data.ext=.data
ldvwriter.files.data.tmpext=.temp
ldvwriter.files.data.rollover.time=10
ldvwriter.files.data.norecords.timeout=10
ldvwriter.files.control.use=true
ldvwriter.files.control.ext=.ctrl
ldvwriter.files.control.rootdir=./dirout
ldvwriter.metacols=position,timestamp,@TOKEN-RBA,@TOKEN-POS,opcode,txind,schema,table
ldvwriter.metacols.TOKEN-RBA.fixedlen=10
ldvwriter.metacols.TOKEN-POS.fixedlen=10
ldvwriter.metacols.timestamp.fixedlen=26
ldvwriter.metacols.schema.fixedjustify=right
ldvwriter.metacols.schema.fixedpadchar.chars=Y
ldvwriter.metacols.opcode.fixedlen=1
ldvwriter.metacols.opcode.insert.chars=I
ldvwriter.metacols.opcode.update.chars=U
ldvwriter.metacols.opcode.delete.chars=D
ldvwriter.metacols.txind.fixedlen=1
ldvwriter.metacols.txind.begin.chars=B
ldvwriter.metacols.txind.middle.chars=M
ldvwriter.metacols.txind.end.chars=E
ldvwriter.metacols.txind.whole.chars=W
ldvwriter.ldv.vals.missing.chars=M
ldvwriter.ldv.vals.present.chars=P
ldvwriter.ldv.vals.null.chars=N
ldvwriter.ldv.lengths.record.mode=binary
ldvwriter.ldv.lengths.record.length=4
ldvwriter.ldv.lengths.field.mode=binary
ldvwriter.ldv.lengths.field.length=2
ldvwriter.files.rolloveronshutdown=false
ldvwriter.statistics.toreportfile=false
ldvwriter.statistics.period=onrollover
ldvwriter.statistics.tosummaryfile=true
ldvwriter.statistics.overall=true
ldvwriter.statistics.summary.fileformat=schema,table,schemaandtable,total,gctimestamp,ctimestamp
ldvwriter.statistics.summary.delimiter.chars=|
ldvwriter.statistics.summary.eol.chars=\n
ldvwriter.metacols.position.format=dec
ldvwriter.writebuffer.size=36863
################################全量数据同步########################################
源端(ogg for mysql)
1.编辑初始化抽取进程参数
GGSCI>edit params ext_init
EXTRACT ext_init
sourcedb db_hxl@192.168.1.134:3306 userid goldengate password mysql
RMTHOST 192.168.1.134, MGRPORT 7819
rmtfile ./dirdat/ei, maxfiles 999999, megabytes 1500, append
TABLE db_hxl.metric;
2.添加抽取进程并启动
GGSCI>add extract ext_init, SOURCEISTABLE
GGSCI>start ext_init
目的端(adapter)
1.创建初始化复制进程
GGSCI>edit params ffwtinit
EXTRACT ffwtinit
--SOURCEDEFS ./dirdef/db_hxl.def
CUSEREXIT ./flatfilewriter.so CUSEREXIT PASSTHRU INCLUDEUPDATEBEFORES, PARAMS "./dirprm/ffue.properties"
TABLE db_hxl.metric;
SOURCEDEFS ./dirdef/db_hxl.def可以去掉
2.启动
add extract ffwtinit, exttrailsource ./dirdat/ei
start ffwtinit
浙公网安备 33010602011771号