Native Flink MySQL CDC into MatrixOne and recovery acceptance¶
Components and source requirements¶
This round uses Flink 1.20.2, MySQL CDC 3.6.0-1.20, Flink JDBC 3.3.0-1.20, Connector/J 8.4.0 and Temurin Java 17. Verify official distribution checksums and place the three connector/driver jars in an isolated FLINK_HOME/lib. Do not mix planner versions.
Enable MySQL binlog, ROW format and FULL row images. Assign unique CDC server IDs, read/replication permissions and sufficient log retention. MatrixOne is the SQL sink in this route. Requalify deployed versions; MySQL CDC does not establish MatrixOne binlog-source support.
Prepare exact data¶
Use a dedicated test database and the connection fixtures. The source is MySQL and the destination is MatrixOne. Create this table on both; insert the fixture only on the source. Set SRC_JDBC_URL/SRC_USER/SRC_PASSWORD and MO_JDBC_URL/MO_USER/MO_PASSWORD for the respective databases, fixing UTF-8 and UTC. Local plaintext examples do not qualify production TLS.
CREATE TABLE native_demo (
id BIGINT PRIMARY KEY,
amount DECIMAL(38,18), biguint BIGINT UNSIGNED,
payload VARBINARY(256), ts DATETIME(6), elapsed TIME(6), label VARCHAR(256)
);
INSERT INTO native_demo VALUES
(1,12345678901234567890.123456789012345678,18446744073709551615,
X'00FF80','2024-02-29 12:00:00.123456','-00:00:00.000001','中文😀'),
(2,NULL,NULL,NULL,NULL,NULL,NULL),
(3,0,9007199254740993,X'','1970-01-01 00:00:00','37:00:00.999999','');
Configure checkpoints and execute¶
Configure durable, recoverable state.checkpoints.dir and state.savepoints.dir and launch JobManager/TaskManager for your deployment. SQL addresses, databases, users and passwords below are placeholders; inject through private generated files or deployment secrets with mode 0600. This projection omits elapsed. Extended TIME and precision-65 decimals need a separate source-side string contract and are outside this native-type example. Pre-create native_demo at the destination without conflicting data.
These are Flink statements. SQL Client uses SET below; Table API sets the corresponding keys through getConfig().getConfiguration().setString(...) and submits DDL/INSERT through executeSql(...). Replace JOBMANAGER_HOST and the REST port with the actual cluster.
SET 'execution.target'='remote';
SET 'rest.address'='JOBMANAGER_HOST';
SET 'rest.port'='8081';
SET 'parallelism.default'='1';
SET 'execution.checkpointing.interval'='2 s';
SET 'execution.checkpointing.mode'='EXACTLY_ONCE';
SET 'execution.checkpointing.externalized-checkpoint-retention'='RETAIN_ON_CANCELLATION';
CREATE TABLE source_rows (
id BIGINT, amount DECIMAL(38,18), biguint DECIMAL(20,0),
payload BYTES, ts TIMESTAMP(6), label STRING,
PRIMARY KEY(id) NOT ENFORCED
) WITH (
'connector'='mysql-cdc', 'hostname'='MYSQL_HOST', 'port'='3306',
'username'='SOURCE_USER', 'password'='SOURCE_PASSWORD',
'database-name'='source_db', 'table-name'='native_demo',
'server-id'='57300-57301', 'server-time-zone'='UTC',
'scan.startup.mode'='initial', 'scan.incremental.snapshot.enabled'='true'
);
CREATE TABLE target_rows (
id BIGINT, amount DECIMAL(38,18), biguint DECIMAL(20,0),
payload BYTES, ts TIMESTAMP(6), label STRING,
PRIMARY KEY(id) NOT ENFORCED
) WITH (
'connector'='jdbc',
'url'='jdbc:mysql://MO_HOST:6001/target_db?useSSL=false&serverTimezone=UTC',
'table-name'='native_demo', 'driver'='com.mysql.cj.jdbc.Driver',
'username'='TARGET_USER', 'password'='TARGET_PASSWORD',
'sink.buffer-flush.max-rows'='17', 'sink.buffer-flush.interval'='100 ms',
'sink.max-retries'='0'
);
INSERT INTO target_rows SELECT * FROM source_rows;
"$FLINK_HOME/bin/sql-client.sh" -f native-cdc.sql
Delete, DDL and recovery acceptance¶
Record the Job ID and wait for RUNNING and a completed checkpoint. Compare snapshot values by key using fresh connections: Decimal, UInt64, bytes, microseconds and NULL.
Commit source updates, physical DELETE, new rows and primary-key changes. Check removed old keys, inserted new keys and all values at both ends, beyond counts.
After source ADD COLUMN, verify the fixed projection continues. Existing-field success does not propagate the new field; migrate target DDL and job projection separately.
Preserve a completed checkpoint ID/path. Stop this job’s TaskManager, commit source updates/deletes while it is down and restart it. Record REST checkpoints/latest/restored and fresh all-field results.
Roll back a source transaction and verify rolled-back values never reach the sink. Retain binlog positions, failures, checkpoints and final hashes.
An ordinary JDBC sink is at-least-once. checkpoint mode=EXACTLY_ONCE describes Flink state, not external database transactions. Idempotent final state from primary-key upserts does not prove EOS for non-idempotent effects. An XA route requires qualification of MatrixOne, driver and sink prepare/commit/recover and failure windows.
Actual CDC results of this round¶
On 2026-10-13, six phases × two destinations produced 12 passing cases against the same MatrixOne commit and MySQL 8.4 control. Native TableEnvironment.executeSql submitted Job 01c5f3cd5d6ffecc6ae85cd28de42119. Actual JobManager/TaskManager processes ran a MySQL snapshot/binlog source and two JDBC primary-key sinks.
Phase |
Rows at each destination |
Result |
|---|---|---|
initial_snapshot |
64 |
Fresh full-value reads passed |
update_delete_insert |
80 |
Fresh full-value reads passed |
primary_key_change |
80 |
Fresh full-value reads passed |
source_add_column_fixed_projection |
80 |
Fresh full-value reads passed |
taskmanager_restart_offline_deltas |
73 |
Fresh full-value reads passed |
rolled_back_source_transaction |
73 |
Fresh full-value reads passed |
REST evidence confirms restoration from checkpoint 3 (is_savepoint=false). Source deltas committed during TaskManager downtime reached both destinations; each finished with 73 rows. Original Decimal(38,18), UInt64 bounds, bytes, TIMESTAMP(6), Unicode, SQL NULL and JSON null were checked. JSON was compared structurally and original text archived. ADD COLUMN qualified the existing fixed projection; automatic new-field propagation was not tested.
Initial cropped installation omitted Python/Gateway jars, and the SQL Client’s embedded Gateway bind-port configuration blocked submission. These are runtime preflights, excluded from the 12 CDC cases. Official jars were supplied; the final round used Table API. The SQL Client recipe above was not fully qualified in this round. Use the complete official distribution and separate client/cluster configurations: avoid inheriting the cluster’s fixed rest.bind-port in the client, which can conflict with the embedded Gateway or its advertised address.
Retain failed attempts, completed checkpoints, archives and fresh readback hashes. The ordinary JDBC sink remains at-least-once. XA, manual replay of an older checkpoint, recovery after binlog cleanup, cross-system non-idempotent effects, throughput and long soak were not qualified.