ogg同步mysql数据到greenplum

源端：

ogg的安装，可以在官网直接进行下载解压到相应目录，地址：https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html 版本为12.3.0.1.5

mysql配置环境变量：

export GG_HOME=/home/ogg/
export PATH=$PATH:$GG_HOME
export LD_LIBRARY_PATH=$OG{G}_{H}OME:$JAVA_HOME/jre/lib/amd64/libjsig.so:$JAV{A}_{H}OME/jre/lib/amd64/server/libjvm.so:$JAVA_HOME/jre/lib/amd64/server:$JAVA_HOME/jre/lib/amd64

mysql开启bin-log
在my.conf配置文件中添加

[mysqld]
server_id=1
log-bin=/home/mysql3307/mysql-5.6.33/logs/log-bin
binlog_format=row

重启mysql

进入ogg安装目录,执行以下命令：
进入ogg

ggsci

创建子目录

create subdirs

编辑抽取进程

edit param ext_cshy

以下为内容：

EXTRACT ext_cshy
setenv (MYSQL_HOME="/home/mysql3307/mysql-5.6.33")
setenv (NLS_LANG=“AMERICAN_AMERICA.UTF8”)
tranlogoptions altlogdest /home/mysql3307/mysql-5.6.33/data/slave3307_bin.index
sourcedb xxxx@192.168.x.xx:xxxx USERID xxxx, PASSWORD xxxx
DISCARDROLLOVER ON FRIDAY
DISCARDFILE /home/ogg/dirrpt/ext_cshy.dsc, APPEND, MEGABYTES 1024
REPORTCOUNT EVERY 1 MINUTES, RATE
GETUPDATEBEFORES
UPDATERECORDFORMAT COMPACT
GETTRUNCATES
EXTTRAIL /home/ogg/dirdat/cs
table aisino.wechat_mp_order;
table aisino.wechat_mp_order_item;

添加抽取进程

add extract ex_test,tranlog,begin now
add exttrail /home/ogg/dirdat/te,extract ex_test

编辑投递进程

edit param pum_cshy

以下为内容：

EXTRACT pum_cshy
SETENV (NLS_LANG = “AMERICAN_AMERICA.UTF8”)
PASSTHRU
RMTHOST 192.168.x.xx, MGRPORT 7809
RMTTRAIL /data/ogg/dirdat/cs
table aisino.wechat_mp_order;
table aisino.wechat_mp_order_item
add extract dp_test,exttrailsource /opt/ogg/dirdat/te

添加投递进程(路径为投递至远程的目录路径)

add rmttrail /opt/ogg/dirdat/te,extract dp_test

编辑映射文件

edit param def_cshy

以下为内容：

defsfile /home/ogg/dirdef/def_cshy.prm
sourcedb xxxx@192.168.x.xx:xxxx, userid xxxx,password xxxx
table aisino.wechat_mp_order;
table aisino.wechat_mp_order_item;

退出ogg

exit

通过映射配置文件生成对应的描述文件

./defgen paramfile ./dirprm/def_cshy.prm

拷贝./dirdef下的defgen.prm到目标端${OGG_HOME}/dirdef下

目标端：

安装ogg 版本为12.3.0.1.2

编辑复制进程

edit rep_test

以下为内容：

REPLICAT rep_cshy
TARGETDB LIBFILE libggjava.so SET property=/data/ogg/dirprm/kafka_cshy.props
SOURCEDEFS /data/ogg/dirdef/def_cshy.prm
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 10000
GETTRUNCATES
MAP aisino., TARGET cshy.;

添加复制进程

add replicat rep_test,exttrail /opt/ogg/dirdat/te,checkpointtable ogg.checkpoint

配置kafka配置文件dirprm/kafka_cshy.props
以下为内容：

gg.handlerlist = kafkahandler
gg.handler.kafkahandler.type=kafka
gg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties
#The following resolves the topic name using the short table name
gg.handler.kafkahandler.topicMappingTemplate=ogg_cshy
#The following selects the message key using the concatenated primary keys
gg.handler.kafkahandler.format.includePrimaryKeys=true
gg.handler.kafkahandler.format=json
gg.handler.kafkahandler.BlockingSend =false
gg.handler.kafkahandler.includeTokens=false
gg.handler.kafkahandler.mode=op
goldengate.userexit.writers=javawriter
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE
gg.log=log4j
gg.log.level=INFO
gg.report.time=30sec
#Sample gg.classpath for Apache Kafka
gg.classpath=/data/ogg/dirprm/:/home/gpadmin/software/kafka_2.12-2.1.0/libs/:/data/ogg/:/data/ogg/lib/
#Sample gg.classpath for HDP
#gg.classpath=/etc/kafka/conf:/usr/hdp/current/kafka-broker/libs/*
javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=ggjava/ggjava.jar

启动
源端：

ggsci
strat ext_cshy
start pum_cshy

目的端：

ggsci
start rep_cshy

可以使用view report 进程名 查看启动日志

kafka到greenplum需要编写同步程序