2

使用DataX将oracle中的数据同步到MySQL

 1 year ago
source link: https://blog.51cto.com/u_13753753/5765113
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

使用DataX将oracle中的数据同步到MySQL_雍州无名的技术博客_51CTO博客

{    "job": {        "setting": {            "speed": {                "channel": 5            }        },        "content": [            {                "reader": {                    "name": "oraclereader",                    "parameter": {                        "username": "test",                        "password": "test",                        "where": "",                        "mandatoryEncoding": "UTF8",                        "connection": [                            {                                "querySql": ["    SELECT  user_code,user_name  FROM  ORACLE_DATAX1 "                                ],                                "jdbcUrl": [            "jdbc:oracle:thin:@10.2.3.223:1521/orcl"                                ]                            }                        ]                    }                },                "writer": {                    "name": "mysqlwriter",                    "parameter": {                          "column": ["user_code","user_name"],            "connection": [                            {                                "jdbcUrl": "jdbc:mysql://10.2.3.114:3306/wang?useUnicode=true&characterEncoding=utf8",                                "table": ["wang_datax1"]                            }                        ],                        "password": "123456",                        "username": "user",                        "preSql": ["truncate table wang_datax1"],                        "segment_reject_limit": 0,                        "copy_queue_size": 100000,                        "num_copy_processor": 4,                        "num_copy_writer": 1                    }                }            }        ]    }}



然后将oracle-to-mysql拷贝到 /opt/datax/job 目录



使用DataX将oracle中的数据同步到MySQL_oracle



3.将oracle中的数据同步到MySQL
[root@db9 ~]# python /opt/datax/bin/datax.py /opt/datax/job/oracle-to-mysql

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


2022-10-17 22:49:56.639 [main] INFO MessageSource - JVM TimeZone: GMT+08:00, Locale: zh_CN
2022-10-17 22:49:56.642 [main] INFO MessageSource - use Locale: zh_CN timeZone: sun.util.calendar.ZoneInfo[id="GMT+08:00",offset=28800000,dstSavings=0,useDaylight=false,transitions=0,lastRule=null]
2022-10-17 22:49:56.658 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2022-10-17 22:49:56.663 [main] INFO Engine - the machine info =>

osInfo: Oracle Corporation 1.8 25.121-b13
jvmInfo: Linux amd64 3.10.0-1160.el7.x86_64
cpu num: 4

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


2022-10-17 22:49:56.685 [main] INFO Engine -
{
"content":[
{
"reader":{
"name":"oraclereader",
"parameter":{
"connection":[
{
"jdbcUrl":[
"jdbc:oracle:thin:@10.2.3.223:1521/orcl"
],
"querySql":[
"\tSELECT user_code,user_name FROM ORACLE_DATAX1 "
]
}
],
"mandatoryEncoding":"UTF8",
"password":"****",
"username":"test",
"where":""
}
},
"writer":{
"name":"mysqlwriter",
"parameter":{
"column":[
"user_code",
"user_name"
],
"connection":[
{
"jdbcUrl":"jdbc:mysql://10.2.3.114:3306/wang?useUnicode=true&characterEncoding=utf8",
"table":[
"wang_datax1"
]
}
],
"copy_queue_size":100000,
"num_copy_processor":4,
"num_copy_writer":1,
"password":"****************",
"preSql":[
"truncate table wang_datax1"
],
"segment_reject_limit":0,
"username":"user"
}
}
}
],
"setting":{
"speed":{
"channel":5
}
}
}

2022-10-17 22:49:56.708 [main] WARN Engine - prioriy set to 0, because NumberFormatException, the value is: null
2022-10-17 22:49:56.710 [main] INFO PerfTrace - PerfTrace traceId=job_-1, isEnable=false, priority=0
2022-10-17 22:49:56.711 [main] INFO JobContainer - DataX jobContainer starts job.
2022-10-17 22:49:56.713 [main] INFO JobContainer - Set jobId = 0
2022-10-17 22:49:57.062 [job-0] INFO OriginalConfPretreatmentUtil - Available jdbcUrl:jdbc:oracle:thin:@10.2.3.223:1521/orcl.
2022-10-17 22:49:57.363 [job-0] INFO OriginalConfPretreatmentUtil - table:[wang_datax1] all columns:[
id,user_code,user_name,create_time
].
2022-10-17 22:49:57.374 [job-0] INFO OriginalConfPretreatmentUtil - Write data [
INSERT INTO %s (user_code,user_name) VALUES(?,?)
], which jdbcUrl like:[jdbc:mysql://10.2.3.114:3306/wang?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&tinyInt1isBit=false]
2022-10-17 22:49:57.375 [job-0] INFO JobContainer - jobContainer starts to do prepare ...
2022-10-17 22:49:57.375 [job-0] INFO JobContainer - DataX Reader.Job [oraclereader] do prepare work .
2022-10-17 22:49:57.376 [job-0] INFO JobContainer - DataX Writer.Job [mysqlwriter] do prepare work .
2022-10-17 22:49:57.384 [job-0] INFO CommonRdbmsWriterJob - Begin to execute preSqls:[truncate table wang_datax1]. context info:jdbc:mysql://10.2.3.114:3306/wang?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=true&tinyInt1isBit=false. 2022-10-17 22:49:57.496 [job-0] INFO  JobContainer - jobContainer starts to do split ... 2022-10-17 22:49:57.497 [job-0] INFO  JobContainer - Job set Channel-Number to 5 channels. 2022-10-17 22:49:57.500 [job-0] INFO  JobContainer - DataX Reader.Job [oraclereader] splits to [1] tasks. 2022-10-17 22:49:57.501 [job-0] INFO  JobContainer - DataX Writer.Job [mysqlwriter] splits to [1] tasks. 2022-10-17 22:49:57.521 [job-0] INFO  JobContainer - jobContainer starts to do schedule ... 2022-10-17 22:49:57.526 [job-0] INFO  JobContainer - Scheduler starts [1] taskGroups. 2022-10-17 22:49:57.529 [job-0] INFO  JobContainer - Running by standalone Mode. 2022-10-17 22:49:57.536 [taskGroup-0] INFO  TaskGroupContainer - taskGroupId=[0] start [1] channels for [1] tasks. 2022-10-17 22:49:57.544 [taskGroup-0] INFO  Channel - Channel set byte_speed_limit to -1, No bps activated. 2022-10-17 22:49:57.544 [taskGroup-0] INFO  Channel - Channel set record_speed_limit to -1, No tps activated. 2022-10-17 22:49:57.561 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] taskId[0] attemptCount[1] is started 2022-10-17 22:49:57.567 [0-0-0-reader] INFO  CommonRdbmsReaderTask - Begin to read record by Sql: [ SELECT user_code,user_name FROM ORACLE_DATAX1
] jdbcUrl:[jdbc:oracle:thin:@10.2.3.223:1521/orcl].
2022-10-17 22:49:57.735 [0-0-0-reader] INFO CommonRdbmsReader$Task - Finished read record by Sql: [ SELECT user_code,user_name FROM ORACLE_DATAX1
] jdbcUrl:[jdbc:oracle:thin:@10.2.3.223:1521/orcl].
2022-10-17 22:49:57.862 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] is successed, used[308]ms
2022-10-17 22:49:57.863 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] completed it's tasks.
2022-10-17 22:50:07.548 [job-0] INFO StandAloneJobContainerCommunicator - Total 3 records, 18 bytes | Speed 1B/s, 0 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2022-10-17 22:50:07.548 [job-0] INFO AbstractScheduler - Scheduler accomplished all tasks.
2022-10-17 22:50:07.548 [job-0] INFO JobContainer - DataX Writer.Job [mysqlwriter] do post work.
2022-10-17 22:50:07.548 [job-0] INFO JobContainer - DataX Reader.Job [oraclereader] do post work.
2022-10-17 22:50:07.549 [job-0] INFO JobContainer - DataX jobId [0] completed successfully.
2022-10-17 22:50:07.550 [job-0] INFO HookInvoker - No hook invoked, because base dir not exists or is a file: /opt/datax/hook
2022-10-17 22:50:07.551 [job-0] INFO JobContainer -
[total cpu info] =>
averageCpu | maxDeltaCpu | minDeltaCpu
-1.00% | -1.00% | -1.00%
[total gc info] =>
NAME | totalGCCount | maxDeltaGCCount | minDeltaGCCount | totalGCTime | maxDeltaGCTime | minDeltaGCTime
PS MarkSweep | 0 | 0 | 0 | 0.000s | 0.000s | 0.000s
PS Scavenge | 0 | 0 | 0 | 0.000s | 0.000s | 0.000s

2022-10-17 22:50:07.551 [job-0] INFO JobContainer - PerfTrace not enable!
2022-10-17 22:50:07.552 [job-0] INFO StandAloneJobContainerCommunicator - Total 3 records, 18 bytes | Speed 1B/s, 0 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2022-10-17 22:50:07.553 [job-0] INFO JobContainer -
任务启动时刻 : 2022-10-17 22:49:56
任务结束时刻 : 2022-10-17 22:50:07
任务总计耗时 : 10s
任务平均流量 : 1B/s
记录写入速度 : 0rec/s
读出记录总数 : 3
读写失败总数 : 0

[root@db9 ~]#

        

        

            

                report
            

        

    







        
Recommend

        



                

    
About Joyk

    
 





Aggregate valuable and interesting links.

Joyk means Joy of geeK