ClickHouse to MySQL: How to Move Your Data

Moving ClickHouse to MySQL runs against the usual direction, and that is the point. ClickHouse computes something expensive across billions of rows overnight, and an application needs to read the result per request in single-digit milliseconds. A columnar analytical engine is not built for that access pattern, and a row-oriented operational database is.

This guide covers the managed path with Airbyte. Two things shape it, and both come from the source rather than the destination: ClickHouse's connector reads with a cursor rather than a change log, and the table you point it at may return duplicate rows depending on which engine it uses.

ClickHouse to MySQL at a glance:

Why move data from ClickHouse to MySQL?

Two situations account for most of these pipelines, and both involve a small slice of a large analytical store.

The first is serving computed results back to an application. A usage total shown on a billing page, a score behind a recommendation, a rolled-up metric a customer sees in your product. ClickHouse can compute those beautifully and is not the thing you want fielding thousands of point lookups a second.

The second is giving an existing system a connection it can use. Internal tools, admin panels and older reporting systems that speak MySQL and nothing else. Rather than rewriting them, you land the specific tables they need. In both cases the operative word is specific, which is the theme of everything below.

What do you need before you start?

Two credentials and two decisions, and the decisions are what determine whether the result is correct:

A read-only ClickHouse user. Scoped to the database you are reading. The connector is built on the JDBC code base and does not alter the schema present in your ClickHouse instance, so it is genuinely a read-only operation on the source side. SSL and SSH tunnel connections are both supported. The ClickHouse source documentation covers the connection options.

A narrow table or view designed for this pipeline. Do not point this at a wide analytical table. ClickHouse is columnar, so a table with two hundred columns is cheap to scan when you touch three of them. MySQL is row-oriented and reads the whole row regardless, which means a table that performs well in the source can perform badly in the destination at the same row count.

A column that can serve as a cursor. The connector only offers incremental sync on a table where at least one column can be used as a cursor field. Where none exists, that table is full refresh, and on an analytical store that can be an expensive read.

A MySQL user with write access. A database and a user able to create and write tables in the target schema. Airbyte adds its own metadata columns alongside your data, so decide whether the consuming application tolerates them or reads a view that excludes them.

If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on both systems before you begin.

How do you build a ClickHouse to MySQL pipeline in Airbyte?

Step 1: Build the result table in ClickHouse

Create a table or view containing exactly the rows and columns the consuming application reads, with a timestamp column maintained on every write so there is something to use as a cursor. Keep the column names short enough to stay clear of MySQL's 64 character identifier limit, which analytical column naming reaches faster than you would think. This is the step that makes the rest of the pipeline straightforward.

Step 2: Configure the ClickHouse source

Click Sources in the left navigation, then New Source, and select ClickHouse, following adding a source. Supply the host, port, database and credentials, and enable SSL or configure an SSH tunnel if your instance sits behind a bastion. Namespaces are supported and enabled by default, so your ClickHouse database structure carries across rather than flattening.

Step 3: Configure the MySQL destination

Click Destinations, then New Destination, and select MySQL, following adding a destination. Supply the host, port, database and credentials. If the consuming application is latency-sensitive, think about which MySQL instance this writes to, because a sync writing a large table is a write load arriving on a database that is also serving requests.

Step 4: Create the connection and nominate a cursor

Click Connections, then New connection, select your tables and nominate a cursor field so you can use an incremental sync mode. If a table offers no incremental option in the interface, that means no column on it can serve as a cursor, which is a signal to go back to step 1 rather than to accept full refresh.

Set a frequency that matches how often the underlying computation actually reruns. Syncing hourly from a table rebuilt nightly is work for nothing, and on an analytical store the read is not free.

Why might your source table contain duplicates?

This is the detail most likely to produce a wrong answer in MySQL, and it originates entirely in ClickHouse. Some ClickHouse table engines, notably ReplacingMergeTree, deduplicate rows during background merges rather than on write. Between a write and a merge, the table genuinely holds more than one version of a row.

Querying such a table without the FINAL operator can return those pre-merge duplicates. A pipeline reading it is doing exactly that kind of query, so whatever duplicates exist at sync time are copied faithfully into MySQL, where nothing will ever collapse them because MySQL has no equivalent background merge.

So check which engine your source table uses. If it is one that deduplicates on merge, sync a view that applies FINAL rather than the table itself, and accept the query cost as the price of correctness. This is a case where the same behaviour that is a mild annoyance for an analyst becomes a durable data quality problem once the rows leave ClickHouse.

What does the cursor miss?

Incremental sync here is cursor-based. The connector's feature table lists both incremental deletes and log-based replication as coming soon, which means today a row removed from ClickHouse persists in MySQL indefinitely, and nothing reports the discrepancy.

On this particular source that matters less than it would elsewhere, because analytical tables in ClickHouse tend to be append-oriented. Events, metrics, aggregates recomputed rather than edited. If your result table is rebuilt each night rather than mutated in place, there are no deletions to miss.

Where rows do leave the result set, for instance when a customer drops out of a segment, the clean fix is a flag in the ClickHouse table that your application filters on, rather than removing the row. The blunt fix is a periodic full refresh on that table. Decide which before someone builds a feature on a segment that never shrinks.

Frequently asked questions

Does the ClickHouse source support change data capture?

Not currently. Incremental sync is cursor-based, and both incremental deletes and log-based replication are listed as coming soon on the connector.

Why does my MySQL table have duplicate rows?

Your ClickHouse source table probably uses an engine that deduplicates during background merges. Reading it without FINAL returns pre-merge duplicates, which are then copied permanently. Sync a view that applies FINAL instead.

Why is incremental sync unavailable on one of my tables?

The connector only offers incremental where at least one column can serve as a cursor field. Add a maintained timestamp column to the table or view you are syncing.

Should I sync a wide analytical table?

No. ClickHouse is columnar and MySQL is row-oriented, so a wide table that scans cheaply in the source reads expensively in the destination. Narrow it first.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. You will want to write the ClickHouse view that narrows the result and, if your engine needs it, applies FINAL.

Get your ClickHouse data into MySQL

Narrow the result in ClickHouse, check which table engine you are reading and apply FINAL if it deduplicates on merge, maintain a timestamp column so incremental is available, and decide what a removed row should mean before an application depends on it.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same pattern applies to other analytical and operational pairings. For the equivalent pipeline out of a warehouse, see BigQuery to MySQL, and for ClickHouse as a destination rather than a source, Mixpanel to ClickHouse.

Start syncing now →