MySQL to ClickHouse CDC with Debezium: Architecture and Lessons Learned

Analytics queries and transactional traffic want opposite things from a database. OLTP wants small, indexed, row-oriented lookups. Analytics wants to scan tens of millions of rows and aggregate them. Running both on the same MySQL primary works right up until it very suddenly does not.

The usual first fix is a nightly ETL job. That works too, until someone asks why the dashboard is a day behind. Change-data-capture (CDC) is the middle path: stream row-level changes out of MySQL as they happen and materialise them in a columnar store built for scans.

This post walks through a MySQL → Debezium → Kafka → ClickHouse pipeline: how the pieces fit, the parts that are genuinely tricky, and the decisions I would make earlier next time.

The shape of the pipeline

┌──────────┐   binlog    ┌──────────┐   topics   ┌───────┐   sink    ┌────────────┐
│  MySQL   │ ──────────► │ Debezium │ ─────────► │ Kafka │ ────────► │ ClickHouse │
│ (primary)│   row-based │ connector│  1 per tbl │       │  consumer │  (MergeTree)│
└──────────┘             └──────────┘            └───────┘           └────────────┘

Each stage has exactly one job:

• MySQL writes every committed row change to its binary log. Debezium is just another replication client.
• Debezium decodes the binlog into structured change events (before, after, operation, source metadata) and publishes one Kafka topic per table.
• Kafka is the durable buffer. It is what lets ClickHouse be slow, restart, or be down for an hour without losing data.
• ClickHouse consumes those topics and merges them into tables designed for aggregation.

The buffer is the part people are tempted to skip. Don’t. Without it, any ClickHouse hiccup becomes back-pressure on your binlog reader, and a binlog reader that falls far enough behind runs into binlog expiry — at which point your only recovery is a full re-snapshot.

Preparing MySQL

CDC needs row-based binary logging with full row images. Anything less and you get change events you cannot apply deterministically.

# my.cnf — example values
[mysqld]
server-id                = 1
log_bin                  = /var/log/mysql/mysql-bin.log
binlog_format            = ROW
binlog_row_image         = FULL
binlog_expire_logs_seconds = 604800   # 7 days
gtid_mode                = ON
enforce_gtid_consistency = ON

Two of these deserve comment.

binlog_row_image = FULL writes every column for every change, not just the ones that changed. It costs disk and network, but partial images make it impossible to reconstruct a complete row in the sink without reading back from the source.

binlog_expire_logs_seconds is your recovery window. It is the maximum time the connector can be down before you lose the ability to resume. Seven days is comfortable; one day will eventually ruin a weekend.

The connector needs its own least-privilege user:

CREATE USER 'debezium'@'%' IDENTIFIED BY 'use-a-real-secret-here';
GRANT SELECT, RELOAD, SHOW DATABASES,
      REPLICATION SLAVE, REPLICATION CLIENT
  ON *.* TO 'debezium'@'%';
FLUSH PRIVILEGES;

RELOAD and SELECT are only needed for the initial snapshot; the streaming phase uses the replication grants.

Configuring the connector

A minimal Debezium MySQL connector, registered against Kafka Connect:

{
  "name": "orders-cdc",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "database.hostname": "mysql.internal",
    "database.port": "3306",
    "database.user": "debezium",
    "database.password": "${file:/run/secrets/debezium:password}",
    "database.server.id": "184054",
    "topic.prefix": "shop",
    "database.include.list": "shop",
    "table.include.list": "shop.orders,shop.order_items,shop.customers",
    "schema.history.internal.kafka.bootstrap.servers": "kafka:9092",
    "schema.history.internal.kafka.topic": "schema-history.shop",
    "snapshot.mode": "initial",
    "decimal.handling.mode": "string",
    "time.precision.mode": "connect"
  }
}

decimal.handling.mode is the setting that bites people. The default encodes DECIMAL columns as base64-wrapped binary, which is correct but unhelpful downstream. string keeps full precision and is trivially parseable in ClickHouse. Never use double for money.

database.server.id must be unique across every replication client on that MySQL server. Two connectors sharing an ID will fight, disconnect each other, and produce a confusing intermittent failure.

Modelling the ClickHouse side

This is where CDC stops being a plumbing problem and becomes a data-modelling problem.

