实战教程第四章4.7：如何使用 DATAX 迁移 MySQL数据到 OceanBase

将 MySQL 数据迁移到 OceanBase ，如果源端和目标端不能同时跟 DATAX 服务器网络联通，则使用上面方法通过 CSV 文件中转。如果源端数据库和目标端数据库能同时跟 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:oceanbase://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:oceanbase://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:oceanbase://obproxyIp:port/dbname?yearIsDateType=false&ZeroDateTimeBehavior=convertToNull&tinyIntlisBit=false&rewriteBatchedStatements=true",
                                "table": [
                                    "tabName"
                                ]
                            }
                        ],
                        "obWriteMode": "insert",
                        "password": "",
                        "preSql": [
                            ""
                        ],
                        "username": ""
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": ""
            }
        }
    }
}

附录：

• 4.1 OceanBase 的 MySQL 兼容性简介
• 4.2 如何使用 mysqldump 迁移 MySQL 表 OceanBase
• 4.3 如何使用 dbcat 迁移 MySQL 表结构到 OceanBase
• 4.4 如何把 MySQL 表数据导出到 CSV 文件
• 4.5 如何使用 OceanBase 的 LOAD 命令加载 csv 数据文件 OceanBase
• 4.6 如何使用 DataX 加载 CSV 数据文件到 OceanBase
• 4.7 如何使用 DATAX 迁移 MySQL数据到 OceanBase
• 4.8 如何使用 OBDUMPER / OBLOADER 工具导出/导入 OceanBase 数据
• 4.9 如何使用 DATAX 迁移 OceanBase 数据到 MySQL/ORACLE
• 4.10 如何使用 CANAL 将 MySQL 数据实时同步到 OceanBase

结束语

加入教程直播群方式一：钉钉群号3255 4020

加入教程直播群方式二：扫码下方二维码加入