基于OGG实现Oracle实时同步MySQL

📢📢📢📣📣📣 哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10余年DBA及大数据工作经验 一位上进心十足的【大数据领域博主】！😜😜😜 中国DBA联盟(ACDU)成员，目前服务于工业互联网 擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。 ✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞 ❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

文章目录 前言1.架构设计2.OGG安装部署2.1 OGG for Oracle2.2 OGG for MySQL 3.Oracle相关配置3.1 参数调整3.2 新增用户 4.MySQL数据初始化5.Oracle OGG设置6.MySQL OGG设置7.全量同步数据8.增量时实同步8.1 Oracle端8.2 MySQL端 9.测试同步

前言 本文详细阐述了基于OGG实现Oracle实时同步MySQL全过程

---

1.架构设计

2.OGG安装部署 2.1 OGG for Oracle

1.OGG下载地址： .oracle /middleware/technologies/goldengate-downloads.html 创建OGG使用目录 chown -R oracle:oinstall /oraogg chmod 775 -R /oraogg

2.环境变量如下 vi .bash_profile export GG_HOME=/oraogg/goldengate export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$GG_HOME export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib alias ggsci='cd $GG_HOME;ggsci' source .bash_profile 3.静默安装 cd /oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response vi /oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp --只修改如下3个地方即可。 INSTALL_OPTION=ora19c SOFTWARE_LOCATION=/oraogg/goldengate INVENTORY_LOCATION=/u01/app/oraInventory /u01/app/oracle/product/19.3.0/db_1

使用如下命令静默安装： /oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/runInstaller -silent -responseFile /oraogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp

OGG初始化 cd /ogg ./ggsci create subdirs

2.2 OGG for MySQL

1.安装客户端 yum install dev.mysql /get/mysql80-community-release-el7-3.noarch.rpm yum install mysql-community-server --nogpgcheck

2.创建oggm用户运行OGG for MySQL软件，安装目录为/oggmysql groupadd -g 1005 ogg useradd -g ogg -u 1005 -m oggm mkdir -p /oggmysql chown oggm:ogg /oggmysql

3.解压缩安装 cd /oggmysql/ unizp 213000_ggs_Linux_x64_MySQL_64bit.zip tar -xf ggs_Linux_x64_MySQL_64bit.tar

[root@ogg21all oggmysql]# ./ggsci -V Oracle GoldenGate Command Interpreter for MySQL Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047 Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46 Copyright © 1995, 2021, Oracle and/or its affiliates. All rights reserved.

4.配置 su - oggm $ /oggmysql/ggsci GGSCI (ogg21all) 1> create subdirs

5.环境变量设置 vi .bash_profile export GG_HOME=/oggmysql export PATH=$PATH:$HOME/bin:$GG_HOME alias ggsci='cd $GG_HOME;ggsci' source .bash_profile

3.Oracle相关配置 3.1 参数调整

– oracle数据库配置 1.开启数据库归档–如果没有开启 2.开启数据库级别附加日志–如果没有开始最小附加日志 3.开启强制日志–如果没有开启强制日志 4.设置ENABLE_GOLDENGATE_REPLICAT参数为TRUE 5.创建OGG用户包括包括源端用户、目标端用户以及OGG抽取用户

alter database add supplemental log data; alter database add supplemental log data (all) columns; alter database force logging; alter system set enable_goldengate_replication=TRUE;

##修改归档路径 mkdir -p /home/oracle/arch SYS@oradb> alter system set log_archive_dest_1=‘location=/home/oracle/arch’; System altered. SYS@oradb> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch Oldest online log sequence 3 Next log sequence to archive 5 Current log sequence 5

SYS@oradb> select name,supplemental_log_data_min , force_logging, log_mode from v$database; NAME SUPPLEMENTAL_LOG FORCE_LOGGING LOG_MODE ------------------ ---------------- ----------------- ------------------------ ORCLCDB YES YES ARCHIVELOG

##关闭回收站 SQL> SHOW PARAMETER recyclebin; SQL> ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE; SQL> show recyclebin; SQL> PURGE recyclebin;

