How to Replicate Data Between Oracle and MySQL Database? (Doc ID 1605674.1)
How to Replicate Data Between Oracle and MySQL Database? (Doc ID 1605674.1)
GOAL
This note describes a set up of a GoldenGate replication between Oracle and MySQL
SOLUTION
Please make sure you have installed GoldenGate on the Oracle and the MySQL server. First let's start with the configuration of GoldenGate on the MySQL machine.
1. Pre-requisites
So let's start ggsci: ./ggsci
and in ggsci let's create the subdirectories:
1 2 3 4 5 6 7 8 9 10 11 12 13 | GGSCI 1> create subdirs Creating subdirectories under current directory /var/lib/mysql/ggs Parameter files /var/lib/mysql/ggs/dirprm: already exists Report files /var/lib/mysql/ggs/dirrpt: created Checkpoint files /var/lib/mysql/ggs/dirchk: created Process status files /var/lib/mysql/ggs/dirpcs: created SQL script files /var/lib/mysql/ggs/dirsql: created Database definitions files /var/lib/mysql/ggs/dirdef: created Extract data files /var/lib/mysql/ggs/dirdat: created Temporary files /var/lib/mysql/ggs/dirtmp: created Stdout files /var/lib/mysql/ggs/dirout: created |
2. GoldenGate Manager
The next step is to configure a basic GoldenGate manager process:
1 2 3 4 5 6 7 | GSCI (server) 2> edit param mgr PORT 7806 and start it: GGSCI 3> start mgr |
(SIDE NOTE - I have to use 7806 port here as my replicat and extract will be running on the same machine...)
We do the same steps now for the GG side that will connect to the Oracle database and use here now the port 7809.
So at the end we have now 2 manager processes running - one for the Oracle GG extract and the other one for the MySQL replicat.
3. GoldenGate demo tables
For a basic replication we create the demo tables in Oracle and MySQL using the scripts shipped with the installation.
3.1 The demo tables for the Oracle database
connect to the Oracle database and execute the demo_ora_create and demo_ora_insert script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> @demo_ora_create SQL> @demo_ora_insert.sql 1 row created. 1 row created. 1 row created. 1 row created. Commit complete. SQL> select count(*) from tcustmer; COUNT(*) \---------- 2 |
3.2 Creating the MySQL demo tables
Connect to the MySQL database and execute demo_mysql_create.sql script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | -bash-3.2$ mysql gateway -u <your MySQl user> -p<your MySQL user's password' <demo_mysql_create.sql which will create the TCUSTMER table: mysql> describe gateway.TCUSTMER; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | CUST_CODE | varchar(4) | NO | PRI | NULL | | | NAME | varchar(30) | YES | | NULL | | | CITY | varchar(20) | YES | | NULL | | | STATE | char(2) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec) |
4. Oracle database pre-requisites
4.1 Verify Oracle DB connection
In my Oracle database I have a user mysql with password
1 2 3 4 5 6 7 8 9 10 | GGGSCI 1> dblogin userid mysql, password <passwd> Successfully logged into database. and check the table list: GGSCI 4> list tables * MYSQL.TCUSTMER MYSQL.TCUSTORD |
Found 2 tables matching list criteria.
4.2 Add the TRANDATA
to be able to capture the changes for the table we need to add trandata:
1 2 3 4 | GGSCI 7> add trandata MYSQL.TCUSTMER Logging of supplemental redo data enabled for table MYSQL.TCUSTMER. |
5. GoldenGate initial load (no data pump process configured to keep it simple)
The Oracle table already contains some data, so let's define an initial load process to first replicate the data to the target:
1 2 3 4 5 6 7 8 9 10 11 12 13 | GGSCI 8> edit param emini EXTRACT emini SETENV (ORACLE_SID=db1124) SETENV (ORACLE_HOME=/home/oracle/product/11.2.0.4/db_gtw) USERID mysql, PASSWORD <passwd> RMTHOST remote_server, MGRPORT 7806 RMTTASK REPLICAT, GROUP RMINI TABLE mysql.TCUSTMER; GGSCI 9> ADD EXTRACT emini, SOURCEISTABLE EXTRACT added. |
6. The SOURCEDEFS file
when replicating data between an Oracle and a foreign database we also need to create the SOURCEDEFS file which requires a definition generator parameter file. It is created in ggsci containing the username and password as well as the table we want to map.
6.1 DEFGEN parameter file
1 2 3 4 5 | GGSCI 10> edit param defgen DEFSFILE ./dirsql/GGMySQLTEST.sql USERID mysql, password <passwd> TABLE MYSQL.TCUSTMER; |
6.2 Defgen command
Now we need to exit from the ggsci command line tool and call the defgen executable:
1 2 3 4 5 6 7 8 9 10 11 | [oracle@server ggs]$ ./defgen paramfile ./dirprm/defgen.prm Oracle GoldenGate Table Definition Generator for Oracle Version 11.2.1.0.7 16934304 OGGCORE_11.2.1.0.7_PLATFORMS_130709.1600.1 Linux, x64, 64bit (optimized), Oracle 11g on Jul 18 2013 04:10:02 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved. ... Definitions generated for 1 table in ./dirsql/GGMySQLTEST.sql |
6.3 COPY DEFGEN file
The defgen file was created in the installation directory from the extract process and we need to copy it to the target GoldenGate installation.
Copy DEFGEN File ./dirsql/GGMySQLTEST.sql to TARGET machine into ./dirsql/GGMySQLTEST.sql
7. MySQL initial load replicat process
We already created a process that extracts the source table data and directly pushes the extracted data to the target machine/direcory. So we skipped the PUMP process that is commonly getting the data from the extract and pushing it to the source and need to configure now a replicat process only to read the extracted data:
1 2 3 4 5 6 7 8 9 10 11 12 13 | GGSCI 1> edit param rmini REPLICAT rmini SOURCEDEFS ./dirsql/GGMySQLTEST.sql TARGETDB user@server:port, USERID root, PASSWORD <passwd> DISCARDFILE ./dirrpt/drmini.dsc, purge MAP MYSQL.TCUSTMER, TARGET gateway.TCUSTMER; Then start the replicat: GGSCI 2> ADD REPLICAT rmini, SPECIALRUN REPLICAT added. |
8. Start Initial Load Extract process
There's already a replicat process running and waiting for the data from the initial load extract process. So let's start it...
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | GGSCI 3> start extract emini Sending START request to MANAGER ... EXTRACT EMINI starting Checking out the status on th replicat side shows: GGSCI 1> view report emini .... *********************************************************************** \* ** Run Time Statistics ** * *********************************************************************** Report at 2013-12-04 06:59:29 (activity since 2013-12-04 06:59:23) Output to RMINI: From Table MYSQL.TCUSTMER: \# inserts: 2 \# updates: 0 \# deletes: 0 \# discards: 0 REDO Log Statistics Bytes parsed 0 Bytes output 246 |
=> We got 2 records replicated now from the source Oracle database to the target which can be checked out using a MySQL tool as well:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | -bash-3.2$ mysql gateway -u <your MySQL username> -p<your MySQl password> Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 438 Server version: 5.5.27-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2011, 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> select * from TCUSTMER; +-----------+------------------+---------+-------+ | CUST_CODE | NAME | CITY | STATE | +-----------+------------------+---------+-------+ | JANE | ROCKY FLYER INC. | DENVER | CO | | WILL | BG SOFTWARE CO. | SEATTLE | WA | +-----------+------------------+---------+-------+ 2 rows in set (0.00 sec) |
The initial load was working and we can configure also a normal replication to push the changes from the Oracle database to the MySQl database. Again we here do not configure a data pump process to keep it simple.
9. GoldenGate Extract for the Oracle database
9.1 GoldenGate extract parameter file
1 2 3 4 5 6 7 8 9 | GGSCI 8> edit param exmys EXTRACT exmys SETENV (ORACLE_SID=db1124) SETENV (ORACLE_HOME=/home/oracle/product/11.2.0.4/db_gtw) USERID mysql, PASSWORD <passwd> RMTHOST remte_server, MGRPORT 7806 RMTTRAIL ./dirdat/em TABLE mysql.TCUSTMER; |
9.2 Add GoldenGate extract
1 2 3 4 5 6 7 | GGSCI 9> add extract exmys, tranlog, begin now EXTRACT added. GGSCI 10> add rmttrail ./dirdat/em, extract exmys, megabytes 5 RMTTRAIL added. |
9.3 start GoldenGate extract
1 2 3 4 | GGSCI 1> start extract exmys Sending START request to MANAGER ... EXTRACT EXMYS starting |
9.4 Status extract
Checking out the status on the extract side shows a running Manager and Extract process:
1 2 3 4 5 6 | GGSCI 2> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXMYS 00:00:00 00:19:44 |
10 MySQL replicat
10.1 MySQl replicat parameter file
1 2 3 4 5 6 7 | GGSCI 1> edit param rmys REPLICAT rmys SOURCEDEFS ./dirsql/GGMySQLTEST.sql TARGETDB user@server:port, USERID <userid>, PASSWORD <passwd> DISCARDFILE ./dirrpt/dismys.dsc, purge MAP MYSQL.TCUSTMER, TARGET gateway.TCUSTMER; |
10.2 add and start MySQL REPLICAT process
1 2 3 4 5 6 7 8 | GGSCI 2> add replicat rmys, NODBCHECKPOINT, exttrail ./dirdat/em REPLICAT added. GGSCI 1> start rmys Sending START request to MANAGER ... REPLICAT RMYS starting |
10.3 REPLICAT status
1 2 3 4 5 6 | GGSCI 4> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RMYS 00:00:00 00:00:02 |
11. Verify the replication
Extract and replicat process are running. Now let's manipulate the data on the Oracle side:
11.1 Update Oracle table content
1 2 3 4 5 6 7 | SQL> update tcustmer set CITY='Munich' where CITY='DENVER'; 1 row updated. SQL> commit; Commit complete. |
11.2 check replicat statistics
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | Did we get it replicated? GGSCI 3> stats replicat rmys Sending STATS request to REPLICAT RMYS ... Start of Statistics at 2013-12-04 10:06:38. Replicating from MYSQL.TCUSTMER to gateway.TCUSTMER: *** Total statistics since 2013-12-04 07:05:05 *** Total inserts 0.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2013-12-04 07:05:05 *** Total inserts 0.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2013-12-04 10:00:00 *** No database operations have been performed. *** Latest statistics since 2013-12-04 07:05:05 *** Total inserts 0.00 Total updates 1.00 Total deletes 0.00 Total discards 0.00 Total operations 1.00 End of Statistics. |
Looks good - the replicat statistics contain at least an update... let's verify it using a MySQL tool:
1 2 3 4 5 6 7 8 9 | mysql> select * from TCUSTMER; +-----------+------------------+---------+-------+ | CUST_CODE | NAME | CITY | STATE | +-----------+------------------+---------+-------+ | JANE | ROCKY FLYER INC. | Munich | CO | | WILL | BG SOFTWARE CO. | SEATTLE | WA | +-----------+------------------+---------+-------+ 2 rows in set (0.00 sec) |
So the update was replicated from the Oracle source database to the MySQL target.
