MySQL to ClickHouse Replication: Incremental and CDC

Fritz Larco avatar
Fritz Larco
•
Cover for MySQL to ClickHouse Replication: Incremental and CDC

Last updated: November 2026

To replicate MySQL to ClickHouse, define both connections in Sling, list the MySQL tables in a replication file, and run sling run -r replication.yaml. Sling creates the ClickHouse tables, then copies new rows on each incremental run. For near real-time sync, Sling reads the MySQL binlog with CDC.

This guide shows:

  • The connection setup for self-hosted ClickHouse and ClickHouse Cloud.
  • A full-refresh run and an incremental run, with the real CLI output.
  • How Sling creates the ClickHouse table and applies updates.
  • How to change to binlog CDC when a schedule is not fast enough.

The output below comes from a run with a Docker MySQL as the source and a self-hosted ClickHouse 25.4 as the target.

Install Sling

# macOS / Linux
curl -fsSL https://slingdata.io/install.sh | bash

# Windows
irm https://slingdata.io/install.ps1 | iex

# Python
pip install sling

Make sure that the install is correct:

sling --version

The Sling CLI getting started guide has the full install steps.

Set up the MySQL source

Sling reads connections from ~/.sling/env.yaml, from environment variables, or from sling conns set. For replication, a read-only MySQL user is sufficient:

CREATE USER 'sling'@'%' IDENTIFIED BY '<password>';
GRANT SELECT ON <source_schema>.* TO 'sling'@'%';

Set the connection:

sling conns set MYSQL_SOURCE type=mysql host=host.ip user=sling \
  database=mydb password=mypass port=3306

The same connection in ~/.sling/env.yaml:

connections:
  MYSQL_SOURCE:
    type: mysql
    host: host.ip
    user: sling
    password: mypass
    port: 3306
    database: mydb

If MySQL needs TLS, set tls: skip-verify (or true). Test the connection:

sling conns test MYSQL_SOURCE

The MySQL connection docs list the SSL and SSH tunnel options.

Set up the ClickHouse target

Sling uses the ClickHouse native protocol by default. The port is 9000, or 9440 with TLS.

Self-hosted ClickHouse:

sling conns set CLICKHOUSE type=clickhouse host=host.ip user=default \
  password=mypass port=9000 database=default

ClickHouse Cloud needs TLS, so set secure=true on port 9440:

sling conns set CLICKHOUSE \
  url="clickhouse://default:[email protected]:9440/default?secure=true"

The same self-hosted connection in ~/.sling/env.yaml:

connections:
  CLICKHOUSE:
    type: clickhouse
    host: host.ip
    user: default
    password: mypass
    port: 9000
    database: default

Test it:

sling conns test CLICKHOUSE

To connect over HTTP (port 8123, or 8443 with TLS), set http_url instead. The ClickHouse connection docs list all options.

Run a full-refresh replication

The MySQL source has three tables in a demo_mysql_clickhouse database:

  • customers: 5,000 rows
  • orders: 30,000 rows, with an updated_at timestamp
  • events: 60,000 rows, with an occurred_at timestamp

The replication file:

# replication.yaml
source: MYSQL_SOURCE
target: CLICKHOUSE

defaults:
  mode: full-refresh
  object: demo_mysql_clickhouse.{stream_table}

streams:
  demo_mysql_clickhouse.customers:
    primary_key: [customer_id]

  demo_mysql_clickhouse.orders:
    primary_key: [order_id]
    update_key: updated_at

  demo_mysql_clickhouse.events:
    primary_key: [event_id]
    update_key: occurred_at

Some notes:

  • {stream_table} is a runtime variable. Sling puts the source table name in the target object, so you do not write it for each stream.
  • The streams set primary_key and update_key in full-refresh mode too. The incremental section changes only the mode.
  • Sling creates the demo_mysql_clickhouse database in ClickHouse on the first run.

Run it:

sling run -r replication.yaml

Real output, with some lines removed:

INF Sling Replication [3 streams] | MYSQL_SOURCE -> CLICKHOUSE

INF [1 / 3] running stream demo_mysql_clickhouse.customers
INF reading from source database
INF writing to target database [mode: full-refresh]
INF created table `demo_mysql_clickhouse`.`customers`
INF inserted 5000 rows into `demo_mysql_clickhouse`.`customers` in 0 secs [8,853 r/s] [396 kB]
INF execution succeeded

INF [2 / 3] running stream demo_mysql_clickhouse.orders
INF created table `demo_mysql_clickhouse`.`orders`
INF inserted 30000 rows into `demo_mysql_clickhouse`.`orders` in 1 secs [29,381 r/s] [2.8 MB]
INF execution succeeded

INF [3 / 3] running stream demo_mysql_clickhouse.events
INF created table `demo_mysql_clickhouse`.`events`
INF inserted 60000 rows into `demo_mysql_clickhouse`.`events` in 0 secs [81,559 r/s] [3.2 MB]
INF execution succeeded

INF Sling Replication Completed in 4s | MYSQL_SOURCE -> CLICKHOUSE | 3 Successes | 0 Failures

The run copied 95,000 rows in 3 tables in 4 seconds.

What table does Sling create in ClickHouse?

Sling creates a MergeTree table. When the stream has a primary_key, those columns become the ClickHouse PRIMARY KEY and the ORDER BY key. Without a primary key, the table uses ORDER BY tuple().

Sling maps the column types as follows:

Sling typeClickHouse type
integer, bigintInt64
decimalDecimal(p,s)
floatFloat64
string, text, jsonString
timestamp, datetimeDateTime64(6)
dateDate
boolBool
uuidUUID

Non-key columns get the Nullable(...) form of the type. Primary key columns do not, because ClickHouse does not let a sorting key be nullable by default.

Check the data in ClickHouse

Count the rows after the run:

SELECT 'customers' AS t, count() AS c FROM demo_mysql_clickhouse.customers
UNION ALL SELECT 'orders',    count()   FROM demo_mysql_clickhouse.orders
UNION ALL SELECT 'events',    count()   FROM demo_mysql_clickhouse.events;
+-----------+-------+
| T         | C     |
+-----------+-------+
| customers |  5000 |
| orders    | 30000 |
| events    | 60000 |
+-----------+-------+

Look at some rows to make sure that the types are correct:

SELECT event_id, customer_id, event_type, region, occurred_at
FROM demo_mysql_clickhouse.events
ORDER BY event_id LIMIT 5;
+----------+-------------+------------+--------+-------------------------------+
| EVENT_ID | CUSTOMER_ID | EVENT_TYPE | REGION | OCCURRED_AT                   |
+----------+-------------+------------+--------+-------------------------------+
|        1 |           2 | signup     | us-2   | 2025-01-01 00:00:01 +0000 UTC |
|        2 |           3 | purchase   | us-3   | 2025-01-01 00:00:02 +0000 UTC |
|        3 |           4 | logout     | us-4   | 2025-01-01 00:00:03 +0000 UTC |
|        4 |           5 | page_view  | us-1   | 2025-01-01 00:00:04 +0000 UTC |
|        5 |           6 | click      | us-2   | 2025-01-01 00:00:05 +0000 UTC |
+----------+-------------+------------+--------+-------------------------------+

The numeric, varchar, and timestamp columns are correct. The region column is null on each seventh source row. In ClickHouse it is Nullable(String), and the nulls stay nulls. They do not become the text "NULL".

How do you sync only new MySQL rows to ClickHouse?

Use incremental mode. Sling reads only the rows with an update_key value higher than the highest value in the target.

Remove customers from the streams (it changes slowly, so a weekly full refresh is sufficient) and change the mode:

# replication-incremental.yaml
source: MYSQL_SOURCE
target: CLICKHOUSE

defaults:
  mode: incremental
  object: demo_mysql_clickhouse.{stream_table}

streams:
  demo_mysql_clickhouse.orders:
    primary_key: [order_id]
    update_key: updated_at

  demo_mysql_clickhouse.events:
    primary_key: [event_id]
    update_key: occurred_at

Add 1,000 orders and 2,500 events on the source, then run again:

sling run -r replication-incremental.yaml
INF Sling Replication [2 streams] | MYSQL_SOURCE -> CLICKHOUSE

INF [1 / 2] running stream demo_mysql_clickhouse.orders
INF getting checkpoint value (updated_at)
INF writing to target database [mode: incremental]
INF inserted 1000 rows into `demo_mysql_clickhouse`.`orders` in 0 secs [1,926 r/s] [93 kB]
INF execution succeeded

INF [2 / 2] running stream demo_mysql_clickhouse.events
INF getting checkpoint value (occurred_at)
INF writing to target database [mode: incremental]
INF inserted 2500 rows into `demo_mysql_clickhouse`.`events` in 0 secs [4,040 r/s] [134 kB]
INF execution succeeded

INF Sling Replication Completed in 2s | MYSQL_SOURCE -> CLICKHOUSE | 2 Successes | 0 Failures

At the getting checkpoint value line, Sling reads the highest updated_at in ClickHouse. It uses that value as the lower limit of the source query. Only the new rows move:

SELECT 'orders' AS t, count() c FROM demo_mysql_clickhouse.orders
UNION ALL SELECT 'events', count()   FROM demo_mysql_clickhouse.events;
+--------+-------+
| T      | C     |
+--------+-------+
| orders | 31000 |
| events | 62500 |
+--------+-------+

Orders went from 30,000 to 31,000. Events went from 60,000 to 62,500. These numbers agree with the inserts on the source.

How does Sling apply updates in ClickHouse?

