Synchronize all tables in the specified MySQL database to Doris in real time.

[HWTsang]

I want to synchronize all tables in a database (my_database) to Doris in real time. How can this be done?

[DanielLeens]

You can use MySQL-CDC with table-pattern to capture all tables in one MySQL database, and then write them to Doris with dynamic table mapping.

For a single exact database such as my_database, prefer database-names instead of database-pattern:

env {
  job.mode = "STREAMING"
  checkpoint.interval = 5000
}

source {
  MySQL-CDC {
    url = "jdbc:mysql://mysql:3306"
    username = "your_mysql_user"
    password = "your_mysql_password"

    database-names = ["my_database"]
    table-pattern = "my_database\\..*"

    server-id = "5400-5408"
    startup.mode = "initial"
  }
}

sink {
  Doris {
    fenodes = "doris-fe:8030"
    username = "root"
    password = "your_doris_password"

    # Option 1: keep the upstream database and table names.
    database = "${database_name}"
    table = "${table_name}"

    # Option 2: write all MySQL tables into one fixed Doris database.
    # database = "my_doris_database"
    # table = "${table_name}"

    sink.label-prefix = "mysql-doris-cdc"
    sink.enable-2pc = "true"
    sink.enable-delete = "true"
    schema_save_mode = "CREATE_SCHEMA_WHEN_NOT_EXIST"

    doris.config {
      format = "json"
      read_json_by_line = "true"
    }
  }
}

Notes:

1. table-pattern must include the database name. For all tables in my_database, use my_database\\..*.
2. table-pattern and table-names are mutually exclusive, so do not configure both.
3. Use database-pattern only when you really want to match databases by regular expression, for example:

database-pattern = "source.*"
table-pattern = "source.*\\..*"

4. server-id should be explicit and unique among MySQL replication clients.
5. If you want to propagate deletes to Doris, sink.enable-delete = "true" requires Doris delete support and the Doris table model must match that requirement. The Doris connector documentation notes this is for the Unique model.
6. With sink.enable-2pc = "true", Doris commit is driven by checkpoints, so choose checkpoint.interval carefully and keep sink.label-prefix stable and unique enough for the job.

References:

• MySQL-CDC source options: https://seatunnel.apache.org/docs/connectors/source/MySQL-CDC/
• Doris sink options: https://seatunnel.apache.org/docs/connectors/sink/Doris/