ogg 异步同步，mysql to oracle

桃扇骨 2022-08-31 06:59 234阅读 0赞

今天遇到一个朋友需要做一个mysql到oralce 的ogg同步测试，正好利用他的环境测试了一下myql到oracle表的数据同步。

下面给出测试的环境

mysql:5.7.25   oracle:11.2.0.4

测试同步数据:mysql mytest库下test 表同步到 oracle test用户下test1表中去

ogg版本这里我选择了19C ogg,下载连接如下：

[https://www.oracle.com/middleware/technologies/goldengate-downloads.html][https_www.oracle.com_middleware_technologies_goldengate-downloads.html]

选择如下2个版本：

MySQL选择如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70][]

oracle 选择如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 1][] 其实不管是不是异步环境，ogg 的配置都大致一样，也就是需要部署3个进程，这里我把这3个进程分别叫做抽进程，送进程，吃进程

下面开始真正的测试部署

**一、首先初始化环境**

** 源端ogg初始：**

1.首先mysql必须要要开启log-bin并且binlog 必须为row模式

binlog_format=ROW
    log-bin=/var/lib/mysql/logs/log
    # binlog index path
    log-bin-index=/var/lib/mysql/logs/binlog.index
    # enable binlog must set server-id, you can modify if there are multiple services
    server-id=1

2.创建测试账号和表

这里我再mysql里创建了一个mytest的数库，并且创建了一个叫test的表，另外创建了一个叫mytest的账号用于ogg的配置账号，这里只需要创建一个本地账号即可

mysql> create database mytest;
    Query OK, 1 row affected (0.01 sec)
    
    mysql> use mytest
    Database changed
    
    mysql> create table test(id int);
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> create user mytest@'localhost' identified by 'Test#520';
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> grant all privileges on mytest.* to  mytest@'localhost';
    Query OK, 0 rows affected, 1 warning (0.02 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.02 sec)

3.解压软件

[root@tidb2 dbmonitor]# cd ogg/
    [root@tidb2 ogg]# ll
    total 69976
    -rw-r--r-- 1 root root 71654538 Jul 19 18:04 191003_ggs_Linux_x64_MySQL_64bit.zip
    [root@tidb2 ogg]# unzip 191003_ggs_Linux_x64_MySQL_64bit.zip 
    Archive:  191003_ggs_Linux_x64_MySQL_64bit.zip
      inflating: ggs_Linux_x64_MySQL_64bit.tar  
      inflating: OGG-19.1.0.0-README.txt  
      inflating: OGG_WinUnix_Rel_Notes_19.1.0.0.3.pdf  
    [root@tidb2 ogg]# ll
    total 352552
    -rw-r--r-- 1 root root  71654538 Jul 19 18:04 191003_ggs_Linux_x64_MySQL_64bit.zip
    -rw-rw-r-- 1 root root 289024000 Sep  7  2019 ggs_Linux_x64_MySQL_64bit.tar
    -rw-r--r-- 1 root root      1413 May 30  2019 OGG-19.1.0.0-README.txt
    -rw-r--r-- 1 root root    326332 Sep 18  2019 OGG_WinUnix_Rel_Notes_19.1.0.0.3.pdf
    [root@tidb2 ogg]# tar -xvf ggs_Linux_x64_MySQL_64bit.tar

进入到ogg，执行ggsci，创建必要的目录，这里只需要执行create subdir 就会自动创建

GGSCI () 2> create subdirs
    
    Creating subdirectories under current directory /opt/dbmonitor/ogg
    
    Parameter file                 /opt/dbmonitor/ogg/dirprm: created.
    Report file                    /opt/dbmonitor/ogg/dirrpt: created.
    Checkpoint file                /opt/dbmonitor/ogg/dirchk: created.
    Process status files           /opt/dbmonitor/ogg/dirpcs: created.
    SQL script files               /opt/dbmonitor/ogg/dirsql: created.
    Database definitions files     /opt/dbmonitor/ogg/dirdef: created.
    Extract data files             /opt/dbmonitor/ogg/dirdat: created.
    Temporary files                /opt/dbmonitor/ogg/dirtmp: created.
    Credential store files         /opt/dbmonitor/ogg/dircrd: created.
    Masterkey wallet files         /opt/dbmonitor/ogg/dirwlt: created.
    Dump files                     /opt/dbmonitor/ogg/dirdmp: created.

