Kafka Middleware for synchronizing heterogeneous databases using Goldengate

Receive business unit demand, request to synchronize Oracle database a table to MySQL database, heterogeneous environment we use Kafka to implement, below is the specific configuration;

Due to business needs, we now request to synchronize the following data to the Butler MySQL database using the schema Group data Synchronization Service

Agent User data:
A. Data source: SSP Library aaa.system_user
B. Data target: MySQL DLS library Dls_system_user
C. Synchronization logic: None
D. Synchronizing data and correspondence: see Accessories
E. Whether sensitive information is involved: no

Prepare for work; Because the table already exists in the MySQL library of the target library, we back up the table and get the table statement;
--Get the Build table statement
Mysql> Show CREATE TABLE Dls_system_user;

--Export single data table structure and data
Mysqldump-uroot-p DLs Dls_system_user > Dls_system_user_180622.sql

--Renaming a table
ALTER TABLE dls_system_userrename dls_system_user_bak0622;

--New empty table
CREATE TABLE dls_system_user (
IDvarchar (+) is not NULL,
ACCOUNT_EXPIREDInt (1) not NULL DEFAULT ' 0 ',
ACCOUNT_LOCKEDInt (1) not NULL DEFAULT ' 0 ',
ENABLEDInt (1) not NULL DEFAULT ' 0 ',
ORG_NOvarchar (255) Not NULL DEFAULT ' ',
USER_CODEvarchar (+) not NULL DEFAULT ' ',
REMARK_NAMEvarchar (255) Not NULL DEFAULT ' ',
IS_CREATE_PERSONvarchar (255) Not NULL DEFAULT ' ',
STATUSInt (ten) not NULL DEFAULT ' 0 ',
PRIMARY KEY ( ID ),
KEY IDX_DLS_SYSTEM_USER_USER_CODE ( USER_CODE )
) Engine=innodb DEFAULT charset=utf8mb4;

Oracle Source-Side Goldengate configuration:
1. Add additional logs for the tables to be synchronized
dblogin USERID [email protected], password GGs
Add Trandata Aaa.system_user

2. Add Extraction Process
Add extract EXT_KAFB, Tranlog, begin now
Add Exttrail./DIRDAT/A2, extract ext_kafb,megabytes 200

Edit params EXT_KAFB

Extract EXT_KAFB
USERID [email protected], password GGs
Logallsupcols
Exttrail./dirdat/a2,format RELEASE 11.2
Table Aaa.system_user;

3, add the delivery process:
Add extract PMP_KAFB, Exttrailsource./dirdat/a2
Add Rmttrail./dirdat/b2,extract pmp_kafb,megabytes 200

Eidt params PMP_KAFB

EXTRACT PMP_KAFB
USERID [email protected], password GGs
PASSTHRU
Rmthost 172.16.xxx.5, Mgrport 9178--kafka server address
Rmttrail./dirdat/b2,format Release 11.2
Table Aaa.system_user;

----initialization files are stored in/ggs/ggs12/dirprm/

4. Add initialization process
Add EXTRACT ek_20, sourceistable---source side added

Edit params ek_20

EXTRACT ek_20
USERID [email protected], password GGs
Rmthost 172.16.154.5, Mgrport 9178
Rmtfile./dirdat/lb,maxfiles 999, megabytes 500
Table Aaa.system_user;

5. Generate DEF file:
ggsci> edit param Defgen_n9

USERID [email protected], password GGs
Defsfile/goldengate/ggskafka/dirdef/defgen_n9.def,format Release 11.2
Table Aaa.system_user;

Under Ogg_home, execute the following command to generate the Def file
Defgen PARAMFILE/GOLDENGATE/GGSKAFKA/DIRPRM/DEFGEN_N9.PRM

Upload the generated def file to the Kafka server $ogg_home/dirdef

---target MySQL database address 172.16.xxx.148, you need to create a new Kafka user

Grant Select,insert,update,delete,create,drop on dls.* to ' Kafka ' @ ' percent ' identified by ' Jiubugaosuni ';

--kafka Server goldengate operation
1. Add initialization process:---DIRPRM
ggsci> ADD Replicat Rn_3,specialrun

EDIT PARAMS Rn_3

Specialrun
End Runtime
Setenv (nls_lang= "American_america. ZHS16GBK ")
Targetdb libfile libggjava.so Set Property=./dirprm/kafkat_n3.props
Sourcedefs./dirdef/defgen_n9.def
Extfile./dirdat/lb
Reportcount Every 1 minutes, rate
Grouptransops 10000
MAP Aaa.system_user, TARGET DLS. Dls_system_user;

2. Add the replication process:
Ggsci>add replicat Rn_kf3,exttrail./dirdat/lb
Ggsci>edit params rn_kf3

Replicat RN_KF3
Setenv (nls_lang= "American_america. ZHS16GBK ")
Handlecollisions
Targetdb libfile libggjava.so Set Property=./dirprm/kafkat_n3.props
Sourcedefs./dirdef/defgen_n9.def
Reportcount Every 1 minutes, rate
Grouptransops 10000
MAP Aaa.system_user, TARGET DLS. Dls_system_user;

3, parameter configuration:
Cd/home/app/ogg/ggs12/dirprm

The contents of the Custom_kafka_producer.properties file are as follows:

[Email protected] dirprm]$ more custom_kafka_producer.properties
bootstrap.servers=172.16.xxx.5:9092,172.16.xxx.7:9092
Acks=1
reconnect.backoff.ms=1000

Value.serializer=org.apache.kafka.common.serialization.bytearrayserializer
Key.serializer=org.apache.kafka.common.serialization.bytearrayserializer

100KB per partition

batch.size=16384
Linger.ms=0

---vi add the corresponding file Kafkat_n3.props
The contents of the Kafka.props file are as follows:
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= DLS. Dls_merchant_status
#gg. Handler.kafkahandler.format=avro_op
Gg.handler.kafkahandler.format =json--Specifying file types
Gg.handler.kafkahandler.format.insertopkey=i
Gg.handler.kafkahandler.format.updateopkey=u
Gg.handler.kafkahandler.format.deleteopkey=d
Gg.handler.kafkahandler.format.truncateopkey=t
Gg.handler.kafkahandler.format.prettyprint=false
Gg.handler.kafkahandler.format.jsondelimiter=cdata[]
Gg.handler.kafkahandler.format.includeprimarykeys=true
Gg.handler.kafkahandler.schematopicname= DLS. Dls_merchant_status--Specify topic name
Gg.handler.kafkahandler.BlockingSend =false
Gg.handler.kafkahandler.includetokens=false
Gg.handler.kafkahandler.mode=op
Goldengate.userexit.timestamp=utc
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=dirprm/:/opt/cloudera/parcels/kafka/lib/kafka/libs/--patch Path
#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

At this point our configuration is basically complete, now we have to open the process, initialize the data;
1, start the source-side crawl process
ggsci> Start EXT_KAFB
2. Start the source-side delivery process
ggsci> Start PMP_KAFB
3. Start the source-side initialization process
ggsci> Start ek_20
4. Start the target-side initialization process
ggsci> Start Rn_3
Under $ogg_home, execute the following command:

./replicat paramfile./DIRPRM/RN_3.PRM reportfile./dirrpt/rn_3.rpt-p initialdataload
5. Start the target-side recovery process
ggsci> Start RN_KF3

Kafka Middleware for synchronizing heterogeneous databases using Goldengate