ClickHouse has no cheap in-place UPDATE or DELETE. The MergeTree family is append-optimised; mutations rewrite parts. So you do not apply changes — you append them and let the table converge.

ReplacingMergeTree with a version column is the standard answer:

CREATE TABLE shop.orders
(
    id            UInt64,
    customer_id   UInt64,
    status        LowCardinality(String),
    total_amount  Decimal(12, 2),
    created_at    DateTime64(3, 'UTC'),
    updated_at    DateTime64(3, 'UTC'),
    -- CDC bookkeeping
    _version      UInt64,
    _deleted      UInt8 DEFAULT 0
)
ENGINE = ReplacingMergeTree(_version, _deleted)
PARTITION BY toYYYYMM(created_at)
ORDER BY (id);

Three things are doing real work here:

1. _version must increase monotonically per row. The binlog position, or the source ts_ms combined with a sequence, both work. Whatever you choose, out-of-order delivery must still resolve to the latest state.
2. _deleted is the is_deleted column of ReplacingMergeTree. Debezium emits deletes as an event with op: "d"; you translate that into a row with _deleted = 1 rather than an actual DELETE.
3. ORDER BY (id) defines row identity for deduplication. It must match the source primary key.

The safe read pattern, without FINAL’s cost on wide tables:

SELECT
    id,
    argMax(status,       _version) AS status,
    argMax(total_amount, _version) AS total_amount,
    argMax(_deleted,     _version) AS is_deleted
FROM shop.orders
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY id
HAVING is_deleted = 0;

In practice I expose that as a VIEW so nobody has to remember it, and reserve the raw table for backfills and debugging.

Snapshots, and why the first one is the hard one

snapshot.mode: initial tells Debezium to read the existing table contents before it starts streaming. On a small table that is a non-event. On a large one it is the riskiest part of the whole project: it holds read locks (briefly, with the default settings), it takes hours, and if it fails you start over.

What helps:

• Take the snapshot from a replica, not the primary. Debezium does not care which server’s binlog it reads, as long as GTIDs are on.
• Use snapshot.mode: schema_only when you genuinely only need changes from now on, then backfill history with a separate bulk load. Two simple jobs beat one complicated one.
• Set incremental.snapshot.chunk.size and use incremental snapshots when you need to add a table later. Re-snapshotting the whole connector to pick up one new table is a bad trade.

Schema changes

A column added in MySQL flows through as a schema change event, and Kafka Connect’s schema registry handles the evolution. ClickHouse does not find out automatically.

The workable discipline is boring and it works:

1. Add the column in ClickHouse first, nullable or with a default.
2. Deploy the MySQL migration.
3. Update the sink mapping to populate it.

Renames and type narrowing are not additive and need a new table plus a backfill. Treat them as migrations, not as schema tweaks. A DROP COLUMN on the source with no corresponding sink change is harmless; the reverse is not.

What to monitor

Four signals cover most of the failure space:

That last one is the one people skip and later wish they had not. A daily job comparing counts (and a checksum over a recent window) per table catches dropped events, misconfigured filters and deduplication mistakes that no infrastructure metric will show you.

-- Example reconciliation, run against both sides for the same window
SELECT toDate(created_at) AS d, count() AS c
FROM shop.orders FINAL
WHERE _deleted = 0
  AND created_at >= today() - 7
GROUP BY d ORDER BY d;

Lessons I would apply from the start

Buffer before you sink. Kafka is not optional overhead. It converts “ClickHouse is down” from an incident into a backlog.

Version every row explicitly. Relying on arrival order works locally and fails under retries, rebalances and parallel consumers.

Decide staleness per consumer, not globally. “Near real time” is not one number. An operational dashboard might need 30 seconds; a finance report is often happier with a well-defined hourly boundary it can reconcile against. Writing those expectations down prevents a lot of arguments.

Make the first snapshot resumable. Whatever you choose — incremental snapshots, snapshot-from-replica, or a separate bulk load — plan for it to fail once, because it will.

Keep the raw change stream. Retaining CDC topics for a week or two costs very little and has turned a “we need to re-snapshot everything” afternoon into a targeted replay more than once.

CDC is not hard in the sense of being complicated. It is hard in the sense that most of the complexity lives in states you only see under failure: a connector that resumed at the wrong offset, a merge that has not run yet, a schema change that arrived out of order. Design for those and the happy path takes care of itself.