如何使用 DataX 工具迁移 MySQL 数据到 OceanBase

MySQL [test11]> CREATE TABLE departments (
    ->   dept_id INT PRIMARY KEY,
    ->   dept_name VARCHAR(50) NOT NULL,
    ->   dept_location VARCHAR(50),
    ->   dept_manager VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.01 sec)

MySQL [test11]> INSERT INTO departments (dept_id, dept_name, dept_location, dept_manager) VALUES (1, '人事部', '北京市', '王经理');
Query OK, 1 row affected (0.01 sec)

MySQL [test11]> INSERT INTO departments (dept_id, dept_name, dept_location, dept_manager) VALUES (2, '财务部', '上海市', '张经理');
Query OK, 1 row affected (0.00 sec)

-- ...省略插入数据

MySQL [test11]> INSERT INTO departments (dept_id, dept_name, dept_location, dept_manager) VALUES (9, '采购部', '杭州市', '钱经理');
Query OK, 1 row affected (0.00 sec)

MySQL [test11]> INSERT INTO departments (dept_id, dept_name, dept_location, dept_manager) VALUES (10, '翻译部', '重庆市', '孙经理');
Query OK, 1 row affected (0.01 sec)

MySQL [test11]> select count(1) from departments;
+----------+
| count(1) |
+----------+
|       10 |
+----------+
1 row in set (0.01 sec)

obclient> CREATE TABLE departments (
    ->   dept_id INT PRIMARY KEY,
    ->   dept_name VARCHAR(50) NOT NULL,
    ->   dept_location VARCHAR(50),
    ->   dept_manager VARCHAR(50)
    -> );
Query OK, 0 rows affected (1.11 sec)

obclient> select count(1) from departments;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.23 sec)

wget https://datax-opensource.oss-cn-hangzhou.aliyuncs.com/202303/datax.tar.gz
tar zxvf datax.tar.gz
cd datax

$ tree -L 1 --filelimit 30
.
├── bin
├── conf
├── job
├── lib
├── log
├── log_perf
├── plugin
├── script
└── tmp

{
    "job": {
        "setting": {
            "speed": {
                "channel": 4
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.1
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "root",
                        "password": "xxxxxx",
                        "column": ["*"],
                        "connection": [
                            {
                                "table": ["departments"],
                                "jdbcUrl": ["jdbc:mysql://xx.xx.xx.xx:3406/test11?useSSL=false&useUnicode=true&characterEncoding=utf8"]
                            }
                        ]
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "obWriteMode": "insert",
                        "column": ["*"],
                        "preSql": ["truncate table departments"],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:oceanbase://xx.xx.xx.xx:2883/test11?useSSL=false&useUnicode=true&characterEncoding=utf8",
                                "table": ["departments"]
                            }
                        ],
                        "username": "root@mysql#obcluster",
                        "password": "xxxxxx",
                        "writerThreadCount": 10,
                        "batchSize": 1000,
                        "memstoreThreshold": "0.9"
                    }
                }
            }
        ]
    }
}

cd datax/bin
python datax.py ../job/mysqljob.json

obclient> select * from DEPARTMENTS;
+---------+-----------+---------------+--------------+
| dept_id | dept_name | dept_location | dept_manager |
+---------+-----------+---------------+--------------+
|       1 | 人事部    | 北京市        | 王经理       |
|       2 | 财务部    | 上海市        | 张经理       |
|       3 | 市场部    | 广州市        | 李经理       |
|       4 | 研发部    | 深圳市        | 赵经理       |
|       5 | 销售部    | 成都市        | 刘经理       |
|       6 | 运营部    | 武汉市        | 周经理       |
|       7 | 客服部    | 南京市        | 黄经理       |
|       8 | 技术部    | 天津市        | 陈经理       |
|       9 | 采购部    | 杭州市        | 钱经理       |
|      10 | 翻译部    | 重庆市        | 孙经理       |
+---------+-----------+---------------+--------------+
10 rows in set (0.05 sec)