Oracle到MySQL的数据同步

以下步骤以docker容器的方式快速演示如何搭建Oracle到MySQL的单向复制环境.

创建网络

docker network create dtle-net

创建源端 Oracle

  # 启动oracle镜像
    docker run -it -d -p 1521:1521 --name oracle-src --network=dtle-net  -e ORACLE_ALLOW_REMOTE=true  wnameless/oracle-xe-11g-r2

  # 环境配置并启动oracle
    docker exec -it oracle-src bash
    mkdir /u01/app/oracle/oradata/archive_log
    chown oracle /u01/app/oracle/oradata/archive_log

    export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
    export PATH=$ORACLE_HOME/bin:$PATH
    export ORACLE_SID=XE

    service oracle-xe start

  # 设置同步配置
    sqlplus SYS/oracle AS SYSDBA  
    alter system set log_archive_dest_1='location=/u01/app/oracle/oradata/archive_log' scope=spfile;
    alter system set db_recovery_file_dest_size = 10G;

    shutdown immediate;
    startup mount;
    alter database add logfile group 3 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/redo01.log' size 500m;
    alter database add logfile group 4 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/redo02.log' size 500m;
    alter database add logfile group 5 '/u01/app/oracle/fast_recovery_area/XE/onlinelog/redo03.log' size 500m;
    alter database archivelog;
    alter database add supplemental log data (all) columns;
    alter database open;

  # 创建同步账号
    create role roma_logminer_privs;
    grant create session,execute_catalog_role,select any transaction,select_catalog_role,select any dictionary to roma_logminer_privs;
    grant select on SYSTEM.LOGMNR_COL$ to roma_logminer_privs;
    grant select on SYSTEM.LOGMNR_OBJ$ to roma_logminer_privs;
    grant select on SYSTEM.LOGMNR_USER$ to roma_logminer_privs;
    grant select on SYSTEM.LOGMNR_UID$ to roma_logminer_privs;
    create user roma_logminer identified by oracle default tablespace users;
    grant roma_logminer_privs to roma_logminer;
    alter user roma_logminer quota unlimited on users;

创建目标端 MySQL

docker run --name mysql-dst -e MYSQL_ROOT_PASSWORD=pass -p 33061:3306 --network=dtle-net -d mysql:5.7 --gtid-mode=ON --enforce-gtid-consistency=1 --log-bin=bin --server-id=1

检查是否联通:

> mysql -h 127.0.0.1 -P 33061 -uroot -ppass -e "select @@version\G"
< *************************** 1. row ***************************
@@version: 5.7.23-log

创建 dtle

docker run --name dtle-consul -p 8500:8500 --network=dtle-net -d consul:latest
docker run --name dtle -p 4646:4646 --network=dtle-net -d actiontech/dtle
# 如需要使用dtle 2.x HTTP API兼容层，则需要额外映射8190端口：-p 8190:8190

检查是否正常:

> curl -XGET "127.0.0.1:4646/v1/nodes" -s | jq
< [
    {
      "Address": "127.0.0.1",
      "Datacenter": "dc1",
      "Drivers": {
        "dtle": {
          "Attributes": {
            "driver.dtle": "true",
            "driver.dtle.version": "..."
          },
          "Detected": true,
          "Healthy": true,
        }
      },
      "ID": "65ff2f9a-a9fa-997c-cce0-9bc0b4f3396c",
      "Name": "nomad0",
      "Status": "ready",
    }
  ]
# （部分项目省略）

准备作业定义文件

准备文件job.json, 内容如下:

{
  "Job": {
    "ID": "dtle-demo",
    "Datacenters": ["dc1"],
    "TaskGroups": [{
      "Name": "src",
      "Tasks": [{
        "Name": "src",
        "Driver": "dtle",
        "Config": {
          "ReplicateDoDb": [{
            "TableSchema": "TEST",
            "Tables": [{
              "TableName": "t1"
            }]
          }],
          "SrcOracleConfig": {
            "User":        "roma_logminer",
            "Password":    "oracle",
            "Host":        "oracle-src",
            "Port":        1521,
            "ServiceName": "XE",
            "Scn": 0
          },
          "DestConnectionConfig": {
            "Host": "mysql-dst",
            "Port": 3306,
            "User": "root",
            "Password": "pass"
          }
        }
      }]
    }, {
      "Name": "dest",
      "Tasks": [{
        "Name": "dest",
        "Driver": "dtle",
        "Config": {
          "DestType": "mysql"
        }
    }]
   }]
  }
}

其中定义了:

• 源端 Oracle 的连接配置
• 目标端 MySQL 的连接配置
• 要复制的表为TEST.t1
• SCN点位为0, 表示此复制是从任务启动时间点开始复制. 如需测试指定位置增量复制, 可指定合法的SCN

创建复制任务

> curl -XPOST "http://127.0.0.1:4646/v1/jobs" -d @job.json -s | jq
< {...}

查看作业状态:

> curl -XGET "127.0.0.1:4646/v1/job/dtle-demo" -s | jq '.Status'
< "running"

测试

在源端写入数据:

  sqlplus SYS/oracle AS SYSDBA  
  create user TEST identified by oracle;
  grant connect,resource to TEST;
  create table TEST."t1" (id int,name varchar(255));
  insert into TEST."t1" values(1,'ryan');
  commit;

验证目标端数据

  查看目标端数据是否一致

字段映射关系参看 5.4 Oracle MySQL 字段映射