Version: Next

MySQL Binlog Replication (Native CDC)

Stream every INSERT, UPDATE, and DELETE from a MySQL table directly into a Spice-accelerated dataset by subscribing to the source's binary log (binlog) — the MySQL analog of PostgreSQL Logical Replication.

This is the recommended way to keep a Spice accelerator (DuckDB, SQLite, PostgreSQL, Cayenne, Turso, Arrow) continuously in sync with a MySQL source. No Kafka, no Debezium, no external CDC infrastructure — one spicepod.yml entry gives you a locally materialized, continuously updated copy of a MySQL table.

How it works

┌────────────┐   binlog (ROW events)   ┌──────────────────────────────┐
│   MySQL    │ ──────────────────────► │            spiced            │
│  (source)  │                         │  decode → ChangeBatch →      │
│            │                         │  accelerator upsert/delete   │
└────────────┘                         └──────────────────────────────┘
       ▲                                        │
       └── resume position persisted in the ────┘
           accelerator (spice_sys_mysql_binlog)

On first start the connector:

Validates and discovers. Spice validates the source server settings (see Prerequisites) and reads the table's column layout from information_schema (binlog row events are positional — they carry no column names).
Captures the head position. The current binlog file and offset is captured before the snapshot, so changes racing the snapshot are delivered at least once and converge via the primary-key upsert.
Snapshots. The table's existing rows stream in over a START TRANSACTION WITH CONSISTENT SNAPSHOT read, in batches of mysql_replication_bootstrap_batch_size. A truncate barrier is applied first so a re-bootstrap over a persistent accelerator starts clean.
Persists the position. Once the snapshot is durably applied, the captured head position is written to the accelerator's spice_sys_mysql_binlog sidecar table. This is Spice's replacement for a Postgres replication slot — MySQL keeps no per-replica cursor server-side.
Streams. Spice attaches as a replica (COM_BINLOG_DUMP) and turns committed transactions into insert/update/delete/truncate changes. The committed position checkpoints to the sidecar every mysql_replication_checkpoint_interval.

On subsequent restarts, if a persisted position exists, the connector resumes from it directly — no snapshot — and marks the dataset ready immediately. Because the position lives inside the accelerator itself, data and cursor share one lifecycle: a non-persistent accelerator (arrow, or mode: memory) boots with no position and naturally re-snapshots — no special configuration needed.

Delivery is at-least-once: a crash between applying a change and checkpointing its position replays up to one checkpoint interval of history, which the primary-key upsert absorbs idempotently. This is the same contract as the PostgreSQL connector's snapshot/WAL boundary.

Minimal configuration

datasets:
  - from: mysql:mydb.orders
    name: orders
    params:
      mysql_host: db.internal
      mysql_tcp_port: '3306'
      mysql_user: replicator
      mysql_pass: ${secrets:mysql_pass}
      mysql_db: mydb
    acceleration:
      enabled: true
      engine: duckdb
      mode: file
      refresh_mode: changes
      primary_key: id
      on_conflict:
        id: upsert

primary_key + on_conflict: upsert are required (except on the append-only arrow engine): UPDATE events apply as upserts keyed on the primary key and DELETE events are routed by it. The connector fails fast at startup with an actionable message if either is missing.

All upsert-capable accelerator engines are supported — duckdb, sqlite, cayenne, postgres, and turso — and each persists the binlog resume position in its spice_sys_mysql_binlog sidecar when file-backed. The arrow engine works append-only (UPDATEs insert new rows).

Prerequisites

The following source server settings are required. Spice validates them at startup and fails fast with an actionable message if any is wrong.

Setting	Required value	Notes
`log_bin`	`ON`	Default on MySQL 8.0+.
`binlog_format`	`ROW`	Default on MySQL 8.0+. Validated at startup.
`binlog_row_image`	`FULL`	Default. Validated at startup; `MINIMAL` images are rejected.
`binlog_row_value_options`	`''` (empty)	Validated at startup; partial JSON row images cannot be applied.
`binlog_expire_logs_seconds`	≥ longest expected spiced downtime	See When the position is purged.

The connecting user needs:

GRANT SELECT ON mydb.orders TO 'replicator'@'%';           -- snapshot + layout discovery
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'%';

Parameters

Configure replication behavior with the following params on the MySQL dataset:

Parameter	Default	Description
`mysql_replication_server_id`	derived	The `server_id` this replica registers with. Must be unique among all replicas attached to the source; the default is derived from the dataset name and process, so two spiced instances don't collide.
`mysql_replication_snapshot_mode`	`auto`	When existing rows load: `auto` snapshots when no resumable position exists; `never` streams changes only; `always` re-snapshots on every start.
`mysql_replication_checkpoint_interval`	`10s`	How often the committed position persists to the sidecar. Bounds crash-replay volume.
`mysql_replication_bootstrap_batch_size`	`8192`	Rows per emitted snapshot batch. Maximum: `1048576`.
`mysql_replication_invalid_position_behavior`	`error`	What to do when the persisted position was purged from the source: `error`, or `rebootstrap` (drop the position and re-snapshot).

