MySQL to ClickHouse with Continuous CDC
Quick answer: a local MySQL events table with 5,000,001 rows was loaded into a managed ClickHouse server in about 3 minutes 23 seconds, then kept current with binary-log-based CDC. A Python agent reads the snapshot in keyset-paginated batches and streams changes outward over HTTPS—without exposing MySQL publicly.
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');| Setting | Required value | Why it matters |
|---|---|---|
log_bin | ON | Makes changes available to the CDC reader. |
binlog_format | ROW | Captures inserts, updates, and deletes as row events. |
binlog_row_image | FULL | Provides reliable before/after row images. |
gtid_mode | Recommended | Improves production recovery options. |
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
| Metric | Result |
|---|---|
| Initial MySQL rows | 5,000,001 |
| Batch size | 50,000 rows |
| Time to CDC-ready | About 3 minutes 23 seconds |
| Approximate throughput | About 24,600 rows per second |
| CDC validation | One new MySQL event arrived successfully |
| Final current-row count | 5,000,002 |
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.