MySQL to Oracle Heterogeneous Unidirectional Replication using Slave Server Bin log

MySQL to Oracle Heterogeneous Unidirectional Replication using Slave Server Bin log 

In this article we will see   MySQL to Oracle Heterogeneous Unidirectional Replication using Slave Server Bin log 

First setup Master -slave replication between two mysql servers.

Use below link to setup Master-slave  Replication

MySQL Master-Slave Replication on different AWS instances.

Once data was replicating between two master and slave now  do some changes at slave side to replicate data into oracle database.

Before doing changes please check following figure :

In Above diagram Data will be replicated from Master to Slave server then on slave server we will enable log_slave_updates parameter to push  master DML tx’s into binnary log of slave.

 

Insatll mysql on two machines and keep below line on /etc/my.cnf

Important lines in MySQL Replication :

–>The slave server always connects with Master server.

–>MySQL Replication works based on Three types threads

Prerequisite for MySQL replication :

Both master and slave should be running with different server id.
Master server should be enabled with binary logs.
Step : 1

Make sure the id of both the servers should be different.

Master Server :

Check server id need to connect mysql and issue following command.

For master server the id is 1.

Slave Server :

For slave process also the id is same.

If server id is same replication is not possible, so we need change the server id.

How to change sever id :

Open my.cnf file and write

server-id=2
Restart the service and check server id.

There serverid & server uuid will be written for every sql statement in binary logs :

Step 2 :

Check binary log is enabled or not on both side .

Master Server :

Slave Server :

Step 3 :

In Master side we need to create a user with required privileges.

Here i have created a user called replica with privileges called replication slave , replication client.

When we are performing changes to server , content will be added to binary logs.

check binary logs on master.

Step 4:

Check ip address of master .

The Master ip address will be given to server to get binary to slave side.

Slave Server :
execute below line

What is the use of each variable ?

Master_host –> Need to give master host address
master_port –> Need to write Master mysql db running port number
master_user –> The user which is created for replication and privs
master_password –> Password of that user
master_log_file –> Binary logfile which currently running at master side
master_log_pos –> Binary logfile master log postion

Step 5 :

Check status of slave process at slave side :

The \G rearranges the text to make it more readable.

Once we start the slave process, the above two variable will show as yes.

Activate the slave server:

Now check the status again.

Step 6 :

Now check Replication is happening master to slave or not.

Master server :

Check database list and create new database.

Use the database crm and create a table.

Create table which you want to replicat

Now insert the records and check at slave side.

Step 6:

Slave Server :

IN Slave Sever and check records.

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Check Relay logs at slave side :

mysql> exit
Bye

In above example we are able to see relay logs with contents and also relay-log.info and master.info files. As of now master to slave replication done

Now we need to do some changes on slave machine before doing installation

Stop the servcie

Add new parameter on slave server /etc/my.cnf

Note : The above parameter  to push  master tx’s into binnary log of slave.

then start mysql server

MySQL Slave Server  to Oracle Heterogeneous GoldenGate Unidirectional Replication
Node Details :

MySQL Slaver Server (SOURCE)

The MySQL database name is ggadmin, and uses the innodb engine.

The MySQL server name is mysql1.

GG2(TARGET)

192.168.0.40 gg2.orcl.com gg2

Database Name :ORCl

Instance Name : orcl

Download Goldengate Binaries
Download GoldenGate binaries from Oracle.com using below link(Oracle)

https://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

Make sure that you are using the correct OS version of binaries.

MySQL Slaver Server :

Starting on the MySQL database server, mysql1, install the GoldenGate software.

set the Bash_profile

Run the bash_profile

unzip the GG s/w

untar the file

Check manager sunning or not , If not start the Mgr after specifying the port

Connect as Root user and create users

Create database
Verify that the MySQL ODBC connector is installed. As the root Linux user, run the following package manager query command:

login as root on OS:

rpm -qa odbc
If it doesn’t exist, as root, create the /usr/local/etc/odbc.ini file. Add (or verify) the following in the file:

vi /usr/local/etc/odbc.ini

Connect to GGSCI on the Oracle server and create the initial load extract. Since these are small tables, we will use GoldenGate to do the initial load. If these were large tables, we would need to use a different, faster loading method such as load data infile. Also, note that the parameters point to the target (MySQL) database.

Login as oggadm1

Edit extmysql file

Add extract process

Add extrail file to extract process

Start Extract process

Add extract process for datapump(dpmysql)

Add remote trail file

Edit Datapump param file

Start dpmysql

Check process

Check records

Master Server :

Insert records from master and check stats on slave ggsci

 

Slave server :

Connect ggsci of mysql and check stats

ORACLE TARGET :

Install GG software in oracle database

Check below link to install GG:

GoldenGate: Oracle to Oracle GoldenGate Unidirectional Replication

Connect to GGSCI:

Login as ggamdin user

Create wallet and add credential store to create alias name

Add Replicat process

Edit repmysql param file

Start the replicate process

Check the process

Check the stats repmysql

Now check the records

 

Insert few more records on master and check table data in Slave and Oracle target.

 

Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment