1.环境准备
(1).在oracle上创建表及数据
create table ORACLE_DATAX1
(
user_code VARCHAR2(20) not null,
user_name VARCHAR2(50)
);
insert into ORACLE_DATAX1 (user_code, user_name)
values ('A001', '张三');
insert into ORACLE_DATAX1 (user_code, user_name)
values ('A002', '李四');
insert into ORACLE_DATAX1 (user_code, user_name)
values ('A003', '王五');
commit;
(2).在mysql上创建表
CREATE TABLE `wang_datax1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增长流水号',
`user_code` varchar(10) NOT NULL COMMENT '代码',
`user_name` varchar(50) NOT NULL COMMENT '名称',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`),
UNIQUE KEY `user_code` (`user_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表1';
2.创建名称为joboracle-to-mysql的job
{
"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 目录
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 ~]#
查看数据是否同步成功
