top of page
DBA Genesis Logo

Oracle to MYSQL Replication Using Golden Gate

Set up Oracle-to-MySQL data replication with GoldenGate.

In this article, we will perform single table DML replication from Oracle database (Linux) to MySQL database (Linux) using Oracle Golden Gate.

oracle to mysql replication using golden gate configuration overview


Setup Source Server (Oracle)


We will be using GGPROD (already created) as the source database. FOX.TAB1 table from GGPROD (oracle database) will be replicated to target table TAB1 on MySQL database.


Also, we have Golden Gate installed on the Oracle server

oracle to mysql replication using golden gate - v$database

You can see that the Golden Gate version is 12c R2 and our Oracle database version is 12c R1. On the target system, we have to use same version of Oracle Golden Gate 12c R2.



Setup Target Server (MYSQL)


We will be installing MySQL on OEL 6.5 virtual machine. Create a new virtual machine, install OEL 6.5 and name it as MySQL_SRV. Once OEL installation is done, proceed with the below steps for MySQL 5.6 installation.


Install MySQL 5.6 on OEL


Stop firewall

service iptables stop
chkconfig iptables off

Create MySQL Admin (mysql) and Golden Gate Admin (oracle) Users

groupadd mysql
useradd mysql -g mysql
passwd mysql

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba,mysql oracle
passwd orac

Create installation directories

mkdir -p /u01 /u02
chown -R oracle:oinstall /u01 /u02

Install MySQL using YUM repository. Make sure your machine is able to ping internet

yum install mysql mysql-server -y

Start MySQL service and make sure it starts automatically when server is bounced

chkconfig mysqld on
service mysqld start

Configure MySQL database. Create root password – this root user is for mysql and is different from OS root user.

mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):  "Just hit enter"
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] Y     "Y to change MySQL root password"
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.

Remove anonymous users? [Y/n] Y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] Y
... Success!

Cleaning up...


All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

Switch to mysql user and add MYSQL_HOME location

su – musql
vi .bash_profile
export MYSQL_HOME=/var/lib/mysql

Log in to the mysql database and crate golden gate admin db lever user

mysql -uroot -p

Now create a new database for our replication test

create database mydb;
Show databases;
Exit;

Create symbolic link to msyql.sock file otherwise you won’t be able to login to MySQL database from Golden Gate prompt (dblogin)

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

MySQL database installation and setup is done.


Install Golden Gate 12cR2


First of all, we will have to download Golden Gate software for MySQL on Linux server

download golden gate software for mysql on Linux

Once the download is done, copy the file to Linux VM using WinSCP under /u02. Switch to Oracle user and create GG home directory. In this case, GG HOME location is /u01/data/gg

su - oracle
mkdir -p /u01/data/gg
vi .bash_profile
export GG_HOME=/u01/data/gg
export LD_LIBRARY_PATH=/u01/data/gg

Unzip the Golden gate zip file under /u02

cd /u02
unzip 122022_ggs_Linux_x64_MySQL_64bit.zip

Copy the .tar file under GG_HOME location

cp ggs_Linux_x64_MySQL_64bit.tar /u01/data/gg/

Untar the ggs file under GG HOME

cd /u01/data/gg
tar -xvf ggs_Linux_x64_MySQL_64bit.tar

Log in to ggsci, create sub directories, create manager parameter file and start mgr

./ggsci
GGSCI> create subdirs
GGSCI> edit param mgr
port 7809
ACCESSRULE, PROG *, IPADDR *, ALLOW	Allow remote connections
GGSCI> start mgr
GGSCI> exit


Prepare MySQL for replication


On the source (Oracle) we have below table

create table in oracle for mysql replication

Let us create same TAB1 table on target MySQL database under mydb (which we created above)

su - mysql		
mysql	-uroot -p	
Enter	root	password:	 Enter mysql root user password

MYSQL> use	mydb;	 switch to mydb
MYSQL> CREATE TABLE tab1
(
c1 int,
c2 VARCHAR(30),
c3 int,
c4 int,
PRIMARY KEY ( c1 )
)ENGINE=InnoDB;
MYSQL> show tables;


Setup Replication From Oracle to MySQL


As we are performing hybrid database replication, we must generate source definitions file and copy it onto MySql DB server

On Oracle DB Server:
====================
GGSCI> edit param defgen1
DEFSFILE ./dirdef/source_tab1.def
USERID ogg PASSWORD ogg
TABLE FOX.TAB1;

Use defgen utility to generate definitions file 

./defgen paramfile /u01/app/oracle/product/gg/dirprm/defgen1.prm

Scp the definitions file on target GG_HOME/dirdef location

