开发者

基于OGG实现Oracle实时同步MySQL的全过程

开发者 https://www.devze.com 2023-11-28 08:59 出处:网络 作者: IT邦德
目录1.架构设计2.OGG安装部署2.1 OGG for oracle2.2 OGG for mysql3.Oracle相关配置3.1 参数调整3.2 新增用户4.MySQL数据初始化5.Oracle OGG设置6.MySQL OGG设置7.全量同步数据8.增量时实同步8.1 Oracle端8.2 MySQL端
目录
  • 1.架构设计
  • 2.OGG安装部署
    • 2.1 OGG for oracle
    • 2.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.测试同步

            1.架构设计

            基于OGG实现Oracle实时同步MySQL的全过程

            基于OGG实现Oracle实时同步MySQL的全过程

            2.OGG安装部署

            2.1 OGG for Oracle

            1.OGG下载地址:

            https://www.oracle.com/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

            基于OGG实现Oracle实时同步MySQL的全过程

            2.2 OGG for MySQL

            1.安装客户端

            yum install https://dev.mysql.com/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@ophpgg21all 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
            

            基于OGG实现Oracle实时同步MySQL的全过程

            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`js 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.*

            基于OGG实现Oracle实时同步MySQL的全过程

            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
            

            基于OGG实现Oracle实时同步MySQL的全过程

            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

            基于OGG实现Oracle实时同步MySQL的全过程

            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

            基于OGG实现Oracle实时同步MySQL的全过程

            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 ***
              js  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 requesVyYStwVt 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        python                     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)
            

            以上就是基于OGG实现Oracle实时同步MySQL的全过程的详细内容,更多关于Oracle实时同步MySQL的资料请关注编程客栈(www.devze.com)其它相关文章!

            0

            精彩评论

            暂无评论...
            验证码 换一张
            取 消