使用 DataX 离线迁移 MySQL

能力：全量、增量

本教程演示如何使用 DataX 将 MySQL 数据加载到 Databend。请提前在环境中部署好 Databend、MySQL 与 DataX。

1. 在 MySQL 中创建用于数据迁移的 SQL 用户，并准备示例表及数据。

MySQL

mysql> create user 'mysqlu1'@'%' identified by 'databend';
mysql> grant all on *.* to 'mysqlu1'@'%';
mysql> create database db;
mysql> create table db.tb01(id int, d double, t TIMESTAMP,  col1 varchar(10));
mysql> insert into db.tb01 values(1, 3.1,now(), 'test1'), (1, 4.1,now(), 'test2'), (1, 4.1,now(), 'test2');

2. 在 Databend 中创建对应目标表。

备注

DataX 会自动将数据类型映射到 Databend 类型，详情见 https://github.com/alibaba/DataX/blob/master/databendwriter/doc/databendwriter.md#33-type-convert

Databend

databend> create database migrated_db;
databend> create table migrated_db.tb01(id int null, d double null, t TIMESTAMP null,  col1 varchar(10) null);

3. 将以下内容保存为 mysql_demo.json。更多参数请参考 https://github.com/alibaba/DataX/blob/master/databendwriter/doc/databendwriter.md#32-configuration-description

mysql_demo.json

{
  "job": {
    "content": [
      {
        "reader": {
          "name": "mysqlreader",
          "parameter": {
            "username": "mysqlu1",
            "password": "databend",
            "column": [
              "id", "d", "t", "col1"
            ],
            "connection": [
              {
                "jdbcUrl": [
                  "jdbc:mysql://127.0.0.1:3307/db"
                ],
                "driver": "com.mysql.jdbc.Driver",
                "table": [
                  "tb01"
                ]
              }
            ]
          }
        },
        "writer": {
          "name": "databendwriter",
          "parameter": {
            "username": "databend",
            "password": "databend",
            "column": [
              "id", "d", "t", "col1"
            ],
            "preSql": [
            ],
            "postSql": [
            ],
            "connection": [
              {
                "jdbcUrl": "jdbc:databend://localhost:8000/migrated_db",
                "table": [
                  "tb01"
                ]
              }
            ]
          }
        }
      }
    ],
    "setting": {
      "speed": {
        "channel": 1
       }
    }
  }
}

提示

上述配置默认以 INSERT 模式写入 Databend。若需启用 REPLACE 模式，请添加 writeMode 与 onConflictColumn，如：

mysql_demo.json

...
"writer": {
          "name": "databendwriter",
          "parameter": {
            "writeMode": "replace",
            "onConflictColumn":["id"],
            "username": ...

4. 运行 DataX：

cd {YOUR_DATAX_DIR_BIN}
python datax.py ./mysql_demo.json 

完成后即可在 Databend 中验证：

databend> select * from migrated_db.tb01;
+------+------+----------------------------+-------+
| id   | d    | t                          | col1  |
+------+------+----------------------------+-------+
|    1 |  3.1 | 2023-02-01 07:11:08.500000 | test1 |
|    1 |  4.1 | 2023-02-01 07:11:08.501000 | test2 |
|    1 |  4.1 | 2023-02-01 07:11:08.501000 | test2 |
+------+------+----------------------------+-------+