MySQL to ClickHouse with Continuous CDC

Last updated: September 2026

The objective was not just an initial transfer. We needed a repeatable pattern for loading history, continuing to deliver inserts, updates, and deletes, and presenting an up-to-date reporting view in ClickHouse.

The architecture

A Python CDC agent runs next to MySQL. It reads an initial snapshot in primary-key batches, then consumes row-based binary logs for inserts, updates, and deletes. The agent pushes to ClickHouse over HTTPS on port 443, so MySQL needs no public tunnel or inbound firewall rule.

This is a useful pattern for a company-managed ClickHouse server. For ClickHouse Cloud, ClickPipes is the managed choice; a local agent is useful when the source is local and the target is an owned server.

1. Prepare MySQL for CDC

MySQL CDC relies on row-based binary logs. We verified the configuration before starting:

SHOW VARIABLES WHERE Variable_name IN
('log_bin', 'binlog_format', 'binlog_row_image',
 'gtid_mode', 'enforce_gtid_consistency',
 'binlog_expire_logs_seconds');

The CDC user needs SELECT, REPLICATION SLAVE, and REPLICATION CLIENT privileges. In production it should be a dedicated least-privilege user.

2. Model CDC data in ClickHouse

Rather than writing directly to a reporting table, the pipeline stores versioned records in a raw CDC table. ReplacingMergeTree retains a version number and deletion marker, while a current-state view is used by dashboards.

CREATE TABLE <database>.mysql_chicago_events_cdc
(
    event_id UInt64,
    event_time DateTime64(3),
    user_id UInt64,
    event_name LowCardinality(String),
    product String,
    amount Decimal(13, 2),
    region LowCardinality(String),
    _cdc_version UInt64,
    _is_deleted UInt8
)
ENGINE = ReplacingMergeTree(_cdc_version)
PARTITION BY toYYYYMM(toDate(event_time))
ORDER BY event_id;

3. Run the snapshot and CDC agent

The agent uses clickhouse-connect, pymysql, and mysql-replication. Connection settings are supplied through environment variables only. Keyset pagination with a 50,000-row batch size reads in event-ID order instead of repeatedly scanning earlier rows.

After the snapshot, the agent saves the last copied event ID and MySQL binary-log position. On restart, it resumes CDC rather than reloading history.

Results

Production considerations

This was a successful POC, not a final production design. Before using this pattern for critical workloads, add a managed service or host for automatic restarts, vault-backed secret storage, replication-lag and agent-health monitoring, a consistent snapshot strategy, retries, schema-change handling, alerts, and an operational runbook.