1. 简介
本文简单介绍了 datax 操作 OceanBase 的过程，主要内容为：
1、MySQL 离线同步到 OceanBase。
2、OceanBase 数据写出到 csv 文件。
注：1.本次使用 obproxy ，不使用 obproxy 直连2881端口可能无法成功。
注：2.只提供 datax 操作 OceanBase 的细节部分，datax 的细节请参考 github 的介绍。
2. 环境准备
3. OceanBase
已准备好一个3节点的 OceanBase 集群（开源3.1.1版本），并建立了业务租户 obcp_t2 。搭建过程及业务租户操作等可参考官方文档或官方入门教程，地址：https://open.oceanbase.com。
如下图，红色方框部分均有相关的搭建过程介绍。
3.1. MySQL
版本8.0.27，搭建过程省略，具体可参见 MySQL 官方文档，详见：https://dev.mysql.com/doc/。
4. 编译datax
由于官方编译好的包不支持 OceanBase 的操作，所以需要从源码进行编译，过程如下：
# 下载源码
git clone https://github.com/alibaba/datax.git
# 安装编译工具maven，linux下可使用yum进行安装
yum install -y maven
# 进入到datax源码目录，执行编译：
mvn -U clean package assembly:assembly -Dmaven.test.skip=true
## 如没有报错，生成的编译包在target目录下。
## 为避免生成的包体积太大，可修改pom.xml文件删除一些不需要的reader、writer。
参考more pom.xml(截取一部分)
<modules>
<module>common</module>
<module>core</module>
<module>transformer</module>
<!-- reader -->
<module>mysqlreader</module>
<module>txtfilereader</module>
<module>streamreader</module>
<module>oceanbasev10reader</module>
<!-- writer -->
<module>mysqlwriter</module>
<module>txtfilewriter</module>
<module>ftpwriter</module>
<module>streamwriter</module>
<module>oceanbasev10writer</module>
<!-- common support module -->
<module>plugin-rdbms-util</module>
<module>plugin-unstructured-storage-util</module>
</modules>
5. MySQL离线同步到OceanBase
5.1. json 配置文件
说明：
oceanbasev10writer ："jdbcUrl": "||_dsc_ob10_dsc_||obcp_test:obcp_t2||_dsc_ob10_dsc_||jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
其中 obcp_test 为集群名，obcp_t2 为业务租户名，tpcc 为库名，2883为 obproxy 的端口。
more mysql2ob.json
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "oracle_4U",
"column": [
"*"
],
"connection": [
{
"table": [
"customer"
],
"jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false"]
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": [
"*"
],
"preSql": [
"truncate table customer"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obcp_test:obcp_t2||_dsc_ob10_dsc_||jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSession
State=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table": [
"customer"
]
}
],
"username": "root",
"password": "oracle_4U",
"writerThreadCount":10,
"batchSize": 1000,
"memstoreThreshold": "0.9"
}
}
}
]
}
}
5.2. 执行同步
python bin/datax.py mysql2ob.json
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
2021-12-26 09:53:27.305 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2021-12-26 09:53:27.317 [main] INFO Engine - the machine info =>
osInfo: Oracle Corporation 1.8 25.231-b11
jvmInfo: Linux amd64 3.10.0-1062.9.1.el7.x86_64
cpu num: 24
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
2021-12-26 09:53:27.342 [main] INFO Engine -
{
"content":[
{
"reader":{
"name":"mysqlreader",
"parameter":{
"column":[
"*"
],
"connection":[
{
"jdbcUrl":[
"jdbc:mysql://127.0.0.1:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false"
],
"table":[
"customer"
]
}
],
"password":"*********",
"username":"root"
}
},
"writer":{
"name":"oceanbasev10writer",
"parameter":{
"batchSize":1000,
"column":[
"*"
],
"connection":[
{
"jdbcUrl":"||_dsc_ob10_dsc_||obcp_test:obcp_t2||_dsc_ob10_dsc_||jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table":[
"customer"
]
}
],
"memstoreThreshold":"0.9",
"obWriteMode":"insert",
"password":"*********",
"preSql":[
"truncate table customer"
],
"username":"root",
"writerThreadCount":10
}
}
}
],
"setting":{
"errorLimit":{
"percentage":0.1,
"record":0
},
"speed":{
"channel":4
}
}
}
2021-12-26 09:53:27.369 [main] WARN Engine - prioriy set to 0, because NumberFormatException, the value is: null
2021-12-26 09:53:27.372 [main] INFO PerfTrace - PerfTrace traceId=job_-1, isEnable=false, priority=0
2021-12-26 09:53:27.372 [main] INFO JobContainer - DataX jobContainer starts job.
2021-12-26 09:53:27.376 [main] INFO JobContainer - Set jobId = 0
2021-12-26 09:53:27.920 [job-0] INFO OriginalConfPretreatmentUtil - Available jdbcUrl:jdbc:mysql://127.0.0.1:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true.
2021-12-26 09:53:27.921 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置文件中的列配置存在一定的风险. 因为您未配置读取数据库表的列，当您的表字段个数、类型有变动时，可能影响任务正确性甚至会运行出错。请检查您的配置并作出修改.
2021-12-26 09:53:27.957 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2021-12-26 09:53:27.958 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obcp_test:obcp_t2:root :url=jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true
2021-12-26 09:53:28.551 [job-0] INFO DbUtils - value for query [SHOW VARIABLES LIKE 'ob_compatibility_mode'] is [MYSQL]
2021-12-26 09:53:28.563 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2021-12-26 09:53:28.563 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obcp_test:obcp_t2:root :url=jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2021-12-26 09:53:29.143 [job-0] INFO OriginalConfPretreatmentUtil - table:[customer] all columns:[
c_id,c_d_id,c_w_id,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data
].
2021-12-26 09:53:29.144 [job-0] WARN OriginalConfPretreatmentUtil - 您的配置文件中的列配置信息存在风险. 因为您配置的写入数据库表的列为*，当您的表字段个数、类型有变动时，可能影响任务正确性甚至会运行出错。请检查您的配置并作出修改.
2021-12-26 09:53:29.153 [job-0] INFO OriginalConfPretreatmentUtil - Write data [
INSERT INTO %s (c_id,c_d_id,c_w_id,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
], which jdbcUrl like:[||_dsc_ob10_dsc_||obcp_test:obcp_t2||_dsc_ob10_dsc_||jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true]
2021-12-26 09:53:29.154 [job-0] INFO JobContainer - jobContainer starts to do prepare ...
2021-12-26 09:53:29.155 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] do prepare work .
2021-12-26 09:53:29.156 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] do prepare work .
2021-12-26 09:53:29.157 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2021-12-26 09:53:29.157 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obcp_test:obcp_t2:root :url=jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2021-12-26 09:53:29.225 [job-0] INFO CommonRdbmsWriter$Job - Begin to execute preSqls:[truncate table customer]. context info:||_dsc_ob10_dsc_||obcp_test:obcp_t2||_dsc_ob10_dsc_||jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true.
2021-12-26 09:53:29.785 [job-0] INFO DBUtil - this is ob1_0 jdbc url.
2021-12-26 09:53:29.786 [job-0] INFO DBUtil - this is ob1_0 jdbc url. user=obcp_test:obcp_t2:root :url=jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2021-12-26 09:53:29.873 [job-0] INFO DbUtils - value for query [show variables like 'version'] is [3.1.1]
2021-12-26 09:53:29.874 [job-0] INFO JobContainer - jobContainer starts to do split ...
2021-12-26 09:53:29.875 [job-0] INFO JobContainer - Job set Channel-Number to 4 channels.
2021-12-26 09:53:29.880 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] splits to [1] tasks.
2021-12-26 09:53:29.882 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] splits to [1] tasks.
2021-12-26 09:53:29.928 [job-0] INFO JobContainer - jobContainer starts to do schedule ...
2021-12-26 09:53:29.934 [job-0] INFO JobContainer - Scheduler starts [1] taskGroups.
2021-12-26 09:53:29.937 [job-0] INFO JobContainer - Running by standalone Mode.
2021-12-26 09:53:29.946 [taskGroup-0] INFO TaskGroupContainer - taskGroupId=[0] start [1] channels for [1] tasks.
2021-12-26 09:53:30.052 [taskGroup-0] INFO Channel - Channel set byte_speed_limit to -1, No bps activated.
2021-12-26 09:53:30.053 [taskGroup-0] INFO Channel - Channel set record_speed_limit to -1, No tps activated.
2021-12-26 09:53:30.084 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] attemptCount[1] is started
2021-12-26 09:53:30.087 [0-0-0-writer] INFO OceanBaseV10Writer$Task - tableNumber:1,writerTask Class:com.alibaba.datax.plugin.writer.oceanbasev10writer.task.ConcurrentTableWriterTask
2021-12-26 09:53:30.089 [0-0-0-writer] INFO ConcurrentTableWriterTask - configure url is unavailable, use obclient for connections.
2021-12-26 09:53:30.089 [0-0-0-reader] INFO CommonRdbmsReader$Task - Begin to read record by Sql: [select * from customer
] jdbcUrl:[jdbc:mysql://127.0.0.1:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true].
2021-12-26 09:53:30.171 [0-0-0-writer] INFO ConcurrentTableWriterTask - Disable partition calculation feature.
2021-12-26 09:53:30.370 [0-0-0-writer] INFO CommonRdbmsWriter$Task - write mode: insert
2021-12-26 09:53:30.371 [0-0-0-writer] INFO ConcurrentTableWriterTask - writeRecordSql :INSERT INTO customer (c_id,c_d_id,c_w_id,c_first,c_middle,c_last,c_street_1,c_street_2,c_city,c_state,c_zip,c_phone,c_since,c_credit,c_credit_lim,c_discount,c_balance,c_ytd_payment,c_payment_cnt,c_delivery_cnt,c_data) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
2021-12-26 09:53:30.372 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url.
2021-12-26 09:53:30.372 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url. user=obcp_test:obcp_t2:root :url=jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2021-12-26 09:53:30.402 [0-0-0-writer] ERROR ConcurrentTableWriterTask - partCalculator is null
2021-12-26 09:53:30.402 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 1 insert task.
2021-12-26 09:53:30.459 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 2 insert task.
2021-12-26 09:53:30.502 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 3 insert task.
2021-12-26 09:53:30.523 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 4 insert task.
2021-12-26 09:53:30.543 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 5 insert task.
2021-12-26 09:53:30.562 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 6 insert task.
2021-12-26 09:53:30.589 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 7 insert task.
2021-12-26 09:53:30.615 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 8 insert task.
2021-12-26 09:53:30.647 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 9 insert task.
2021-12-26 09:53:30.672 [0-0-0-writer] INFO ConcurrentTableWriterTask - start 10 insert task.
2021-12-26 09:53:30.697 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url.
2021-12-26 09:53:30.697 [0-0-0-writer] INFO DBUtil - this is ob1_0 jdbc url. user=obcp_test:obcp_t2:root :url=jdbc:oceanbase://172.20.0.30:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true
2021-12-26 09:53:30.768 [0-0-0-writer] INFO ColumnMetaCache - fetch columnMeta of table customer success
2021-12-26 09:53:32.110 [0-0-0-writer] INFO CommonRdbmsWriter$Task - isMemstoreFull=false
2021-12-26 09:53:40.116 [job-0] INFO StandAloneJobContainerCommunicator - Total 0 records, 0 bytes | Speed 0B/s, 0 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 0.00%
2021-12-26 09:53:44.968 [0-0-0-reader] INFO CommonRdbmsReader$Task - Finished read record by Sql: [select * from customer
] jdbcUrl:[jdbc:mysql://127.0.0.1:3306/tpcc?useUnicode=true&characterEncoding=utf8&useSSL=false&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewriteBatchedStatements=true].
2021-12-26 09:53:44.991 [0-0-0-writer] INFO ConcurrentTableWriterTask - ConcurrentTableWriter has put all task in queue, queueSize = 0, total = 300, finished = 298
2021-12-26 09:53:45.158 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[0] is successed, used[15076]ms
2021-12-26 09:53:45.158 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] completed it's tasks.
2021-12-26 09:53:50.121 [job-0] INFO StandAloneJobContainerCommunicator - Total 300000 records, 160729712 bytes | Speed 15.33MB/s, 30000 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 10.871s | All Task WaitReaderTime 1.692s | Percentage 100.00%
2021-12-26 09:53:50.121 [job-0] INFO AbstractScheduler - Scheduler accomplished all tasks.
2021-12-26 09:53:50.122 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] do post work.
2021-12-26 09:53:50.122 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] do post work.
2021-12-26 09:53:50.122 [job-0] INFO JobContainer - DataX jobId [0] completed successfully.
2021-12-26 09:53:50.124 [job-0] INFO HookInvoker - No hook invoked, because base dir not exists or is a file: /data/obdata/datax/target/datax/datax/hook
2021-12-26 09:53:50.125 [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 | 20 | 20 | 20 | 0.383s | 0.383s | 0.383s
2021-12-26 09:53:50.125 [job-0] INFO JobContainer - PerfTrace not enable!
2021-12-26 09:53:50.125 [job-0] INFO StandAloneJobContainerCommunicator - Total 300000 records, 160729712 bytes | Speed 7.66MB/s, 15000 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 10.871s | All Task WaitReaderTime 1.692s | Percentage 100.00%
2021-12-26 09:53:50.126 [job-0] INFO JobContainer -
任务启动时刻 : 2021-12-26 09:53:27
任务结束时刻 : 2021-12-26 09:53:50
任务总计耗时 : 22s
任务平均流量 : 7.66MB/s
记录写入速度 : 15000rec/s
读出记录总数 : 300000
读写失败总数 : 0
5.3. 验证
略
6. OceanBase 到 CSV
6.1. json配置文件
{
"job": {
"setting": {
"speed": {
"channel":3
},
"errorLimit": {
"record": 0
}
},
"content": [
{
"reader": {
"name": "oceanbasev10reader",
"parameter": {
"username": "root",
"password": "oracle_4U",
"column": [
"*"
],
"weakRead": "false",
"readBatchSize": 30,
"connection": [
{
"jdbcUrl": ["||_dsc_ob10_dsc_||obcp_test:obcp_t2||_dsc_ob10_dsc_||jdbc:oceanbase://172.20.0.30:2883/tpcc"],
"table": [
"customer"
]
}
]
}
},
"writer": {
"name": "txtfilewriter",
"parameter": {
"path": "/home/admin/",
"fileName": "txtfile",
"writeMode": "truncate",
"format": "yyyy-MM-dd"
}
}
}
]
}
}
6.2. 执行同步
python bin/datax.py ob2txt.json