The runtime-level CDC apply tunables (cdc_prefetch_buffer, cdc_max_coalesced_envelopes, cdc_max_coalesced_bytes, cdc_max_coalesce_age_ms, cdc_commit_timeout_ms) apply to this connector the same way they do to the PostgreSQL one — see Tuning ingestion.

When the position is purged

MySQL expires binary logs on its own schedule (binlog_expire_logs_seconds, default 30 days; much shorter on some managed services). If spiced is down long enough for the persisted position's file to be purged, the stream cannot resume losslessly. By default this surfaces as an error naming the fix; set:

params:
  mysql_replication_invalid_position_behavior: rebootstrap

to instead drop the stale position, truncate the accelerator, and re-snapshot the table automatically.

Semantics and type notes

TRUNCATE TABLE on the source applies as a truncate on the accelerator.
Primary-key updates (UPDATE ... SET id = ...) apply as a delete of the old key plus an upsert of the new row, so no orphan rows linger.
TIMESTAMP columns replicate as UTC (that is how the binlog stores them), and the snapshot pins its session to UTC to match. Set mysql_time_zone: '+00:00' (the default) so federated reads agree.
Zero dates (0000-00-00) coerce to NULL, matching the read connector's default mysql_zero_date_behavior: null.
ENUM / SET columns resolve to their label strings using the definition in information_schema.
UNSIGNED integers map to the same signed Arrow types the read connector uses; values above the signed maximum fail loudly rather than wrap.
Negative TIME values and spatial/geometry types are not supported — exclude such columns from the dataset schema.

Schema changes

On ALTER TABLE against the replicated table, Spice re-fetches the table's layout from information_schema and keeps streaming as long as every dataset column still exists on the source — the same tolerance the PostgreSQL connector's block mode has:

Columns added on the source are not replicated (a warning names them); add them to the dataset schema and restart to capture them.
Dropping or renaming a dataset column (or RENAME TABLE / DROP TABLE) stops the stream with an actionable error.
Retyping a dataset column keeps streaming while values remain convertible to the dataset's Arrow type; an unconvertible value stops the stream with a decode error.

If the stream stopped across a DDL boundary with an un-checkpointed tail, the restart may be unable to decode pre-DDL events — re-bootstrap with mysql_replication_invalid_position_behavior: rebootstrap. Quiescing writes to the table around DDL avoids that case entirely.

Metrics

Exposed under dataset_mysql_* alongside the connection-pool component metrics:

Metric	Meaning
`replication_lag_ms`	Now minus the newest applied source commit timestamp (1s granularity).
`replication_lag_bytes`	Binlog bytes between the source head and the resume position. Reported only while both are in the same binlog file; absent otherwise.
`replication_source_head_file` / `replication_source_head_pos`	The source server's binlog head, polled every checkpoint interval.
`replication_committed_binlog_file` / `replication_committed_binlog_pos`	The checkpointed resume position.
`replication_transactions_total`	Source transactions observed.
`replication_inserts_total` / `replication_updates_total` / `replication_deletes_total` / `replication_truncates_total`	Row events applied.
`replication_bootstrap_rows_total` / `replication_bootstrap_rows_expected` / `replication_bootstrap_complete`	Snapshot progress.
`replication_decode_errors_total`	Binlog decode failures.
`replication_schema_mismatch_errors_total`	Mid-stream DDL detections.
`replication_recv_errors_total` / `replication_reconnects_total`	Transport health.
`replication_checkpoint_persists_total` / `replication_checkpoint_persist_errors_total`	Sidecar checkpoint writes.

Limitations

File + position tracking, not GTID. Resume positions do not survive a source failover to a different primary. GTID auto-positioning is a planned follow-up.
One table per dataset, one binlog connection per dataset. (PostgreSQL offers shared slots; a shared binlog connection is a follow-up.)
Schema evolution is block-mode only — compatible ALTER TABLE is tolerated (see Schema changes), but on_schema_change policies that adopt new columns (append_new_columns / sync_all_columns) are not yet wired to this connector.
XA (two-phase) transactions are not supported. An XA transaction that touches the replicated table stops the stream with an error; XA activity on other tables logs a warning and is ignored.
Not supported source types: geometry/spatial, vectors, negative TIME.

How it works​

Minimal configuration​

Prerequisites​

Parameters​

When the position is purged​

Semantics and type notes​

Schema changes​

Metrics​

Limitations​