使用dataX将数据从Mysql数据库同步到Oracle数据

最近有需求将数据从mysql同步到oracle，之前有使用kettle将表从oracle同步到mysql，这里使用的插件依然是阿里的dataX

详细见：Githup地址：https://github.com/alibaba/DataX

这里也是根据阿里云的一篇帖子来进行验证

https://yq.aliyun.com/articles/715393?spm=a2c4e.11155472.0.0.3aef4dc1IWo28F

1 环境需要

    JDK(1.8以上，推荐1.8)
    Python(推荐Python2.6.X)
    Apache Maven 3.x (Compile DataX)

2 #服务器配置

[ ~]# yum search java|grep jdk
[ ~]# yum install java-1.8.0-openjdk.x86_64 -y
[ ~]# java -version
openjdk version "1.8.0_242"
OpenJDK Runtime Environment (build 1.8.0_242-b07)
OpenJDK 64-Bit Server VM (build 25.242-b07, mixed mode)
[root@yhq ~]# python --version
Python 2.6.6
下载地址：https://maven.apache.org/download.cgi
[root@yhq soft]# ls apache-maven-3.6.3-bin.tar.gz 
apache-maven-3.6.3-bin.tar.gz
[root@yhq soft]# tar -zxvf apache-maven-3.6.3-bin.tar.gz 
[ soft]#  mkdir /opt/maven
[ soft]# mv apache-maven-3.6.3/* /opt/maven/
[ soft]# ln -s /opt/maven/b
bin/  boot/ 
[root@yhq soft]# ln -s /opt/maven/bin/mvn /usr/bin/mvn
[ soft]# vim /etc/profile.d/maven.sh
export M2_HOME=/opt/maven
export PATH=${M2_HOME}/bin:${PATH}
[root@yhq soft]# mvn -v
Apache Maven 3.6.3 (cecedd343002696d0abb50b32b541b8a6ba2883f)
Maven home: /opt/maven
Java version: 1.8.0_242, vendor: Oracle Corporation, runtime: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b07-1.el6_10.x86_64/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "2.6.32-754.el6.x86_64", arch: "amd64", family: "unix"
#安装datax
[ soft]# tar -zxvf datax.tar.gz
[ soft]# ls datax
bin  conf  job  lib  plugin  script  tmp
#自检测试脚本
[ soft]# python datax/bin/datax.py datax/job/job.json 

DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.


2020-02-09 23:27:35.717 [main] INFO  VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2020-02-09 23:27:35.763 [main] INFO  Engine - the machine info  => 

    osInfo:    Oracle Corporation 1.8 25.242-b07
    jvmInfo:    Linux amd64 2.6.32-754.el6.x86_64
    cpu num:    2
2020-02-09 23:27:46.974 [job-0] INFO  JobContainer - PerfTrace not enable!
2020-02-09 23:27:46.975 [job-0] INFO  StandAloneJobContainerCommunicator - Total 100000 records, 2600000 bytes | Speed 253.91KB/s, 10000 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 0.020s |  All Task WaitReaderTime 0.121s | Percentage 100.00%
2020-02-09 23:27:46.976 [job-0] INFO  JobContainer - 
任务启动时刻                    : 2020-02-09 23:27:35
任务结束时刻                    : 2020-02-09 23:27:46
任务总计耗时                    :                 10s
任务平均流量                    :          253.91KB/s
记录写入速度                    :          10000rec/s
读出记录总数                    :              100000
读写失败总数                    :                   0

#测试使用

mysql 表
 CREATE TABLE `datax_test` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `memo` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#插入10w数据

#在oracle里面可以先创建好表结构，或者用kettle同步表

create table datax_test
(id number,
name varchar2(20),
birthday date,
memo varchar2(32));

#创建配置文件
#可以通过命令查看配置模板： python datax.py -r {YOUR_READER} -w {YOUR_WRITER}

