ogg21实现mysql To mysql的全量和增量同步

概述

本文记录使用ogg21实现mysql To mysql的全量和增量同步。

安装

mysql ogg21的安装过程比较简单，其实就是下载安装包解压就行。
Oracle GoldenGate Downloads
我是linux系统，所以选择这个下载：
Oracle GoldenGate 21.3.0.0.0 for MySQL-compatible Databases on Linux x86-64

我解压到/ogg目录下，就可以用。

准备测试数据和迁移用户

源端初始化结构和数据

create database sourcegg;
source /ogg/demo_mysql_create.sql
source /ogg/demo_mysql_insert.sql

目标端只初始化结构

create database targetgg;
source /ogg/demo_mysql_create.sql

CREATE USER 'ogg'@'%' IDENTIFIED BY 'xxx';
GRANT ALL PRIVILEGES ON *.* TO 'ogg'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

启动mgr

cd /ogg/
./ggsci

edit param mgr

PORT 17809
DYNAMICPORTLIST 17810-17909
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 3

start mgr

验证

info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING   

全量同步前先启动增量抽取进程

因为全量同步一次到启动增量抽取的过程中，可能会有新的数据更新产生，因此我们需要提前开启增量抽取：

dblogin sourcedb sourcegg@10.60.143.102:3307,userid ogg,PASSWORD XXX

edit param ECDC

EXTRACT ECDC
SOURCEDB sourcegg@10.60.143.102:3307,USERID ogg,PASSWORD xxx
RMTHOST 10.60.143.102,MGRPORT 17809
tranLogOptions altlogDest REMOTE 
ddl include mapped
EXTTRAIL /ogg/dirdat/ed
TABLE sourcegg.*;

ADD EXTRACT ECDC,TRANLOG,BEGIN NOW
ADD EXTTRAIL /ogg/dirdat/ea,EXTRACT ECDC

start EXTRACT ECDC

我们在源端插入一条数据，然后检查下抽取进程能不能正常捕获：
insert into TCUSTMER values(‘a’,‘b’,‘c’,‘d’);

GGSCI (xxx)> stats ECDC

Sending STATS request to Extract group ECDC ...

Start of statistics at 2025-04-27 10:27:30.

DDL replication statistics (for all trails):

*** Total statistics since extract started     ***
        Operations                                         0.00
        Mapped operations                                  0.00
        Unmapped operations                                0.00
        Other operations                                   0.00
        Excluded operations                                0.00

Output to /ogg/dirdat/ed:

Extracting from sourcegg.tcustmer to sourcegg.tcustmer:

*** Total statistics since 2025-04-27 10:27:06 ***
    Total inserts                              1.00
    Total updates                              0.00
    Total deletes                              0.00
    Total upserts                              0.00
    Total discards                             0.00
    Total operations                           1.00

全量同步一次

配置全量抽取进程

dblogin sourcedb sourcegg@10.60.143.102:3307,userid ogg,PASSWORD xxx

edit param EILOAD

EXTRACT EILOAD
SOURCEDB sourcegg2@10.60.143.102:3307,USERID ogg,PASSWORD xxx
RMTHOST 10.60.143.102,MGRPORT 17809
RMTTASK REPLICAT,GROUP RILOAD
TABLE sourcegg.*;


ADD EXTRACT EILOAD,SOURCEISTABLE

配置全量复制进程

dblogin sourcedb targetgg@10.60.143.103:3306,userid ogg,PASSWORD xxx
edit param RILOAD

REPLICAT RILOAD
TARGETDB targetgg@10.60.143.103:3306,userid ogg,PASSWORD xxx
DISCARDFILE ./dirrpt/RILOAD.dsc, PURGE
MAP sourcegg.*,TARGET targetgg.*

ADD REPLICAT RILOAD, SPECIALRUN

启动一次全量抽取，只需要自动EILOAD即可，RILOAD会自动启动：

start EXTRACT EILOAD

此时数据就全量导入目标端了。EILOAD启动一次后就会停下来。

GGSCI (dbteam-143102) 4> info all tasks

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

EXTRACT     STOPPED     EILOAD                               
REPLICAT    STOPPED     RILOAD

我们配置增量同步之前可以模拟插入一些数据，因为我们提前启动好了增量抽取，这些插入的数据在增量复制进行开启后，也会传输到目标端。（与这个相对应的，如果我们没有提前开启增量抽取，而是简单的先执行全量，然后配置增量，那么这之间产生的变化是无法同步到目标端的）。

开启增量

添加一个checkpointtable

dblogin sourcedb targetgg@10.60.143.103:3306,userid ogg,PASSWORD xxx
add checkpointtable targetgg.ckpt

edit param RCDC

REPLICAT RCDC
TARGETDB targetgg@10.60.143.103:3306,userid ogg,PASSWORD xxx
DISCARDFILE ./dirrpt/ECDC.dsc, PURGE
HANDLECOLLISIONS
MAP sourcegg.*,TARGET targetgg.*;

add replicat RCDC,exttrail /ogg/dirdat/ed,checkpointtable targetgg.ckpt

start REPLICAT RCDC

GGSCI (dbteam-143102) 5> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     ECDC       00:00:00      00:00:09    
REPLICAT    RUNNING     RCDC       00:00:00      00:00:01  

此时两端数据可以正常同步，可以在源端插入数据测试下。