Migrating from MySQL to ClickHouse

Like the PostgreSQL version of this guide, this is an offload story, not a replacement story. MySQL stays as the transactional system of record; ClickHouse takes the analytical queries -- the GROUP BY scans over hundreds of millions of rows that InnoDB's row-oriented storage was never going to be fast at. ClickHouse has no transactional guarantees to speak of, mutations are asynchronous, and single-row lookups by primary key are exactly what it's worst at. Keep MySQL for the app.

The typical trigger: reporting queries that take minutes on MySQL despite proper indexes, read replicas dedicated entirely to BI traffic, or nightly aggregation jobs that no longer fit in the night. If that's not you yet, SUMMARY tables and covering indexes on MySQL are cheaper than a second database. For the engine-level comparison, ClickHouse's columnar storage and vectorized execution are covered in our MergeTree engines guide.

First, the trap: MaterializedMySQL is gone

If you find tutorials recommending the MaterializedMySQL database engine for real-time MySQL replication -- and you will, it was widely blogged about -- know that ClickHouse removed it. The feature was experimental for years, its documentation was pulled in late 2024, and the remaining code was stripped out of ClickHouse in mid-2025. Any guide built on it describes software that no longer exists in current releases. The replacement paths are below.

The Three Realistic Paths

1. One-time copy with the mysql() table function

Built in, zero infrastructure. Create the MergeTree target, then pull:

CREATE TABLE orders
(
    id UInt64,
    customer_id UInt64,
    status LowCardinality(String),
    total Decimal(12, 2),
    created_at DateTime('UTC')
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (status, created_at);
 
INSERT INTO orders
SELECT id, customer_id, status, total, created_at
FROM mysql('mysql-host:3306', 'appdb', 'orders', 'readonly_user', 'secret');

Right for backfills and snapshot-style reporting where "as of last night" is acceptable. For repeated pulls, the MySQL table engine gives you a persistent proxy table; just remember every SELECT against it hits MySQL live.

2. Continuous replication with CDC

For a pipeline that keeps ClickHouse current while MySQL takes writes, you read the binlog:

• ClickPipes MySQL CDC (ClickHouse Cloud): in public beta as of mid-2026 -- unlike the Postgres connector, which is GA, so weigh that for production. Parallel initial snapshot, then continuous binlog streaming; also works against MariaDB. Requirements are standard CDC hygiene: binlog_format = ROW and a sufficient binlog_expire_logs_seconds so a paused pipe doesn't lose its place.
• Altinity Sink Connector (open source, v2.9.1 as of April 2026, actively maintained): Debezium-based, self-hosted, replicates MySQL (and Postgres/MongoDB) into ClickHouse. The mature choice if you're not on ClickHouse Cloud.

Both approaches land data in ReplacingMergeTree tables where deletes become flagged versions rather than removals -- which affects how you count rows (see Validation).

3. Dump and load

mysqldump --tab (TSV per table) or SELECT ... INTO OUTFILE, then clickhouse-client --query "INSERT INTO t FORMAT TabSeparated" < t.tsv. Crude, scriptable, and fine for a one-way trip when the mysql() function can't reach the source network.

Data Type Mapping

The one MySQL-specific wrinkle other sources gloss over: unsigned integers, which MySQL has and most targets don't. ClickHouse is actually the friendly case here -- it has native unsigned types.

Same blanket rules as any ClickHouse target: don't wrap everything in Nullable (bitmap cost, blocked optimizations), and right-size integers -- see choosing data types.

The Schema Redesign Is the Real Work

• ORDER BY is a sort key, not a PRIMARY KEY. No uniqueness enforcement -- the same id inserted twice is two rows. Design the key for query filters (low-cardinality columns first, then time), not identity.
• AUTO_INCREMENT doesn't exist. Keep generating ids in MySQL and replicate them; ClickHouse is the destination, not the allocator.
• Foreign keys and JOIN-heavy schemas. Denormalize into wide fact tables, or use dictionaries as the lookup-join replacement.
• Frequent updates. InnoDB update patterns don't translate; ALTER TABLE ... UPDATE rewrites whole parts in the background. CDC into ReplacingMergeTree handles mutable rows; see updating and deleting data.
• Insert batching. One part per insert means per-row inserts hit TOO_MANY_PARTS fast. Batch thousands of rows or enable async_insert.

Query Changes

Window functions, CTEs, and standard aggregates carry over -- see the ClickHouse window functions and CTE guides for the dialect details (RANGE frame defaults and CTE inlining behave differently than you'd guess).

Validation

-- Run on both engines
SELECT count(*), sum(total), min(created_at), max(created_at) FROM orders;

CDC caveat: ReplacingMergeTree deduplicates at merge time, so a bare count(*) on ClickHouse can exceed MySQL's until merges settle, and CDC deletes are flag columns, not removals. Validate with FINAL and the connector's delete flag filtered out. CRC32-style row checksums don't transfer across engines -- stick to aggregate comparisons per column.

Common Mistakes

Re-pointing dashboards: the ClickHouse connection guides cover drivers per language, and the port-8123-vs-9000 trap that catches everyone.

Mako connects to MySQL and ClickHouse side-by-side with AI-powered autocomplete -- useful for running validation queries against both ends of a migration. Try it free at mako.ai.