至此源端即mysql端前期准备工作完成

**目标端ogg初始化**

在oracle 初始化ogg，如果大家搞过ogg，那应该很熟悉，[https://blog.csdn.net/shiyu1157758655/article/details/62428173][https_blog.csdn.net_shiyu1157758655_article_details_62428173] 这个链接是之前做的rac 到 单机oracle的ogg，可以参考一下

这里重点讲几个点

1.oracle 环境准备

执行下面的sql，结果都是YES即可，如果不是YES，开启他，这里就不介绍了

SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
    
    LOG_MODE     SUPPLEME FOR
    ------------ -------- ---
    ARCHIVELOG   YES      YES
    
    SQL>

2.如果之前部署过11g版本的ogg，其实部署方法同mysql差不多，解压ogg软件，ggsci进入即可，但是在19c版本的ogg中，准确的说好像12c开始，ogg初始化就可以用图像界面操作了，这有点类似安装oracle，执行./runInstaller 进入如下页面

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 2][]

这里由于我们数据库是11g，所以选择最下面11g的那个，然后一路确定即可

2.上面安装之后，就不需要再执行create subdirs，因为上面图形界面安装已经帮你创建好了

3.创建ogg的表空間及用戶，这里我们单独创建一个空间和用户用于存储和管理ogg

SQL> create tablespace ogg datafile '/oradata/hisdg/datafile/ogg01.dbf' size 10m autoextend on; 
    SQL> create user ogg identified by "ogg#2021";  
    
    SQL> alter user ogg default tablespace ogg;

4.初始化ogg

SQL> @marker_setup.sql  --该脚本用于创建DDL的marker表GGS_MARKER，用于存储DDL信息，该表只进行insert操作
    SQL> @ddl_setup.sql  --该脚本创建了进行DDL复制抽取和复制所需的对象
    SQL> @role_setup.sql      --创建DDL复制所需的对象
    SQL>  GRANT GGS_GGSUSER_ROLE TO ogg;
    SQL> @ddl_enable  -- 启用DDL复制，实质上是创建触发器，用以想MARKER和HISTORY表插入DDL信息，一般不开启
    
    注意：这里在执行脚本的时候，schema name 选择之前创建的ogg 用户，即ogg
    
    主要如果是11.2.0.4的话，还要开启enable_goldengate_replication
    
    SQL> alter system set enable_goldengate_replication=true;

至此目标端ogg初始化完成，下面就正式开始部署

**二、部署ogg**

**源端上操作，即mysql上操作**

**1.部署mgr，管理进程**