3.2 新增用户 -- OGG管理用户 SYS@oradb> alter session set container=ORCLPDB1; CREATE USER ogg identified by oracle; GRANT DBA to ogg; grant SELECT ANY DICTIONARY to ogg; GRANT EXECUTE ON SYS.DBMS_LOCK TO ogg; grant select any transaction to ogg; grant select any table to ogg; grant flashback any table to ogg; grant alter any table to ogg; exec dbms_goldengate_auth.grant_admin_privilege('OGG','*',TRUE); -- 业务用户 CREATE USER rptuser identified by oracle; GRANT DBA to rptuser ; grant SELECT ANY DICTIONARY to rptuser; GRANT EXECUTE ON SYS.DBMS_LOCK TO rptuser; 4.MySQL数据初始化 1.生成MySQL端DDL语句 可以使用Navicat的数据传输功能或其它工具直接从Oracle端生成MySQL类型的建表语句如下： mysql -uroot -proot create database rptdb; mysql -uroot -proot -h 172.18.12.91 -D rptdb -f < ddl.sql 2.DDL语句如下 SET NAMES utf8; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `ADDRESSES`; CREATE TABLE `ADDRESSES` ( `ADDRESS_ID` decimal(12, 0) NOT NULL, `CUSTOMER_ID` decimal(12, 0) NOT NULL, `DATE_CREATED` datetime NOT NULL, `HOUSE_NO_OR_NAME` varchar(60) NULL, `STREET_NAME` varchar(60) NULL, `TOWN` varchar(60) NULL, `COUNTY` varchar(60) NULL, `COUNTRY` varchar(60) NULL, `POST_CODE` varchar(12) NULL, `ZIP_CODE` varchar(12) NULL, PRIMARY KEY (`ADDRESS_ID`), INDEX `ADDRESS_CUST_IX`(`CUSTOMER_ID` ASC) ); ----- Table structure for CARD_DETAILS ---- DROP TABLE IF EXISTS `CARD_DETAILS`; CREATE TABLE `CARD_DETAILS` ( `CARD_ID` decimal(12, 0) NOT NULL, `CUSTOMER_ID` decimal(12, 0) NOT NULL, `CARD_TYPE` varchar(30) NOT NULL, `CARD_NUMBER` decimal(12, 0) NOT NULL, `EXPIRY_DATE` datetime NOT NULL, `IS_VALID` varchar(1) NOT NULL, `SECURITY_CODE` decimal(6, 0) NULL, PRIMARY KEY (`CARD_ID`), INDEX `CARDDETAILS_CUST_IX`(`CUSTOMER_ID` ASC) ); ---- Table structure for CUSTOMERS ---- DROP TABLE IF EXISTS `CUSTOMERS`; CREATE TABLE `CUSTOMERS` ( `CUSTOMER_ID` decimal(12, 0) NOT NULL, `CUST_FIRST_NAME` varchar(40) NOT NULL, `CUST_LAST_NAME` varchar(40) NOT NULL, `NLS_LANGUAGE` varchar(3) NULL, `NLS_TERRITORY` varchar(30) NULL, `CREDIT_LIMIT` decimal(9, 2) NULL, `CUST_EMAIL` varchar(100) NULL, `ACCOUNT_MGR_ID` decimal(12, 0) NULL, `CUSTOMER_SINCE` datetime NULL, `CUSTOMER_CLASS` varchar(40) NULL, `SUGGESTIONS` varchar(40) NULL, `DOB` datetime NULL, `MAILSHOT` varchar(1) NULL, `PARTNER_MAILSHOT` varchar(1) NULL, `PREFERRED_ADDRESS` decimal(12, 0) NULL, `PREFERRED_CARD` decimal(12, 0) NULL, PRIMARY KEY (`CUSTOMER_ID`), INDEX `CUST_ACCOUNT_MANAGER_IX`(`ACCOUNT_MGR_ID` ASC), INDEX `CUST_DOB_IX`(`DOB` ASC), INDEX `CUST_EMAIL_IX`(`CUST_EMAIL` ASC) ); 5.Oracle OGG设置

[root@ogg21all /]# su - oracle [oracle@ogg21all ~]$ ggsci GGSCI (ogg21all) 2> edit params mgr PORT 7809

add credentialstore alter credentialstore add user ogg@172.18.12.90/oradb, password oracle alias ora19c INFO CREDENTIALSTORE

GGSCI (ogg21all) 7> INFO CREDENTIALSTORE Reading from credential store: Default domain: OracleGoldenGate Alias: ora19c Userid: ogg@172.18.12.90/oradb

dblogin useridalias ora19c ADD SCHEMATRANDATA RPTUSER INFO SCHEMATRANDATA RPTUSER list tables RPTUSER.*

6.MySQL OGG设置

GGSCI (ogg21all) 1> edit params mgr port 8809

GGSCI (ogg21all) 2> start mgr Manager started.

GGSCI (ogg21all) 3> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING

7.全量同步数据 Oracle全量同步到MySQL 注意：在此阶段，源端需要停业务，不能产生新数据。 -- oracle端 edit params ext0 EXTRACT ext0 USERIDALIAS ora19c rmthost 127.0.0.1,mgrport 8809 rmttask replicat,group rep0 TABLE RPTUSER.ADDRESSES; TABLE RPTUSER.CARD_DETAILS; TABLE RPTUSER.CUSTOMERS; add extract ext0 ,sourceistable delete extract ext0 -- MySQL端 edit params rep0 replicat rep0 targetdb rptdb@172.18.12.91:3306 userid root password root map RPTUSER.ADDRESSES, target rptdb.ADDRESSES; map RPTUSER.CARD_DETAILS, target rptdb.CARD_DETAILS; map RPTUSER.CUSTOMERS, target rptdb.CUSTOMERS; add replicat rep0 ,specialrun delete replicat rep0 -- 直接启动源端ext0即可，rep0不用启动,MGR会自动启动它，等同步结束，它会自动关闭 start ext0 -- 查看日志 info rep0,showch view report rep0 --登录验证数据 mysql -uroot -proot -h 172.18.12.91 -D rptdb mysql> select count(*) from ADDRESSES; +----------+ | count(*) | +----------+ | 150 | +----------+ 1 row in set (0.04 sec) mysql> select count(*) from CARD_DETAILS; +----------+ | count(*) | +----------+ | 150 | +----------+ 1 row in set (0.05 sec) mysql> select count(*) from CUSTOMERS; +----------+ | count(*) | +----------+ | 100 | +----------+ 1 row in set (0.04 sec) GGSCI (ogg21all as ogg@oradb) 21> info ext0 Extract EXT0 Last Started 2023-11-25 18:58 Status STOPPED Checkpoint Lag Not Available Log Read Checkpoint Table RPTUSER.CUSTOMERS 2023-11-25 18:58:46 Record 100 Task SOURCEISTABLE

8.增量时实同步 8.1 Oracle端

ADD EXTRACT exto INTEGRATED TRANLOG BEGIN NOW ADD EXTTRAIL ./dirdat/eo EXTRACT exto

dblogin useridalias ora19c REGISTER EXTRACT exto DATABASE

edit params exto EXTRACT exto USERIDALIAS ora19c TRANLOGOPTIONS FETCHPARTIALLOB EXTTRAIL ./dirdat/eo TABLE RPTUSER.ADDRESSES; TABLE RPTUSER.CARD_DETAILS; TABLE RPTUSER.CUSTOMERS;

启动exto start exto GGSCI (ogg21all as ogg@oradb) 12> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING EXTO 00:02:19 00:00:08

8.2 MySQL端

edit params repm replicat repm targetdb rptdb@172.18.12.91:3306 userid root password root map RPTUSER.ADDRESSES, target rptdb.ADDRESSES; map RPTUSER.CARD_DETAILS, target rptdb.CARD_DETAILS; map RPTUSER.CUSTOMERS, target rptdb.CUSTOMERS;

add rep repm, exttrail /oraogg/goldengate/dirdat/eo, NODBCHECKPOINT delete rep repm

start repm

9.测试同步 1.Oracle端测试产生数据 SYS@oradb> DELETE FROM RPTUSER.ADDRESSES WHERE ADDRESS_ID=150; GGSCI (ogg21all as ogg@oradb) 21> stats exto,total Sending STATS request to Extract group EXTO ... Start of statistics at 2023-11-25 19:36:13. Output to ./dirdat/eo: Extracting from RPTUSER.ADDRESSES to RPTUSER.ADDRESSES: *** Total statistics since 2023-11-25 19:25:02 *** Total inserts 0.00 Total updates 0.00 Total deletes 1.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 End of statistics. 2.mysql端插入数据 mysql -uroot -proot -h 172.18.12.91 -D rptdb mysql> select count(*) from ADDRESSES; GGSCI (ogg21all) 18> stats repm,total Sending STATS request to Replicat group REPM ... Start of statistics at 2023-11-25 19:56:10. Replicating from RPTUSER.ADDRESSES to rptdb.ADDRESSES: *** Total statistics since 2023-11-25 19:54:49 *** Total inserts 0.00 Total updates 0.00 Total deletes 1.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 End of statistics. mysql> SELECT * FROM ADDRESSES WHERE ADDRESS_ID=150; Empty set (0.00 sec) mysql> select database(); +------------+ | database() | +------------+ | rptdb | +------------+ 1 row in set (0.00 sec)