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.

[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/

[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

[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)

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

[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> 

To verify the repplication configuration, we will again insert data and check if that data is replicated

[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

--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

[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>