#  python datax.py -r streamreader -w streamwriter
[ job]# vim job1.json
{
    "job": {
        "setting": {
            "speed": {
                "channel": 5
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "system",
                        "password": "****",
                        "column": ["*"],
                        "connection": [
                           {
                              "table": ["datax_test"],
                              "jdbcUrl": ["jdbc:mysql://ip:3306/vision"]
                           }
                        ]
                    }
                },
                "writer": {
                    "name": "oraclewriter",
                    "parameter": {
                        "username": "vision",
                        "password": "***",
                        "column": ["*"],
                        "connection": [
                            {
                               "jdbcUrl": "jdbc:oracle:thin:@ip:1521:bol",
                               "table": ["datax_test"]
                            }
                        ]
                      }
                    }
                }
        ]
    }
}

#启动数据同步

[ job]# python /data/soft/datax/bin/datax.py job1.json 

DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.


2020-02-10 00:12:03.796 [main] INFO  VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2020-02-10 00:12:03.807 [main] INFO  Engine - the machine info  => 

    osInfo:    Oracle Corporation 1.8 25.242-b07
    jvmInfo:    Linux amd64 2.6.32-754.el6.x86_64
    cpu num:    2

    totalPhysicalMemory:    -0.00G
    freePhysicalMemory:    -0.00G
    maxFileDescriptorCount:    -1
    currentOpenFileDescriptorCount:    -1

    GC Names    [PS MarkSweep, PS Scavenge]

    MEMORY_NAME                    | allocation_size                | init_size                      
    PS Eden Space                  | 256.00MB                       | 256.00MB                       
    Code Cache                     | 240.00MB                       | 2.44MB                         
    Compressed Class Space         | 1,024.00MB                     | 0.00MB                         
    PS Survivor Space              | 42.50MB                        | 42.50MB                        
    PS Old Gen                     | 683.00MB                       | 683.00MB                       
    Metaspace                      | -0.00MB                        | 0.00MB                         


2020-02-10 00:12:03.869 [main] INFO  Engine - 
2020-02-10 00:13:48.061 [job-0] INFO  JobContainer - PerfTrace not enable!
2020-02-10 00:13:48.061 [job-0] INFO  StandAloneJobContainerCommunicator - Total 99999 records, 1888875 bytes | Speed 21.96KB/s, 1190 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 42.323s |  All Task WaitReaderTime 29.424s | Percentage 100.00%
2020-02-10 00:13:48.069 [job-0] INFO  JobContainer - 
任务启动时刻                    : 2020-02-10 00:12:05
任务结束时刻                    : 2020-02-10 00:13:48
任务总计耗时                    :                102s
任务平均流量                    :           21.96KB/s
记录写入速度                    :           1190rec/s
读出记录总数                    :               99999
读写失败总数                    :                   0

#在oracle查看数据
select count(*) from datax_test; #同步正常
#剩下的增量同步，在crontab中定时任务就行。
$ crontab -e
#会进入已有crontab文件编辑界面，继续增加定时任务即可，本示例增加以下内容,并保存
0,10,20,30,40,50 * * * *  python /data/soft/datax/bin/datax.py /data/soft/datax/job/mysql2odps.json  >>/tmp/log.`date +\%Y\%m\%d\%H\%M\%S`  2>&1

测试环境

#测试环境
==mysql db 5.7.27 centos 6.10
create database vision;==oracle db 11.2.0.4  centos 6.10
select file#,name from v$datafile;

create tablespace vision
  logging
  datafile ‘/u01/app/oracle/oradata/bol/vision01.dbf‘
  size 100m
  autoextend on
  next 500m;
  
  create user vision identified by ****  default tablespace vision ;
  grant connect,resource,dba to vision;==
delimiter //
create procedure insrt_t ()
begin
declare i int default 1;
declare j int default 1;
while i < 100000 do
#insert into test values (i,‘a‘);
insert into datax_test
(id,name,birthday,memo)
VALUES
(i,‘yhq‘,now(),‘123‘);
set i=i+1;
set j=j+1;
if j=5000 then
set j=1;
commit;
end if;
end while;
end
//
delimiter ;