I tried directly specifying TiDB Cloud as a MySQL data source in TiDB Cloud Lake and syncing it

Hello, I'm sora from the Game Solutions Department.
This time, I tried to see if I could sync data by directly specifying a TiDB Cloud cluster as the MySQL data source in TiDB Cloud Lake.

Conclusion First

• When specifying TiDB Cloud directly as the MySQL data source, the Snapshot (full copy) could be ingested
• However, three things were required: setting SSL Mode to require, setting binlog_format to ROW, and adding a Primary Key
• Real-time sync via CDC was not possible because TiDB does not emit MySQL binlogs
• Instead, Archive Schedule allowed daily incremental sync of "yesterday's data", and since MERGE prevents duplicates, it can serve as a substitute for continuous sync
• Ingestion is slow, and it's worth noting that staging tables accumulate in the background

Preparing the TiDB Cloud Side

Prepare the TiDB Cloud cluster that will serve as the sync source.
This time, I created a table called appdb.app_logs in TiDB Cloud Starter and inserted dummy data simulating application logs.

Here is the table definition.
I gave the attributes column a JSON type so I could also see how it would be converted on the Lake side.

CREATE TABLE appdb.app_logs (
    log_id     BIGINT PRIMARY KEY,
    level      VARCHAR(16) NOT NULL,
    service    VARCHAR(64) NOT NULL,
    message    TEXT,
    attributes JSON,
    logged_at  DATETIME NOT NULL,
    KEY idx_logged_at (logged_at)
);

For creating the dummy data, I used the same script from the article below.

https://dev.classmethod.jp/articles/tidb-cloud-lake-migrate-from-tidb/

I initially tried with 500,000 rows, but as mentioned later, ingestion was slow and took a long time, so I reduced it to 20,000 rows partway through.

I also changed binlog_format to ROW on the TiDB side.

SET GLOBAL binlog_format = 'ROW';

This is required when running a Snapshot.
The Lake's MySQL integration checks whether binlog_format is ROW at connection time, and since TiDB returns STATEMENT by default, execution will stop with a binlog must ROW format error if it is not set to ROW.

Registering the Data Source

Go to Data > Data Sources > Create Data Source and select MySQL – Credentials as the Service.

Here are the values to enter.

Select require for SSL Mode.
It's not mentioned in the official documentation, but the actual form has an SSL Mode dropdown where you can choose from four options: disable / require / verify-ca / verify-full.

https://docs.pingcap.com/tidbcloudlake/mysql-credentials/

The default disable won't connect because TiDB requires TLS.
When you click Test Connectivity, TiDB's error is returned as-is.

failed to connect to MySQL at gateway01...:4000: Error 1105 (HY000): Connections using insecure transport are prohibited.

verify-ca and verify-full pass the connectivity test, but fail with a TLS configuration error during subsequent task execution (described later).
Therefore, it was safest to use require, which works through execution.
Once Test Connectivity passes with require, save the settings.

Note: When checking the connection source on the TiDB side, the source IP was from Oregon (us-west-2), not the Tokyo region where Lake was created.
The IP also changed with each connection.
If registering the source IP in an access list with Dedicated, this point requires attention.

Creating and Running the Snapshot Task

Create a task from Data > Data Integration > Create Task.
Choose from three options for Sync Mode.

Select Snapshot here.
When you select Snapshot, fields for Snapshot WHERE Condition and Archive Schedule appear, but first proceed without a WHERE clause and with Archive Schedule set to Off.

Enter log_id for Primary Key.
It may seem optional for Snapshot, but leaving it empty causes the following error at runtime.

start mysql pipeline failed: failed to connect sink: ConflictKey is required for Databend sink

As indicated by Databend sink, the internals of Lake are Databend.
Ingestion is written as a MERGE using the primary key, so Primary Key was required.

Clicking Next shows a preview, and you specify the Warehouse, database, and table name for the target.
Selecting New Table in Upload Data To displays the column mapping.

Click Create and then Start to begin ingestion.
At this point, there were three settings to keep in mind.

Note: If you save with verify-full or verify-ca instead of require, the connectivity test passes but the following error occurs only at runtime.
Since the connectivity test passes, you cannot assume it's safe just because it was saved successfully.

start mysql pipeline failed: failed to connect source: failed to create canal: writeAuthHandshake: tls: either ServerName or InsecureSkipVerify must be specified in the tls.Config

Checking Ingestion Speed

The Snapshot ran successfully, but was surprisingly slow — it took about 3.5 minutes to ingest 20,000 rows.

Looking at what was being sent to TiDB, Lake was reading 1,000 rows at a time using primary key-based pagination.

SELECT * FROM `appdb`.`app_logs` WHERE `log_id` > 1000 ORDER BY `log_id` LIMIT 1000

The SELECT itself completed in a few milliseconds to tens of milliseconds on the TiDB side.
The bottleneck was the writes to Lake.
Looking at the SQL History on the Lake side revealed what was happening with the writes.

Every 100 rows read are INSERTed into a staging table, and every 200 rows are MERGEd into the main table.
Even in Snapshot mode, the underlying path goes through a CDC-style staging table and MERGE.

Looking at the Query Profile of a single MERGE statement, nearly half the time was spent on committing to storage (CommitSink).

Databend writes Parquet files and metadata to object storage with each write operation.
That fixed cost accumulates thousands of times in small units of 100 or 200 rows, which is why it becomes slow.

As for type conversion, it was the same as when ingesting via S3: json became VARIANT and DATETIME became TIMESTAMP.

