将 MySQL 数据迁移到 OceanBase 数据库，如果源端和目标端不能同时跟 DataX 服务器网络联通，则需要通过 CSV 文件中转，具体操作请参考 如何使用 DataX 加载 CSV 数据文件到 OceanBase。如果源端数据库和目标端数据库能同时跟 DataX 所在服务器联通，则可以使用 DataX 直接将数据从源端迁移到目标端。
MySQL 数据同步到 OceanBase
配置文件如下：
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "tpcc",
"password": "********",
"column": [
"*"
],
"connection": [
{
"table": [
"bmsql_oorder"
],
"jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/tpccdb?useUnicode=true&characterEncoding=utf8"]
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": [
"*"
],
"preSql": [
"truncate table bmsql_oorder"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obdemo:oboracle||_dsc_ob10_dsc_||jdbc:mysql://127.0.0.1:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table": [
"bmsql_oorder"
]
}
],
"username": "tpcc",
"password":"********",
"writerThreadCount":10,
"batchSize": 1000,
"memstoreThreshold": "0.9"
}
}
}
]
}
}
常见报错问题解决
MySQL 端 ssl 相关的报错
-
现象
Mon Dec 13 15:44:13 CST 2021 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification
-
解决方案 您可在 jdbcurl 里关闭 ssl，示例：
"jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/tpccdb?useUnicode=true&characterEncoding=utf8"]
# 调整为:
"jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/tpccdb?useUnicode=true&characterEncoding=utf8&useSSL=false"]
存在外键导入报错
当有外键的表进 truncate 时报错，可以使用 delete from table 代替，也可以在 prepare sql 语句里修改 set foreign_key_checks='off' 以避免报错。
导入数据时事物超时
出现该报错时您可在 prepare sql 中增加 timeout 的设置，命令为：set ob_trx_timeout=1000000000 。
oceanbasev10reader 和 oceanbasev10writer 插件找不到
出现该报错说明 OceanBase 读写插件在默认的二进制 DataX 包里不存在，需要编译安装后创建对应的目录并复制相关的 json 文件，文件的路径和内容如下：
target/datax/datax/plugin/reader/oceanbasev10reader/plugin_job_template.json
target/datax/datax/plugin/writer/oceanbasev10writer/plugin_job_template.json
{
"job": {
"content": [
{
"reader": {
"name": "oceanbasev10reader",
"parameter": {
"column": [
"*"
],
"connection": [
{
"jdbcUrl": [
"||_dsc_ob10_dsc_||clusterName:tenantName||_dsc_ob10_dsc_||jdbc:mysql://obproxyIp:port/dbname"
],
"table": [
"tabName"
]
}
],
"password": "",
"readBatchSize": 100000,
"username": "",
"weakRead": false
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"column": [
"*"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||clusterName:tenantName||_dsc_ob10_dsc_||jdbc:mysql://obproxyIp:port/dbname?yearIsDateType=false&ZeroDateTimeBehavior=convertToNull&tinyIntlisBit=false&rewriteBatchedStatements=true",
"table": [
"tabName"
]
}
],
"obWriteMode": "insert",
"password": "",
"preSql": [
""
],
"username": ""
}
}
}
],
"setting": {
"speed": {
"channel": ""
}
}
}
}
插件 mysqlreader,txtfilewriter 加载失败
解决方案：
删除 plugin[reader,writer] 目录下所有 ._ 开头的文件夹/文件。
rm -rf ._*