##编辑请用 edit params mgr
    GGSCI (his_106) 7> view params mgr
    
    port 7839  --Ogg端口号
    dynamicportlist  7840-7849  --动态端口号，7809被占用时动态使用
    purgeoldextracts /opt/ogg/dirdat/*,usecheckpoints, minkeepdays 2  --自动管理local trail文件，保留2天，这里路径根据自己实际情况填写
    
    
    然后启动他
    GGSCI (his_106) 7> start mgr

2.部署抽取进程，

GGSCI (his_106) 10> add extract e_mytest, tranlog, begin now
    GGSCI (his_106) 10> add exttrail ./dirdat/extlt/mytest/lt,extract e_mytest
    ##编辑请用 edit params e_mytest
    GGSCI (his_106) 10> view params e_mytest
    
    extract e_mytest
    setenv (MYSQL_HOME="/var/lib/mysql")
    DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306
    sourcedb mytest@127.0.0.1:3306 userid mytest password "Test#520"
    exttrail ./dirdat/extlt/mytest/lt --抽取出來的Trail文件存放位置
    tranLogoptions altLogDest /var/lib/mysql/logs/binlog.index  
    dynamicresolution
    gettruncates
    table mytest.*;
    
    
    GGSCI (his_106) 10> start e_mytest

3.送进程

GGSCI (his_106) 11> add extract p_mytest, exttrailsource ./dirdat/extlt/mytest/lt
    GGSCI (his_106) 11> add rmttrail /u01/app/ogg/dirdat/replt/mytest/lt, extract p_mytest
    ##编辑请用 edit params p_mytest
    GGSCI (his_106) 11> view params p_mytest
    
    extract p_mytest
    rmthost 10.158.5.222,mgrport 7809
    rmttrail /u01/app/ogg/dirdat/replt/mytest/lt  ---送到目标端的目录
    passthru --使用该参数告诉ogg同步的两端表结构一致
    gettruncates --把单词拆解出来理解：get truncates  ，即是否同步truncate操作
    
    table mytest.*;  ---要不同步的表
    
    GGSCI (his_106) 11> start p_mytest

4.配置defgen (异构数据库需要)：

利用DEFGEN工具可以为源端和目标端表生成数据定义文件，当源库和目标库类型不一致时，或源端的表和目标端的表结构不一致时，数据定义文件时必须要有的

##编辑请用 edit params def
    #创建参数文件
    GGSCI (his_106) 15> view params def
    
    DEFSFILE ./dirdef/mytest.prm         
    sourcedb mytest@127.0.0.1:3306 userid mytest password "Test#520"
    TABLE mytest.*;
    
    #生成表定义文件
    [root@his_106 ogg]#  ./defgen paramfile dirprm/def.prm
    
    将生成的def 传送到目标端，oracle 目录下
    [root@his_106 dirdef]# pwd
    /opt/ogg/dirdef
    [root@his_106 dirdef]# scp mytest.prm oracle@10.158.5.222:/u01/app/ogg/dirdef

查看各自的进程状态

GGSCI (his_106) 1> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING                                           
    EXTRACT     RUNNING     E_MYTEST    00:00:00      00:00:05    
    EXTRACT     RUNNING     P_MYTEST    00:00:00      00:00:01

至此源端操作完成，下面在目标端操作

**目标端操作：**

1.部署mgr进程

GGSCI (hisdg) 3> view params mgr
    
    PORT 7809
    autorestart extract *,waitminutes 2,resetminutes 5

2.部署吃进程

#登录
    GGSCI (hisdg) 6> dblogin userid ogg,password ogg#2021
    Successfully logged into database.
    
    #如下语句对每张需要同步的表开启表级日志传输,这里只有一张表,执行如下
    GGSCI (hisdg as ogg@hisdg) 7> add trandata  test.test1  
    
    
    GGSCI (hisdg as ogg@hisdg) 8> add replicat r_mytest, exttrail ./dirdat/replt/mytest/lt, checkpointtable ogg.chkpt
     
    GGSCI (hisdg as ogg@hisdg) 9> view params r_mytest
    
    replicat r_mytest
    sourcedefs ./dirdef/mytest.prm
    userid ogg,password "ogg#2021"
    reperror default,discard
    discardfile ./dirrpt/repl01.dsc,append,megabytes 50
    dynamicresolution
    map mytest.test, target test.test1; 
    
    GGSCI (hisdg as ogg@hisdg) 10> start r_mytet

查看状态

GGSCI (hisdg as ogg@hisdg) 10> info all
    
    Program     Status      Group       Lag at Chkpt  Time Since Chkpt
    
    MANAGER     RUNNING                                           
    REPLICAT    RUNNING     R_MYTEST    00:00:00      00:00:01

至此mysql to oracle ogg通过已经完成，下面测试是否正常同步

##mysql 操作
    mysql> select * from test;
    +------+
    | id   |
    +------+
    |    1 |
    |    3 |
    |    5 |
    |    6 |
    +------+
    4 rows in set (0.02 sec)
    
    #插入一笔数据测试
    mysql> insert into test select 10 ;
    Query OK, 1 row affected (0.03 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    
    #oracle 里查看是否同步过来
    SQL> select * from test1;
    
            ID
    ----------
             1
             3
             5
             6
    
    SQL> select * from test1;
    
            ID
    ----------
             1
             3
             5
             6
            10

从上面可以看出mysql mytest库test 的表数据已经通过ogg同步到oracle test用户下 test1表中去

至此ogg 部署完成。

[https_www.oracle.com_middleware_technologies_goldengate-downloads.html]: https://www.oracle.com/middleware/technologies/goldengate-downloads.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70]: /images/20220829/58bbafbf4b2e4192b52447dce59e95a8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 1]: /images/20220829/dc6edad64514407d8e2bb1c3b5a5a4ea.png
[https_blog.csdn.net_shiyu1157758655_article_details_62428173]: https://blog.csdn.net/shiyu1157758655/article/details/62428173
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoaXl1MTE1Nzc1ODY1NQ_size_16_color_FFFFFF_t_70 2]: /images/20220829/4ec67667118d4243a5f8f61aa88e0da7.png