datax从mysql迁移数据到OceanBase

datax部署

下载datax

datax下载地址

安装datax

tar -zxvf datax.tar.gz

使用datax

使用配置文件

{
    "job": {
        "setting": {
            "speed": {
                "channel": 4 
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.1
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "inno_clu",
                        "password": "***",
                        "column": ["*"],
                        "connection": [
                            {
                                "table": ["titles"],
                                "jdbcUrl": ["jdbc:mysql://192.168.56.20:3306/employees?useUnicode=true&characterEncoding=utf8"]
                            }
                        ]
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "obWriteMode": "insert",
                        "column": ["*"],
                        "preSql": ["truncate table titles"],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:oceanbase://192.168.56.20:2883/employees?",
                                "table": ["titles"]
                            }
                        ],
                        "username": "datax@mq_t1",
                        "password":"***",
                        "writerThreadCount":10,
                        "batchSize": 1000,
                        "memstoreThreshold": "0.9"
                    }
                }
            }
        ]
    }
}

• 第一个username/password是mysql的用户名和密码,第二个username/password是ob的用户名和密码
• 第一个table是mysql源端的表名,第二个table是ob目标端的表名.
• 第一个jdbcUrl是mysql源端的连接信息,第二个jdbcUrl是ob目标端的连接信息.
• preSql表示迁移数据前要执行的sql

执行迁移命令

[root@innodb-cluster01 job]# python ../bin/datax.py myjob.json
         [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.027s             | 0.027s             | 0.027s             
                 PS Scavenge          | 7                  | 7                  | 7                  | 0.096s             | 0.096s             | 0.096s             

2024-03-09 22:01:08.150 [job-0] INFO  JobContainer - PerfTrace not enable!
2024-03-09 22:01:08.151 [job-0] INFO  StandAloneJobContainerCommunicator - Total 443308 records, 14171912 bytes | Speed 1.35MB/s, 44330 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 6.103s |  All Task WaitReaderTime 0.721s | Percentage 100.00%
2024-03-09 22:01:08.151 [job-0] INFO  JobContainer - 
任务启动时刻                    : 2024-03-09 22:00:56
任务结束时刻                    : 2024-03-09 22:01:08
任务总计耗时                    :                 11s
任务平均流量                    :            1.35MB/s
记录写入速度                    :          44330rec/s
读出记录总数                    :              443308
读写失败总数                    :                   0

!注意

datax不会在目标端创建表,因此需要提前把表结构创建好.