ClickHouse has no MERGE statement. When a stream has a primary_key, Sling uses the delete_insert merge strategy:

  1. Sling loads the new rows into a temporary table.
  2. It deletes the matching keys from the target with ALTER TABLE ... DELETE and mutations_sync = 1.
  3. It inserts the rows from the temporary table.

A changed source row thus replaces the old row. It does not make a duplicate. A delete in ClickHouse is a mutation that rewrites data parts. For an append-only table such as events, set merge_strategy: insert in target_options to skip the delete step.

How do you replicate MySQL to ClickHouse in real time?

Incremental mode reads new rows on a schedule. It does not see deletes, and it needs an update_key column. For near real-time sync with deletes, use change data capture (CDC). Sling reads the MySQL binary log and applies each insert, update, and delete to ClickHouse.

CDC needs a CLI Pro Max token or an Advanced Platform plan.

First, make sure that MySQL writes a row-based binlog, and give the Sling user the replication privileges:

SHOW VARIABLES LIKE 'log_bin';          -- must be ON
SHOW VARIABLES LIKE 'binlog_format';    -- must be ROW
SHOW VARIABLES LIKE 'binlog_row_image'; -- should be FULL

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sling'@'%';

Then set mode: change-capture and a state location:

# replication-cdc.yaml
source: MYSQL_SOURCE
target: CLICKHOUSE

defaults:
  mode: change-capture
  object: demo_mysql_clickhouse.{stream_table}

streams:
  demo_mysql_clickhouse.orders:
    primary_key: [order_id]
  demo_mysql_clickhouse.events:
    primary_key: [event_id]

env:
  # Sling keeps the binlog position here
  SLING_STATE: CLICKHOUSE/sling_state

The first run copies each table in chunks of primary key values and saves the binlog position. Each later run reads the binlog from that position, applies the changes, and saves the new position. To delete in ClickHouse, Sling uses the same delete-then-insert step as above. Set soft_delete: true under change_capture_options to keep deleted rows with _sling_synced_op = 'D'.

Run the file from cron each minute, or start one long process with continuous mode (Sling v1.6.8 and later):

sling run -r replication-cdc.yaml --continuous

The MySQL CDC docs list all options. To decide between CDC and incremental mode, read when to use CDC with Sling.

Other options for MySQL to ClickHouse

  • Partition the table. Set target_options.table_keys.partition to an expression, for example [toYYYYMM(occurred_at)]. Sling adds PARTITION BY to the CREATE TABLE.
  • Use another engine. Set target_options.table_ddl, for example create table {object_name} ({col_types}) engine=ReplacingMergeTree ORDER BY event_id. Sling puts the column list in {col_types}.
  • New source columns. add_new_columns is true by default, so Sling adds new MySQL columns to the ClickHouse table on the next run.
  • Filter at the source. Use a sql: block in a stream to select columns or rows before they leave MySQL.

Where to go next

The same replication file works for other database sources into ClickHouse. Change the source connection only. For a PostgreSQL source, see PostgreSQL to ClickHouse. For DuckDB in the cloud as the target, see MySQL to MotherDuck. To run the replication on a schedule with alerts, use the Sling Platform.

Send questions to Discord or GitHub Issues.

FAQ

How do I replicate MySQL to ClickHouse?

Set a MySQL connection and a ClickHouse connection in Sling, list the MySQL tables as streams in a replication YAML file, and run sling run -r replication.yaml. Sling creates the ClickHouse database and tables on the first run.

Can Sling replicate MySQL to ClickHouse in real time?

Yes, with mode: change-capture. Sling reads the MySQL binary log and applies inserts, updates, and deletes to ClickHouse. Each run takes the changes since the last saved position. CDC needs a CLI Pro Max token or an Advanced Platform plan.

What table engine does Sling use when it creates a ClickHouse table?

Sling creates a MergeTree table. When the stream has a primary_key, Sling uses those columns as the ClickHouse primary key and ORDER BY key. Without a primary key, the table uses ORDER BY tuple().

How does Sling apply updates to existing rows in ClickHouse?

ClickHouse has no MERGE statement. With a primary_key in incremental mode, Sling uses the delete_insert strategy. It deletes the changed keys with ALTER TABLE DELETE and mutations_sync = 1, then inserts the new rows. Set merge_strategy to insert to append only.

How do I partition the ClickHouse table or pick another engine?

For a partition, set target_options.table_keys.partition to an expression such as toYYYYMM(occurred_at). For a different engine, set target_options.table_ddl, for example create table {object_name} ({col_types}) engine=ReplacingMergeTree ORDER BY event_id.

Does Sling connect to ClickHouse with the native protocol or HTTP?

Sling uses the native protocol by default, on port 9000 or 9440 with TLS. ClickHouse Cloud works over native TLS with secure=true. To use HTTP, set http_url on the connection.

Are nullable MySQL columns kept as nulls in ClickHouse?

Yes. Sling creates non-key columns as Nullable types, for example Nullable(String) or Nullable(Int64). Nulls stay nulls. Primary key columns are not Nullable.