环境配置（官方推荐）：
JDK(1.8以上，推荐1.8)
Python(推荐Python2.6.X)
下载datax工具：http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
解压后就能使用。
目录结构如下:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
[root@rancher1 datax]# pwd/datax[root@rancher1 datax]# ls -lhtotal 4.0Kdrwxr-xr-x 2 62265 users 59 Jul 8 17:42 bindrwxr-xr-x 2 62265 users 68 Oct 12 2019 confdrwxr-xr-x 2 62265 users 85 Jul 8 18:39 jobdrwxr-xr-x 2 62265 users 4.0K Oct 12 2019 libdrwxr-xr-x 3 root root 24 Jul 8 17:22 logdrwxr-xr-x 3 root root 24 Jul 8 17:22 log_perfdrwxr-xr-x 4 62265 users 34 Oct 12 2019 plugindrwxr-xr-x 2 62265 users 23 Oct 12 2019 scriptdrwxr-xr-x 2 62265 users 24 Oct 12 2019 tmp |
ORACLE服务器信息：
监听信息：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
[oracle@exam ~]$ lsnrctl statusLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 08-JUL-2020 19:45:54Copyright (c) 1991, 2013, Oracle. All rights reserved.Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=exam)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionStart Date 08-JUL-2020 16:51:50Uptime 0 days 2 hr. 54 min. 4 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/oracle/diag/tnslsnr/exam/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=exam)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Services Summary...Service "exam" has 1 instance(s). Instance "exam", status READY, has 1 handler(s) for this service...Service "examXDB" has 1 instance(s). Instance "exam", status READY, has 1 handler(s) for this service...The command completed successfully |
表结构：
|
1
2
3
4
5
6
7
|
SQL> desc issue.db_hosts; Name Null? Type ----------------------------------------- -------- ---------------------------- DB_ID NUMBER(16) HOST_NAME VARCHAR2(256 CHAR) IP VARCHAR2(30 CHAR) RAC_ID NUMBER(16) |
mysql服务器信息：
|
1
|
mysql 2569 1276 0 16:47 ? 00:00:11 /u01/mysql-5.7.27/bin/mysqld --basedir=/u01/mysql-5.7.27 --datadir=/u01/mysql-5.7.27/data --plugin-dir=/u01/mysql-5.7.27/lib/plugin --user=mysql --log-error=/u01/mysql-5.7.27/log/mysql_error.log --open-files-limit=65535 --pid-file=/u01/mysql-5.7.27/mysql.pid --socket=/u01/mysql-5.7.27/mysql.sock --port=3306 |
|
1
2
3
4
5
6
7
8
9
10
|
mysql> desc issue.db_hosts;+-----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------+-------------+------+-----+---------+-------+| db_id | bigint(20) | YES | | NULL | || host_name | varchar(50) | YES | | NULL | || ip | varchar(20) | YES | | NULL | || rac_id | int(11) | YES | | NULL | |+-----------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec) |
配置数据抽取的json文件：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
[root@rancher1 job]# cat db_host.json{ "job": { "content": [ { "reader": { "name": "oraclereader", "parameter": { "username": "issue", "password": "issue", "column": [ "*" ], "connection": [ { "table": [ "db_hosts" ], "jdbcUrl": [ "jdbc:oracle:thin:@192.168.0.210:1521:exam" ] } ] } }, "writer": { "name": "mysqlwriter", "parameter": { "writeMode": "insert", "username": "root", "password": "123456", "column": [ "db_id","host_name","ip","rac_id" ], "preSql": [ "truncate table db_hosts" ], "connection": [ { "jdbcUrl": "jdbc:mysql://192.168.56.140:3306/issue?useUnicode=true&characterEncoding=utf8", "table": [ "db_hosts" ] } ] } } } ], "setting": { "speed": { "channel": 5 } } }} |
执行抽取：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
[root@rancher1 bin]# python datax.py ../job/db_host.jsonDataX (DATAX-OPENSOURCE-3.0), From Alibaba !Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.2020-07-08 19:56:11.019 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl2020-07-08 19:56:11.029 [main] INFO Engine - the machine info => osInfo: Oracle Corporation 1.8 25.161-b14 jvmInfo: Linux amd64 5.5.10-1.el7.elrepo.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-07-08 19:56:11.054 [main] INFO Engine -{ "content":[ { "reader":{ "name":"oraclereader", "parameter":{ "column":[ "*" ], "connection":[ { "jdbcUrl":[ "jdbc:oracle:thin:@192.168.0.210:1521:exam" ], "table":[ "db_hosts" ] } ], "password":"*****", "username":"issue" } }, "writer":{ "name":"mysqlwriter", "parameter":{ "column":[ "db_id", "host_name", "ip", "rac_id" ], "connection":[ { "jdbcUrl":"jdbc:mysql://192.168.56.140:3306/issue?useUnicode=true&characterEncoding=utf8", "table":[ "db_hosts" ] } ], "password":"******", "preSql":[ "truncate table db_hosts" ], "username":"root", "writeMode":"insert" } } } ], "setting":{ "speed":{ "channel":5 } }}2020-07-08 19:56:11.080 [main] WARN Engine - prioriy set to 0, because NumberFormatException, the value is: null2020-07-08 19:56:11.083 [main] INFO PerfTrace - PerfTrace traceId=job_-1, isEnable=false, priority=02020-07-08 19:56:11.083 [main] INFO JobContainer - DataX jobContainer starts job.2020-07-08 19:56:11.086 [main] INFO JobContainer - Set jobId = 02020-07-08 19:56:11.498 [job-0] INFO OriginalConfPretreatmentUtil - Available jdbcUrl:jdbc:oracle:thin:@192.168.0.210:1521:exam.2020-07-08 19:56:11.500 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置文件中的列配置存在一定的风险. 因为您未配置读取数据库表的列，当您的表字段个数、类型有变动时，可能影响任务正确性甚至会运行出错。请检查您的配置并作出修改.2020-07-08 19:56:11.816 [job-0] INFO OriginalConfPretreatmentUtil - table:[db_hosts] all columns:[db_id,host_name,ip,rac_id].2020-07-08 19:56:11.833 [job-0] INFO OriginalConfPretreatmentUtil - Write data [insert INTO %s (db_id,host_name,ip,rac_id) VALUES(?,?,?,?)], which jdbcUrl like:[jdbc:mysql://192.168.56.140:3306/issue?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true]2020-07-08 19:56:11.833 [job-0] INFO JobContainer - jobContainer starts to do prepare ...2020-07-08 19:56:11.834 [job-0] INFO JobContainer - DataX Reader.Job [oraclereader] do prepare work .2020-07-08 19:56:11.835 [job-0] INFO JobContainer - DataX Writer.Job [mysqlwriter] do prepare work .2020-07-08 19:56:11.847 [job-0] INFO CommonRdbmsWriter$Job - Begin to execute preSqls:[truncate table db_hosts]. context info:jdbc:mysql://192.168.56.140:3306/issue?useUnicode=true&characterEncoding=utf8&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true.2020-07-08 19:56:11.857 [job-0] INFO JobContainer - jobContainer starts to do split ...2020-07-08 19:56:11.858 [job-0] INFO JobContainer - Job set Channel-Number to 5 channels.2020-07-08 19:56:11.865 [job-0] INFO JobContainer - DataX Reader.Job [oraclereader] splits to [1] tasks.2020-07-08 19:56:11.865 [job-0] INFO JobContainer - DataX Writer.Job [mysqlwriter] splits to [1] tasks.2020-07-08 19:56:11.887 [job-0] INFO JobContainer - jobContainer starts to do schedule ...2020-07-08 19:56:11.891 [job-0] INFO JobContainer - Scheduler starts [1] taskGroups.2020-07-08 19:56:11.895 [job-0] INFO JobContainer - Running by standalone Mode.2020-07-08 19:56:11.909 [taskGroup-0] INFO TaskGroupContainer - taskGroupId=[0] start [1] channels for [1] tasks.2020-07-08 19:56:11.923 [taskGroup-0] INFO Channel - Channel set byte_speed_limit to -1, No bps activated.2020-07-08 19:56:11.923 [taskGroup-0] INFO Channel - Channel set record_speed_limit to -1, No tps activated.2020-07-08 19:56:11.939 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] attemptCount[1] is started2020-07-08 19:56:11.947 [0-0-0-reader] INFO CommonRdbmsReader$Task - Begin to read record by Sql: [select * from db_hosts] jdbcUrl:[jdbc:oracle:thin:@192.168.0.210:1521:exam].2020-07-08 19:56:12.117 [0-0-0-reader] INFO CommonRdbmsReader$Task - Finished read record by Sql: [select * from db_hosts] jdbcUrl:[jdbc:oracle:thin:@192.168.0.210:1521:exam].2020-07-08 19:56:12.376 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] is successed, used[442]ms2020-07-08 19:56:12.377 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] completed it's tasks.2020-07-08 19:56:21.924 [job-0] INFO StandAloneJobContainerCommunicator - Total 667 records, 21266 bytes | Speed 2.08KB/s, 66 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.003s | All Task WaitReaderTime 0.140s | Percentage 100.00%2020-07-08 19:56:21.925 [job-0] INFO AbstractScheduler - Scheduler accomplished all tasks.2020-07-08 19:56:21.925 [job-0] INFO JobContainer - DataX Writer.Job [mysqlwriter] do post work.2020-07-08 19:56:21.925 [job-0] INFO JobContainer - DataX Reader.Job [oraclereader] do post work.2020-07-08 19:56:21.926 [job-0] INFO JobContainer - DataX jobId [0] completed successfully.2020-07-08 19:56:21.927 [job-0] INFO HookInvoker - No hook invoked, because base dir not exists or is a file: /datax/hook2020-07-08 19:56:21.929 [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 | 1 | 1 | 1 | 0.054s | 0.054s | 0.054s PS Scavenge | 1 | 1 | 1 | 0.032s | 0.032s | 0.032s 2020-07-08 19:56:21.930 [job-0] INFO JobContainer - PerfTrace not enable!2020-07-08 19:56:21.931 [job-0] INFO StandAloneJobContainerCommunicator - Total 667 records, 21266 bytes | Speed 2.08KB/s, 66 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.003s | All Task WaitReaderTime 0.140s | Percentage 100.00%2020-07-08 19:56:21.932 [job-0] INFO JobContainer -任务启动时刻 : 2020-07-08 19:56:11任务结束时刻 : 2020-07-08 19:56:21任务总计耗时 : 10s任务平均流量 : 2.08KB/s记录写入速度 : 66rec/s读出记录总数 : 667读写失败总数 : 0[root@rancher1 bin]# |
查看结果：
|
1
2
3
4
5
6
7
8
9
10
|
mysql> select * from issue.db_hosts;+------------+------------------------+----------------+--------+| db_id | host_name | ip | rac_id |+------------+------------------------+----------------+--------+| 500011196 | dxxxxxxxxx3 | 10.xxx.xxx.174 | 28 || 500014437 | dxxxx | 10.xxx.xxx.30 | 10 || 500014437 | dxxxx4 | 10.1xx.xxx.33 | 10 || 500014437 | dxxxx | 10.xxx.xxx.39 | 10 || 500014437 | dbxx | 10.xxx.xxx.38 | 10 || 500014437 | dbxx | 10.xxx.xxx.37 | 10 | |
感觉配置还是比较简单的，抽取大量数据库没有测试不晓得性能咋样；
大批量表数据迁移时候需要些脚本生成对应的json（配置json文件很容易出错），具体每个类型数据库的json写法参考https://github.com/alibaba/DataX
oracleread：https://github.com/alibaba/DataX/blob/master/oraclereader/doc
mysqlwrite：https://github.com/alibaba/DataX/tree/master/mysqlwriter/doc
表结构可以借助powerdesign工具实行转换，个别表单独修改；
kettle工具也能实现异构数据库的数据同步，提供操作界面拖一拖就能完成（没有实际的项目操作过，性能啥的没对比过）。
java实现oracle数据库json配置文件拼接
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
|
package com.product;import org.springframework.context.ApplicationContext;import org.springframework.context.support.ClassPathXmlApplicationContext;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.core.RowMapper;import org.json.JSONObject;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.LinkedList;import java.util.List;import java.util.Map;public class Product_json { public static void main(String[] args) throws SQLException { String xmlpath = "bean.xml"; ApplicationContext context = new ClassPathXmlApplicationContext(xmlpath); JdbcTemplate query = (JdbcTemplate) context.getBean("jdbctemplate"); String sql="select table_name from user_tables where rownum=1"; List<String> table_name = query.query(sql, new RowMapper<String>(){ public String mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getString(1); } }); sql ="select COLUMN_NAME from user_tab_cols where table_name=?"; for(int j=0;j<table_name.size();j++) { List<String> column_name = query.query(sql, new RowMapper<String>(){ public String mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getString(1); } },table_name.get(j)); //reader数据库连接信息 List<JSONObject> list_re_con = new ArrayList<>(); JSONObject json_re_con = new JSONObject(); String[] re_table = new String[] { table_name.get(j)}; String[] re_jdbcUrl = new String[] { "jdbc:oracle:thin:@192.168.0.210:1521:exam"}; json_re_con.put("jdbcUrl",re_jdbcUrl); json_re_con.put("table",re_table); list_re_con.add(json_re_con); System.out.println(json_re_con); //write 数据库连接信息 List<JSONObject> list_wr_con = new ArrayList<>(); JSONObject json_wr_con = new JSONObject(); String[] wr_table = new String[] { table_name.get(j)}; String[] wr_jdbcUrl = new String[] { "jdbc:mysql://192.168.56.140:3306/issue?useUnicode=true&characterEncoding=utf8"}; json_wr_con.put("jdbcUrl",wr_jdbcUrl); json_wr_con.put("table",wr_table); list_wr_con.add(json_wr_con); System.out.println(json_wr_con); List<String> list_col = new ArrayList<String>(); for(int k=0;k<column_name.size();k++){ list_col.add(column_name.get(k)); } //reader para JSONObject json_re_para = new JSONObject(); json_re_para.put("username","scott"); json_re_para.put("password","tiger"); json_re_para.put("column",list_col); json_re_para.put("connection",list_re_con); System.out.println(json_re_para); //writer para JSONObject json_wr_para = new JSONObject(); json_wr_para.put("writeMode","insert"); json_wr_para.put("username","root"); json_wr_para.put("password","123456"); json_wr_para.put("column",list_col); json_wr_para.put("preSql", new String[] { "truncate table "+table_name.get(j)}); json_wr_para.put("connection",list_wr_con); System.out.println(json_wr_para); JSONObject json_re = new JSONObject(); json_re.put("name","oraclereader"); json_re.put("parameter",json_re_para); JSONObject json_wr = new JSONObject(); json_wr.put("name","mysqlwriter"); json_wr.put("parameter",json_wr_para); List<JSONObject> a = new ArrayList<JSONObject>(); JSONObject json_content = new JSONObject(); json_content.put("reader",json_re); json_content.put("writer",json_wr); a.add(json_content); JSONObject json_job = new JSONObject(); json_job.put("content",a); System.out.println(json_job); JSONObject json = new JSONObject(); json.put("job",json_job); System.out.println(json); } }} |
|
1
2
3
4
5
6
7
|
信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 3, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 10000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 5kk2ywabz5no2a1174n2i|33723e30, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> oracle.jdbc.driver.OracleDriver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 5kk2ywabz5no2a1174n2i|33723e30, idleConnectionTestPeriod -> 30, initialPoolSize -> 3, jdbcUrl -> jdbc:oracle:thin:@192.168.0.15:1521/orcl, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 8, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> true, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]{"jdbcUrl":["jdbc:oracle:thin:@192.168.0.210:1521:exam"],"table":["DEPT"]}{"jdbcUrl":["jdbc:mysql://192.168.56.140:3306/issue?useUnicode=true&characterEncoding=utf8"],"table":["DEPT"]}{"password":"tiger","column":["LOC","DNAME","DEPTNO"],"connection":[{"jdbcUrl":["jdbc:oracle:thin:@192.168.0.210:1521:exam"],"table":["DEPT"]}],"username":"scott"}{"password":"123456","column":["LOC","DNAME","DEPTNO"],"connection":[{"jdbcUrl":["jdbc:mysql://192.168.56.140:3306/issue?useUnicode=true&characterEncoding=utf8"],"table":["DEPT"]}],"writeMode":"insert","username":"root","preSql":["truncate table DEPT"]}{"content":[{"reader":{"parameter":{"password":"tiger","column":["LOC","DNAME","DEPTNO"],"connection":[{"jdbcUrl":["jdbc:oracle:thin:@192.168.0.210:1521:exam"],"table":["DEPT"]}],"username":"scott"},"name":"oraclereader"},"writer":{"parameter":{"password":"123456","column":["LOC","DNAME","DEPTNO"],"connection":[{"jdbcUrl":["jdbc:mysql://192.168.56.140:3306/issue?useUnicode=true&characterEncoding=utf8"],"table":["DEPT"]}],"writeMode":"insert","username":"root","preSql":["truncate table DEPT"]},"name":"mysqlwriter"}}]}{"job":{"content":[{"reader":{"parameter":{"password":"tiger","column":["LOC","DNAME","DEPTNO"],"connection":[{"jdbcUrl":["jdbc:oracle:thin:@192.168.0.210:1521:exam"],"table":["DEPT"]}],"username":"scott"},"name":"oraclereader"},"writer":{"parameter":{"password":"123456","column":["LOC","DNAME","DEPTNO"],"connection":[{"jdbcUrl":["jdbc:mysql://192.168.56.140:3306/issue?useUnicode=true&characterEncoding=utf8"],"table":["DEPT"]}],"writeMode":"insert","username":"root","preSql":["truncate table DEPT"]},"name":"mysqlwriter"}}]}} |
实现有点土。
转自：https://www.cnblogs.com/muzisanshi/p/13266194.html