cd dirdef
scp source_tab1.def oracle@192.168.0.199:/u01/data/gg/dirdef/

Configure Initial Load


Before we can proceed further, we need to create a CHECKPOINT table which will store the checkpoint information inside the database

GGSCI> add checkpointtable ogg.ggschkpt

Our source table already contain some data. We need to configure Initial load and then change sync. Let us first configure Initial Load extract on source

On Oracle DB:
=============
GGSCI> ADD EXTRACT INITLE, SOURCEISTABLE

Edit parameter file for initial load extract

GGSCI> EDIT PARAM INITLE
EXTRACT INITLE
userid ogg, password ogg
RMTHOST 192.168.0.199, mgrport 7809
RMTTASK REPLICAT, GROUP INITLR
TABLE FOX.TAB1;

Before we can proceed further, as mentioned earlier, we must create checkpoint table which will store the checkpoint information inside target database

On MySQL DB Server:
===================
cd /u01/data/gg
./ggsci
GGSCI> dblogin sourcedb mydb userid ogg password ogg
GGSCI> EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE mydb.ggschkpt
GGSCI> add checkpointtable	in case of error, exit and re-login to GGSCI

Add initial load Replicat on target

On MySQL DB Server:
===================
GGSCI> ADD REPLICAT INITLR, SPECIALRUN

Edit parameter file for initial load replicat

GGSCI> EDIT PARAM INITLR
REPLICAT INITLR
TARGETDB mydb, userid ogg, password ogg
SOURCEDEFS ./dirdef/source_tab1.def
MAP FOX.TAB1, TARGET mydb.tab1;	         mysql names are case sensitive

Configure Change-Sync Extract and Replicat


Login to source ggsci and add table level supplemental logging

On Oracle DB:
=============
cd $GG_HOME
./ggsci
GGSCI> dblogin userid ogg, password ogg
GGSCI> add trandata FOX.TAB1

Create GG Extract Process

GGSCI> ADD EXTRACT omex1, TRANLOG, BEGIN NOW

GGSCI> add exttrail ./dirdat/om, extract omex1

GGSCI> edit param omex1

EXTRACT omex1
USERID ogg, PASSWORD ogg
EXTTRAIL ./dirdat/om
TABLE fox.tab1;

Create GG Pump process

GGSCI> Add extract omdp1, EXTTRAILSOURCE ./dirdat/om

GGSCI> Add rmttrail /u01/data/gg/dirdat/om, extract omdp1

GGSCI> edit param omdp1

EXTRACT omdp1
USERID ogg, PASSWORD ogg
RMTHOST 192.168.0.199, MGRPORT 7809
RMTTRAIL /u01/data/gg/dirdat/om
TABLE fox.tab1;

Create GG Replicat process

GGSCI> add replicat omrep1, exttrail /u01/data/gg/dirdat/om

GGSCI> edit param omrep1

REPLICAT omrep1
TARGETDB mydb, USERID ogg, PASSWORD ogg
SOURCEDEFS ./dirdef/source_tab1.def
MAP FOX.TAB1 TARGET mydb.tab1;	        mysql names are case sensitive


Test Replication


First, start the change sync extract and data pump on the source. This will start capturing changes while we perform the initial load. Do not start replicat at this point

On Oracle DB Server:
====================
GGSCI> start omex1
GGSCI> start omdp1

Now start the initial load extract. Remember, this will automatically start the initial load replicat on target

On proddb:
==========
GGSCI> start INITLE

GGSCI> INFO INITLE

EXTRACT	INITLE	    Last Started	2016-01-11 15:59   Status STOPPED
Checkpoint Lag	    Not Available	
Log Read Checkpoint Table FOX.TAB1	
		2016-01-11 15:59:57  Record 4
Task		SOURCEISTABLE

Verify on target if all the 4 records have been loaded on target table or not

On MySQL DB:
============
su – mysql
mysql -uroot -p
Enter root password:
MYSQL> use mydb;
MYSQL> select * from tab1;

Now start the change sync replicat

On MySQL DB:
============
su – oracle
cd /u01/data/gg
./ggsci
GGSCI> dblogin sourcedb mydb userid ogg
GGSCI> start omrep1
At this stage, you can delete the initial load extract and replicat process as they are no longer needed.


Summary


In this article, we replicated one single table from source Oracle database to target MySQL database. In real-time, you would need to configure ODBC to enable golden gate connect to MySQL database. Depends on environment to environment.



Further experiment:

  • Create one newer table on source and target

  • Add a new table to Extract, Pump and Replicat

  • Try to insert some new records on the source and check if it is being reflected on target.


Become a top notch dba with DBA Genesis
Start your DBA career today
bottom of page