MySQL to ClickHouse: How to Replicate Your Data
Replicate MySQL into ClickHouse with Airbyte. Binlog CDC setup, the async_insert fix, type mapping, and why your queries need the FINAL operator.
Replicating MySQL to ClickHouse is what teams do when the reporting queries start hurting the application. MySQL keeps serving transactions; ClickHouse takes the aggregations, the dashboards, and the scans that were never going to be fast on a row store.
Two things decide whether this goes smoothly. Where MySQL is hosted, because managed providers handle binary logs differently, and one query habit on the ClickHouse side that keeps your results correct.
How do you set up MySQL CDC?
The MySQL source offers CDC via the binary log and a standard cursor method. Use CDC. It captures deletions, scales past a terabyte, and does not depend on your tables having a usable updated_at column. That matters more than usual here, because ClickHouse deduplication needs to know which rows superseded which.
Create a dedicated read-only user with GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO <user_name>; then enable binary logging with binlog_format = ROW, binlog_row_image = FULL, a unique non-zero server-id, and retention of about seven days.
Check your host before assuming those settings apply. Amazon RDS ignores binlog_expire_logs_seconds and uses a binlog retention hours parameter that defaults to 0, deleting logs immediately, so set it with mysql.rds_set_configuration. DigitalOcean's managed MySQL clears binary logs on its own schedule and needs a support request to stop. PlanetScale caps queries at 100K records while Airbyte batches at 500K. MariaDB works but has CDC limitations worth testing early. Our MySQL to PostgreSQL guide has the full provider breakdown.
Tables need a primary key for CDC incremental syncs. Without one they can still be replicated by CDC, but only in full refresh mode.
How do you configure ClickHouse?
Create a dedicated user rather than reusing an existing one. The ClickHouse destination needs grants to create and manage databases, to create, alter, drop and truncate tables, and to insert and select data. Server 21.8.10.19 or later is required.
One setting produces a failure whose error message points nowhere useful. If async_insert is enabled on your instance, disable it for the Airbyte user with ALTER USER airbyte_user SETTINGS async_insert = 0; Otherwise the connection check complains about rows not reaching the check table while writing nothing at all.
Enter the hostname without a protocol prefix, the port (8123 for HTTP, 8443 for HTTPS), the database, and credentials. Version 2.0.0 of the destination was a rewrite that writes directly into typed columns rather than JSON blobs in raw tables.
How do MySQL types land in ClickHouse?
Most of the mapping is unremarkable, but a few conversions will change how you write queries.
Why do your queries need FINAL?
Deduplication uses the ReplacingMergeTree engine, which collapses duplicate rows during background merges rather than at write time. Merges happen when ClickHouse decides, not when your sync finishes. Until one runs, a plain SELECT can return duplicated rows, or rows that CDC already deleted in MySQL.
Add the FINAL operator for guaranteed deduplicated results: SELECT * FROM your_table FINAL Without it, your ClickHouse dashboards will disagree with MySQL in a way that looks like a broken pipeline but is not.
Cursor type affects dedup quality too. Integer, Date, and DateTime64 cursors order correctly, while a string cursor makes the connector fall back to ordering by _airbyte_extracted_at, which may not reflect the true order of changes. A warning appears in the sync logs when that happens.
Frequently asked questions
Why am I seeing duplicate rows in ClickHouse?
Your query is missing FINAL. ReplacingMergeTree deduplicates during background merges, so until one runs, duplicates and deleted rows remain visible to a plain SELECT.
Why do my syncs keep falling back to full refresh?
The logs will say the saved offset is no longer present on the server, meaning MySQL removed binlogs Airbyte still needed. Sync more often, raise binlog retention, or both. On RDS check the binlog retention hours parameter specifically.
Do my tables need primary keys?
For CDC incremental syncs, yes. Tables without a primary key can be replicated by CDC only in full refresh mode, which also means no meaningful deduplication in ClickHouse.
How are MySQL JSON columns handled?
They land as the native ClickHouse JSON type if you enabled that option, and as String otherwise. Enable it if your schema uses JSON columns, since parsing strings on every query is avoidable work.
Should I replicate from a replica instead of the primary?
Usually yes. Enable log_replica_updates so the replica writes to its own binlog, then point the connector at it. Since the whole reason for this pipeline is taking load off MySQL, reading from a replica is consistent with the goal.
Get your MySQL data into ClickHouse
Check your host's binlog behaviour, disable async_insert for the Airbyte user, enable the JSON type if you need it, and remember FINAL. Replicating a different database into ClickHouse? See our guides to PostgreSQL to ClickHouse and MongoDB to ClickHouse.
Integrate with 600+ apps using Airbyte
Move data from 600+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.