MySQL data sync to Oracle with OGG(Remote Delivery)
MySQL to Oracle with OGG
1. Install MySQL:
yum install mysql-community-server
[root@localhost ~]# yum install mysql-community-server
Loaded plugins: refresh-packagekit, security, ulninfo
Setting up Install Process
mysql-connectors-community | 2.5 kB 00:00
mysql-connectors-community/primary_db | 17 kB 00:00
mysql-tools-community | 2.5 kB 00:00
mysql-tools-community/primary_db | 38 kB 00:00
mysql57-community | 2.5 kB 00:00
mysql57-community/primary_db | 130 kB 00:00
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.7.20-1.el6 will be obsoletin
--> Processing Dependency: mysql-community-common(x86-64) = 5.7.20-1.el6 fokage: mysql-community-server-5.7.20-1.el6.x86_64
--> Processing Dependency: mysql-community-client(x86-64) >= 5.7.9 for packmysql-community-server-5.7.20-1.el6.x86_64
---> Package mysql-server.x86_64 0:5.1.73-8.0.1.el6_8 will be obsoleted
--> Running transaction check
---> Package mysql.x86_64 0:5.1.73-8.0.1.el6_8 will be obsoleted
---> Package mysql-community-client.x86_64 0:5.7.20-1.el6 will be obsoletin
--> Processing Dependency: mysql-community-libs(x86-64) >= 5.7.9 for packagsql-community-client-5.7.20-1.el6.x86_64
---> Package mysql-community-common.x86_64 0:5.7.20-1.el6 will be installed
--> Running transaction check
---> Package mysql-community-libs.x86_64 0:5.7.20-1.el6 will be obsoleting
---> Package mysql-libs.x86_64 0:5.1.73-8.0.1.el6_8 will be obsoleted
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: 2:pos2.6.6-6.el6_5.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package: perl-ySQL-4.013-3.el6.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) fckage: 2:postfix-2.6.6-6.el6_5.x86_64
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit) fckage: perl-DBD-MySQL-4.013-3.el6.x86_64
--> Running transaction check
---> Package mysql-community-libs-compat.x86_64 0:5.7.20-1.el6 will be obsog
---> Package postfix.x86_64 2:2.6.6-6.el6_5 will be updated
---> Package postfix.x86_64 2:2.6.6-8.el6 will be an update
--> Finished Dependency Resolution
Dependencies Resolved
===========================================================================
Package Arch Version Repository
===========================================================================
Installing:
mysql-community-client x86_64 5.7.20-1.el6 mysql57-community
replacing mysql.x86_64 5.1.73-8.0.1.el6_8
mysql-community-libs x86_64 5.7.20-1.el6 mysql57-community 2
replacing mysql-libs.x86_64 5.1.73-8.0.1.el6_8
mysql-community-libs-compat x86_64 5.7.20-1.el6 mysql57-community 1
replacing mysql-libs.x86_64 5.1.73-8.0.1.el6_8
mysql-community-server x86_64 5.7.20-1.el6 mysql57-community 1
replacing mysql-server.x86_64 5.1.73-8.0.1.el6_8
Installing for dependencies:
mysql-community-common x86_64 5.7.20-1.el6 mysql57-community 3
Updating for dependencies:
postfix x86_64 2:2.6.6-8.el6 public_ol6_latest 2
Transaction Summary
===========================================================================
Install 5 Package(s)
Upgrade 1 Package(s)
Total download size: 181 M
Is this ok [y/N]: y
Downloading Packages:
(1/6): mysql-community-client-5.7.20-1.el6.x86_64.rpm | 23 MB 00:25
(2/6): mysql-community-common-5.7.20-1.el6.x86_64.rpm | 329 kB 00:01
(3/6): mysql-community-libs-5.7.20-1.el6.x86_64.rpm | 2.1 MB 00:16
(4/6): mysql-community-libs-compat-5.7.20-1.el6.x86_64.r | 1.6 MB 00:11
(5/6): mysql-community-server-5.7.20-1.el6.x86_64.rpm | 152 MB 03:10
(6/6): postfix-2.6.6-8.el6.x86_64.rpm | 2.0 MB 00:03
---------------------------------------------------------------------------
Total 724 kB/s | 181 MB 04:16
warning: rpmts_HdrFromFdno: Header V3 DSA/SHA1 Signature, key ID 5072e1f5:
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
Userid : MySQL Release Engineering <mysql-build@oss.oracle.com>
Package: mysql57-community-release-el6-8.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
Installing : mysql-community-common-5.7.20-1.el6.x86_64
Installing : mysql-community-libs-5.7.20-1.el6.x86_64
Installing : mysql-community-libs-compat-5.7.20-1.el6.x86_64
Installing : mysql-community-client-5.7.20-1.el6.x86_64
Installing : mysql-community-server-5.7.20-1.el6.x86_64
Updating : 2:postfix-2.6.6-8.el6.x86_64
Erasing : mysql-server-5.1.73-8.0.1.el6_8.x86_64
Erasing : mysql-5.1.73-8.0.1.el6_8.x86_64
Cleanup : 2:postfix-2.6.6-6.el6_5.x86_64
Erasing : mysql-libs-5.1.73-8.0.1.el6_8.x86_64 1
Verifying : mysql-community-common-5.7.20-1.el6.x86_64
Verifying : mysql-community-libs-compat-5.7.20-1.el6.x86_64
Verifying : 2:postfix-2.6.6-8.el6.x86_64
Verifying : mysql-community-libs-5.7.20-1.el6.x86_64
Verifying : mysql-community-client-5.7.20-1.el6.x86_64
Verifying : mysql-community-server-5.7.20-1.el6.x86_64
Verifying : mysql-server-5.1.73-8.0.1.el6_8.x86_64
Verifying : mysql-libs-5.1.73-8.0.1.el6_8.x86_64
Verifying : 2:postfix-2.6.6-6.el6_5.x86_64
Verifying : mysql-5.1.73-8.0.1.el6_8.x86_64 1
Installed:
mysql-community-client.x86_64 0:5.7.20-1.el6
mysql-community-libs.x86_64 0:5.7.20-1.el6
mysql-community-libs-compat.x86_64 0:5.7.20-1.el6
mysql-community-server.x86_64 0:5.7.20-1.el6
Dependency Installed:
mysql-community-common.x86_64 0:5.7.20-1.el6
Dependency Updated:
postfix.x86_64 2:2.6.6-8.el6
Replaced:
mysql.x86_64 0:5.1.73-8.0.1.el6_8
mysql-libs.x86_64 0:5.1.73-8.0.1.el6_8
mysql-server.x86_64 0:5.1.73-8.0.1.el6_8
Complete!
2. Start service
[root@localhost log]# service mysqld start
Starting mysqld: [ OK ]
[root@localhost log]# grep 'temporary password' /var/log/mysqld.log
2018-01-08T13:17:47.001764Z 1 [Note] A temporary password is generated for localhost: Ba(*ZrtNa7l6
3. Change the temporary password
[root@localhost log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.20
Copyright (c) 2000, 2017, 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 stateme
mysql> set password =password('Welcome_1');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> alter user 'root'@'localhost' password expire never;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@localhost log]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.20 MySQL Community Server (GPL)
4. Create database:
mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)
mysql> use mydb;
Database changed
mysql> show tables;
Empty set (0.00 sec)
5. Install the OGG for MySQL
Unzip the package to the folder
6. Install the Oracle Client
Choose one with networking services
Update the tnsnames.ora of the target and add the target host into /etc/hosts
tnsping
7. Change the folder privilege
Chmod 777 for mysql-bin.index and oracle
[oracle@MYSQL mysql]$ ll
total 122928
-rw-r-----. 1 oracle oracle 56 Jan 9 06:44 auto.cnf
-rwxrwxrwx. 1 oracle oracle 351 Jan 9 05:29 ib_buffer_pool
-rwxrwxrwx. 1 oracle oracle 12582912 Jan 9 06:44 ibdata1
-rwxrwxrwx. 1 oracle oracle 50331648 Jan 9 06:44 ib_logfile0
-rwxrwxrwx. 1 oracle oracle 50331648 Jan 8 08:17 ib_logfile1
-rw-r-----. 1 oracle oracle 12582912 Jan 9 06:44 ibtmp1
drwxrwxrwx. 2 oracle oracle 4096 Jan 9 05:17 mydb
drwxrwxrwx. 2 oracle oracle 4096 Jan 8 08:17 mysql
-rwxrwxrwx. 1 oracle oracle 177 Jan 9 06:42 mysql-bin.000001
-rw-r-----. 1 oracle oracle 154 Jan 9 06:44 mysql-bin.000002
-rwxrwxrwx. 1 oracle oracle 64 Jan 9 06:44 mysql-bin.index
srwxrwxrwx. 1 oracle oracle 0 Jan 9 06:44 mysql.sock
-rw-------. 1 oracle oracle 6 Jan 9 06:44 mysql.sock.lock
drwxrwxrwx. 2 oracle oracle 4096 Jan 8 08:17 performance_schema
drwxrwxrwx. 2 oracle oracle 12288 Jan 8 08:17 sys
8. my.cnf file
[oracle@MYSQL mysql]$ cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#Update for OGG
server-id=11223344
log-bin=/var/lib/mysql/mysql-bin
#log-bin-index=/var/lib/mysql/mysql-bin.index
binlog_format=row
9. OGG setup
Extract:
Extract extmysql
ExtTrail ./dirdat/aa
SourceDB mydb, UserID root, Password Welcome_1
TranLogOptions AltLogDest /var/lib/mysql/mysql-bin.index
Table mydb.TCUSTMER;
Pump:
Extract pummysql
RmtHost MYSQL, MgrPort 7909
RmtTrail ./dirdat/ac
Passthru
Table mydb.TCUSTMER;
Replicat:
Replicat repmysql
UserID ogg@orcl, password ogg
DiscardFile ./dirrpt/repmysql.dsc, purge
map mydb.TCUSTMER,target ogg.TCUSTMER;
Add groups:
--adding extract group by
add extract extmysql,tranlog,begin now
add exttrail ./dirdat/aa,extract extmysql
--adding pump group by
add extract pummysql,exttrailsource ./dirdat/aa
add rmttrail ./dirdat/ac,extract pummysql
--adding replicat group by
add replicat repmysql,exttrail ./dirdat/ac
10. Test DML
Create table:
--source MySQL
CREATE TABLE TCUSTMER
(
CUST_CODE VARCHAR(4) NOT NULL,
NAME VARCHAR(30),
CITY VARCHAR(20),
STATE CHAR(2),
PRIMARY KEY (CUST_CODE)
);
--target Oracle
CREATE TABLE tcustmer
(
cust_code VARCHAR2(4),
name VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
PRIMARY KEY (cust_code)
USING INDEX
);
Test SQL:
INSERT INTO TCUSTMER
VALUES
(
'Alex',
'Alex&Lina INC.',
'LA',
'CA'
);
INSERT INTO TCUSTMER
VALUES
(
'JANE',
'ROCKY FLYER INC.',
'DENVER',
'CO'
);
INSERT INTO TCUSTMER
VALUES
(
'WILL',
'BG SOFTWARE CO.',
'SEATTLE',
'WA'
);
delete from TCUSTMER where CUST_CODE='WILL';
update TCUSTMER set city='LA' where CUST_CODE='JANE';
11. Test result
Extract:
Pump:
Replicate:
MySQL source table:
Oracle target table:
