Configure Destination
In this section we will define Generic JDBC Adapter for MySQL replication, the steps are similar as the previous:
- JDBC Drivers
- MySQL
- Properties File
- Golden Gate (replicat)
So, let's get going.
Configure JDBC Drivers
Configure JDBC Driver
[oracle@edvmr1p0 practice08]$ cd /stage [oracle@edvmr1p0 stage]$ mkdir mysql_jdbc [oracle@edvmr1p0 stage]$ cp mysql-connector-java-5.1.40.tar.gz mysql_jdbc/ [oracle@edvmr1p0 stage]$ cd mysql_jdbc/ [oracle@edvmr1p0 mysql_jdbc]$ tar xzvf mysql-connector-java-5.1.40.tar.gz mysql-connector-java-5.1.40/ mysql-connector-java-5.1.40/docs/ mysql-connector-java-5.1.40/src/ mysql-connector-java-5.1.40/src/com/ mysql-connector-java-5.1.40/src/com/mysql/ mysql-connector-java-5.1.40/src/com/mysql/fabric/ mysql-connector-java-5.1.40/src/com/mysql/fabric/hibernate/ mysql-connector-java-5.1.40/src/com/mysql/fabric/jdbc/ mysql-connector-java-5.1.40/src/com/mysql/fabric/proto/ mysql-connector-java-5.1.40/src/com/mysql/fabric/proto/xmlrpc/
Configure MySQL
Configure my.cnf (e.g. /etc/my.cnf)
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 lower_case_table_names = 1 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
We can start mysql and configure MySQL for the replication
Start & Verify MySQL
[oracle@edvmr1p0 oggtrg]$ service mysqld status
mysqld (pid 3092) is running...
[oracle@edvmr1p0 oggtrg]$
[oracle@edvmr1p0 practice08]$ mysql -u oracle -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use bigdata
Database changed
mysql> create table if not exists customer_prod (
-> customer_id numeric,
-> first_name varchar(50),
-> last_name varchar(100),
-> email_address varchar(132),
-> ssn varchar(20),
-> address varchar(200),
-> city varchar(50),
-> zip_code varchar(10),
-> customer_type numeric);
Query OK, 0 rows affected (0.01 sec)
mysql> create table if not exists product_prod(
-> product_id numeric,
-> prod_code varchar(20),
-> prod_name varchar(132),
-> supplier_id numeric);
Query OK, 0 rows affected (0.00 sec)
Configure Properties File
Configure Properties File(e.g. TRG_OGGHOME/dirprm/jdbc_mysql.properties
gg.handlerlist=jdbc gg.handler.jdbc.type=jdbc gg.handler.jdbc.DriverClass=com.mysql.jdbc.Driver gg.handler.jdbc.connectionURL=jdbc:mysql://localhost:3306/bigdata gg.handler.jdbc.userName=oracle gg.handler.jdbc.password=oracle gg.error.duplicateErrorCodes=1022,1062 gg.error.notFoundErrorCodes=1329 gg.error.deadlockErrorCodes=1213,1614 gg.classpath=/stage/mysql_jdbc/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar 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 jvm.bootoptions=-Xmx512m -Xms32m -Djava.class.path=.:ggjava/ggjava.jar:./dirprm
Configure Golden Gate (replicat)
Configure Golden Gate
[oracle@edvmr1p0 dirprm]$ trg [oracle@edvmr1p0 oggtrg]$ ggsci Oracle GoldenGate Command Interpreter Version 12.2.0.1.160823 OGGCORE_OGGADP.12.2.0.1.0_PLATFORMS_161019.1437 Linux, x64, 64bit (optimized), Generic on Oct 19 2016 16:01:40 Operating system character set identified as UTF-8. Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved. GGSCI (edvmr1p0) 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING GGSCI (edvmr1p0) 2> edit param rjdbc REPLICAT rjdbc TARGETDB LIBFILE libggjava.so SET property=dirprm/jdbc_mysql.properties REPORTCOUNT EVERY 1 MINUTES, RATE GROUPTRANSOPS 1000 MAP OGGSRC.*, TARGET bigdata.*; :wq GGSCI (edvmr1p0) 3> add replicat rjdbc, exttrail ./dirdat/jd REPLICAT added. GGSCI (edvmr1p0) 4> start rjdbc Sending START request to MANAGER ... REPLICAT RJDBC starting GGSCI (edvmr1p0) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RJDBC 00:00:00 00:00:06 GGSCI (edvmr1p0) 6>
Verify
To verify the repplication configuration, we will again insert data and check if that data is replicated
Insert Data into Oracle
[oracle@edvmr1p0 oggsrc]$ sqlplus oggsrc/oracle@orcl SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 12 17:57:25 2020 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time: Thu Nov 12 2020 17:37:43 +00:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> insert into customer_prod select * from customer where customer_id > 1000 and customer_id < 4001; 3000 rows created. SQL> commit; Commit complete. SQL>
Now, we can verify if the Golden Gate Replication Worked
Golden Gate Replication
--Source GGSCI (edvmr1p0) 2> send priex,stats Sending STATS request to EXTRACT PRIEX ... Start of Statistics at 2020-11-12 17:59:35. Output to ./dirdat/in: Extracting from OGGSRC.CUSTOMER_PROD to OGGSRC.CUSTOMER_PROD: *** Total statistics since 2020-11-12 17:59:29 *** Total inserts 3000.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 3000.00 *** Daily statistics since 2020-11-12 17:59:29 *** Total inserts 3000.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 3000.00 *** Hourly statistics since 2020-11-12 17:59:29 *** Total inserts 3000.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 3000.00 *** Latest statistics since 2020-11-12 17:59:29 *** Total inserts 3000.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 3000.00 End of Statistics. GGSCI (edvmr1p0) 3> --Destination GGSCI (edvmr1p0) 6> send rjdbc, stats Sending STATS request to REPLICAT RJDBC ... Start of Statistics at 2020-11-12 17:59:42. Replicating from OGGSRC.CUSTOMER_PROD to bigdata.CUSTOMER_PROD: *** Total statistics since 2020-11-12 17:59:32 *** Total inserts 3000.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 3000.00 *** Daily statistics since 2020-11-12 17:59:32 *** Total inserts 3000.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 3000.00 *** Hourly statistics since 2020-11-12 17:59:32 *** Total inserts 3000.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 3000.00 *** Latest statistics since 2020-11-12 17:59:32 *** Total inserts 3000.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 3000.00 End of Statistics. GGSCI (edvmr1p0) 7>
Finally, we can check if the data is replicated on the MySQL
Verify MySQL
[oracle@edvmr1p0 oggtrg]$ mysql -uoracle -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.1.73 Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bigdata | | test | +--------------------+ 3 rows in set (0.00 sec) mysql> use bigdata Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select count(*) from customer_prod; +----------+ | count(*) | +----------+ | 3000 | +----------+ 1 row in set (0.00 sec) mysql> select customer_id, first_name, last_name, city from customer_prod where customer_id < 1011; +-------------+------------+-----------+--------------------------+ | customer_id | first_name | last_name | city | +-------------+------------+-----------+--------------------------+ | 1001 | Alisa | Vega | Calle Blancos | | 1002 | Inga | Griffin | San Diego | | 1003 | Sopoline | Cooke | Ip��s | | 1004 | Maile | Beasley | San Pedro | | 1005 | Asher | Glenn | Curridabat | | 1006 | Rosalyn | Mcleod | San Isidro de El General | | 1007 | September | Lloyd | Puntarenas | | 1008 | Magee | Higgins | San Felipe | | 1009 | Pascale | Kramer | Ulloa (Barrial) | | 1010 | Melodie | Fox | San Pablo | +-------------+------------+-----------+--------------------------+ 10 rows in set (0.01 sec) mysql>
Replication with Different Table Structure
What if we want to replicate between two tables which have different structure:
Table Differences
--Source: SQL> desc product_prod Name Null? Type ----------------------------------------- -------- ---------------------------- PRODUCT_ID NOT NULL NUMBER(12) PRODUCT_CODE NOT NULL VARCHAR2(20) PRODUCT_NAME NOT NULL VARCHAR2(132) SUPPLIER_ID NOT NULL NUMBER(12) --Destination: mysql> describe product_prod; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | product_id | decimal(10,0) | YES | | NULL | | | prod_code | varchar(20) | YES | | NULL | | | prod_name | varchar(132) | YES | | NULL | | | supplier_id | decimal(10,0) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
In our case the product_id and product_code on the source are different than the prod_name and prod_code on the destination, therefore if we try to add data on the source:
Try to add data on Prod
SQL> insert into product_prod select * from product where product_id < 100; 99 rows created. SQL> commit; Commit complete. SQL>
The Golden Gate Replication Replicat will ABEND:
Golden Gate Replicat (Abend)
GGSCI (edvmr1p0) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED RJDBC 00:00:00 00:00:12
GGSCI (edvmr1p0) 9> view report rjdbc
------------------------------------------------------------------------
: [/u01/app/oggtrg/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreadin
g::Thread::ThreadArgs*)+0x147) [0x762e57]]
: [/u01/app/oggtrg/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x12d) [0x7634
6d]]
: [/u01/app/oggtrg/replicat(main+0x3b) [0x634b2b]]
: [/lib64/libc.so.6(__libc_start_main+0xfd) [0x370421ed1d]]
: [/u01/app/oggtrg/replicat() [0x5238e9]]
2020-11-12 18:10:16 ERROR OGG-01296 Error mapping from OGGSRC.PRODUCT_PROD to bigdata.PRODUCT_PROD.
So in order to fix that we have to do 2 things:
- Fix the jdbc properties file, to allow mapping of column with different names
- Map these columns in the rjdbc parameter file
So let's do it:
Modify JDBC Properties File
Make sure to add the following parameters, to the existing configuration.
Modify JDBC Properties File
gg.mdp.type=jdbc gg.mdp.ConnectionUrl=jdbc:mysql://localhost:3306/bigdata gg.mdp.DriverClassName=com.mysql.jdbc.Driver gg.mdp.UserName=oracle gg.mdp.Password=<password for oracle user>
Modify Replicat Param File
Now we can modify the RJDBC param file:
Modify Replicat param file
[oracle@edvmr1p0 dirprm]$ trg [oracle@edvmr1p0 oggtrg]$ ggsci Oracle GoldenGate Command Interpreter Version 12.2.0.1.160823 OGGCORE_OGGADP.12.2.0.1.0_PLATFORMS_161019.1437 Linux, x64, 64bit (optimized), Generic on Oct 19 2016 16:01:40 Operating system character set identified as UTF-8. Copyright (C) 1995, 2016, Oracle and/or its affiliates. All rights reserved. GGSCI (edvmr1p0) 1> edit param rjdbc REPLICAT rjdbc TARGETDB LIBFILE libggjava.so SET property=dirprm/jdbc_mysql.properties REPORTCOUNT EVERY 1 MINUTES, RATE GROUPTRANSOPS 1000 MAP OGGSRC.CUSTOMER_PROD, TARGET bigdata.customer_prod; MAP OGGSRC.PRODUCT_PROD, TARGET bigdata.product_prod, COLMAP(USEDEFAULTS, prod_code=PRODUCT_CODE,prod_name=PRODUCT_NAME); :wq
Verify the Replication
Now, we can start the RJDBC and verify if the rows were replicated:
Verify the Replication
GGSCI (edvmr1p0) 3> start rjdbc Sending START request to MANAGER ... REPLICAT RJDBC starting GGSCI (edvmr1p0) 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT STARTING RJDBC 00:00:00 00:09:34 GGSCI (edvmr1p0) 5> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RJDBC 00:00:00 00:00:02 GGSCI (edvmr1p0) 7> send rjdbc,stats Sending STATS request to REPLICAT RJDBC ... Start of Statistics at 2020-11-12 18:19:49. Replicating from OGGSRC.PRODUCT_PROD to bigdata.product_prod: *** Total statistics since 2020-11-12 18:19:41 *** Total inserts 99.00 Total updates 0.00 Total deletes 0.00 Total discards 0.00 Total operations 99.00
We see that 99 inserts were processed, we can check of course also the MySQL database:
Verify MySQL Replication
mysql> select count(*) from product_prod; +----------+ | count(*) | +----------+ | 99 | +----------+ 1 row in set (0.00 sec) mysql>