Note: After ingestion completed and I looked at the tables, the staging table app_logs_cdc_raw had the same number of rows remaining as the main table.
Since the raw_data column holds the entire original row as JSON, this data was about 1.4 times the size of the main table (1.08MB compared to the main table's 795.75KB).

Notes on Re-running

When redoing the same Snapshot, it was necessary to not just delete the table on the Lake side, but to recreate the task entirely.

During verification, I reduced the number of rows, deleted the table on the Lake side, and ran the same task again with Start, but for some reason the first 1,000 rows were missing and only 19,000 rows were inserted.

Looking at the TiDB side history, the second and subsequent executions started reading from WHERE log_id > '1000'.
The cause was that the initial batch position was retained as a checkpoint in the task, and it was not reset even after deleting the table on the Lake side.

Since the same source table can only be linked to one task, I deleted the task and created a new one, and this time all 20,000 rows were correctly inserted from the beginning.

Run History shows Success, so it's worth noting that you won't notice missing rows unless you cross-check the row counts.

Verifying CDC Mode Behavior

Everything so far has been about Snapshot.
I also tried CDC for continuous sync, but it did not work when TiDB Cloud was the sync source.

CDC is a mechanism that continuously streams changes that occur in the sync source to Lake, and for MySQL it reads those changes from binlog.
TiDB has a MySQL-compatible interface, but the official documentation states that it does not support MySQL's replication protocol.

https://docs.pingcap.com/tidb/stable/mysql-compatibility/

Therefore, CDC — which reads binlog via the replication protocol — does not work against TiDB.
This explains the results for each mode.

CDC Only does not fail.
It stays in Running, and on the TiDB side, it maintains a connection as a replica and keeps waiting for binlog events that never come.
Not even the target table is created, and only the Warehouse keeps running.
Since no failure is shown, this behavior is actually harder to notice.

Snapshot + CDC, on the other hand, failed immediately.

start mysql pipeline failed: snapshot+CDC mode requires a valid binlog start position; SHOW MASTER STATUS may have failed

This mode is designed to hand off from Snapshot to CDC starting from the binlog position at that point.
Therefore, before starting the Snapshot, it tries to secure a valid binlog start position.
TiDB's SHOW MASTER STATUS returns plausible-looking values, but they cannot be used to resume as MySQL binlog positions, so it stopped before proceeding to the Snapshot.

To summarize:

TiDB handles change history via TiCDC, not binlog.
For continuously streaming to Lake, the path would be to write to S3 via TiCDC and read from there.
This path requires TiCDC, which is available on TiDB Cloud Dedicated or Essential and above.

Scheduled Execution with Archive Schedule

Since CDC is not available, the final check was whether running Snapshot periodically would work.
Edit the Snapshot task and turn Archive Schedule On to reveal four fields.

After saving, the task status remains Stopped.
There is no need to press Start; leaving it alone, it automatically changes to Running and executes at the next Cron timing.

The SQL sent to TiDB at execution time is as follows:

SELECT * FROM `appdb`.`app_logs`
WHERE logged_at >= '2026-09-17 00:00:00' AND logged_at < '2026-09-18 00:00:00'
ORDER BY `log_id` LIMIT 1000

From Mode: Daily and Time Column: logged_at, it automatically applies a WHERE clause for "yesterday's full day" range.
This means only the incremental data is read, not everything.

To verify this, I added 500 rows with yesterday's date on the TiDB side.
The next automatic execution read only those 500 rows, and the main table app_logs grew from 20,000 to 20,500 rows.

After that, I waited through several executions at 5-minute intervals without adding anything, and the main app_logs stayed at 20,500 rows.
Since writes to the main table use MERGE (upsert) by primary key, reading the same range multiple times simply overwrites without creating duplicates.
This means you can safely continue ingesting the incremental data sliced by Time Column on a daily basis.

While it's not real-time sync, this was sufficient for the use case of "periodically offloading logs accumulated in TiDB to Lake."

The main app_logs has 20,500 rows, while the staging table app_logs_cdc_raw has 21,500 rows.
The staging table is an append-only table that temporarily holds rows read from TiDB during ingestion, and the main table is written from here via MERGE with deduplication.

Notes

• The staging table may repeatedly read the same portion.
  • The 21,500 rows in staging are the initial 20,000 rows plus the 500 rows read by the schedule three times (20,000 + 500 × 3).
  • In this case, Mode was left as Daily while running every 5 minutes, so all three runs read the same "yesterday (9/17)" portion and appended the same 500 rows three times.
  • Since the interval can only be chosen from Daily / Weekly / Monthly, running at a shorter interval will result in repeatedly reading the same portion like this.
  • When the date changes, the portion being read also shifts, so in production running once a day like 0 1 * * *, a different day is read each time and the same rows are never re-read.
• The staging table is not automatically deleted.
  • It is append-only, and in this verification it was not automatically cleaned up.
  • Even with once-a-day operation, daily entries accumulate, so it would be good to plan for cleanup separately, such as manually deleting when no longer needed.
• The Warehouse starts up with each execution.
  • Running at short intervals like every 5 minutes incurs charges each time, so for daily archiving, once a day with something like 0 1 * * * is sufficient.

Closing

This time, I tried directly specifying TiDB Cloud as the MySQL data source in TiDB Cloud Lake to see if data could be synced via Snapshot and its scheduled execution.
CDC is not possible because TiDB does not emit MySQL binlogs, but using Archive Schedule allows periodic incremental sync without going through S3.
I hope this article is useful to someone.