MySQL engine

replicare's second engine replicates MySQL → MySQL using the same trigger-based, no-binlog CDC model as Postgres and reuses the engine-neutral copy/apply/reseed/pipeline/observability layers unchanged.

Status: shipped. The MySQL engine runs end to end — trigger capture, chunked initial copy (byte-faithful LOAD DATA), FK-ordered streaming apply (including cyclic NOT NULL components), bloat control (purge/retention/reseed), observability, and the full daemon lifecycle — and coexists with Postgres syncs in one daemon. This page documents the engine's shape and its two operational wrinkles. Try it with the MySQL demo.

Supported versions

  • Source floor: MySQL 5.7. Target: 8.4 (the committed CI pair). 5.6 is best-effort only. See mysql-version-support.md.
  • MariaDB is not supported in v1: its dialect has forked from MySQL's, so the engine detects it at connect and refuses it loudly rather than mis-driving it.

Requirements

  • InnoDB tables only. Non-transactional engines (MyISAM/MEMORY) break the per-component atomic apply and the source consume/track atomicity, so pre-flight blocks them.
  • local_infile=ON is required on the target — it is the copy/apply transport (LOAD DATA LOCAL INFILE). replicare probes it at connect and, if it is off, halts loud with an actionable error rather than failing cryptically mid-copy. Enable it (SET GLOBAL local_infile=1, or local-infile=ON in my.cnf) and set the local_infile: true hint in the target's mysql: block. (A batched-INSERT fallback for local_infile-off targets is deferred to a future release — doing it byte-faithfully across column charsets needs careful design, and a hardening release favors a loud requirement over a risky fallback.)
  • Every replicated table needs a primary key or a usable unique key. Tables without one are skipped with a loud warning.
  • No secondary UNIQUE keys on target tables (beyond the replication key): MySQL's INSERT … ON DUPLICATE KEY UPDATE fires on any unique key and would silently mutate the wrong row, so pre-flight blocks such target tables. This is a hard faithful-transport guarantee.
  • Least privilege (source): SELECT + TRIGGER on the replicated tables, CREATE/owner on the replicare capture database — no SUPER, no REPLICATION SLAVE, no binlog access. (Exact grant SQL ships with MM8.)

The two operational wrinkles

Both follow from the v1 architecture and are worth knowing up front:

1. A MySQL sync still needs a Postgres for state

replicare's own operational state — which syncs exist, per-table copy progress, per-target cursors, and the single-active ownership lock — lives in a Postgres state store (state_store.engine: postgres), regardless of the data engine. This state is small control-plane bookkeeping, entirely separate from your MySQL data: the correctness-bearing replication state (the delta/track tables) lives on the MySQL source, exactly as Postgres puts them on a Postgres source.

So to replicate MySQL→MySQL you also point replicare at a small Postgres for its state. A MySQL-backed state store is a future item behind the pluggable StateStore interface; it is not in v1.

2. MySQL syncs are strictly at-least-once

replicare's delivery is at-least-once with idempotent apply. For a Postgres target, when the state store is that target database, a cursor advance can ride the same transaction as its apply batch — yielding effectively-exactly-once apply per batch.

That optimization is not available for MySQL syncs: the state store is a different engine/server (Postgres) than the MySQL target, so no single transaction can span both. MySQL syncs are therefore strictly at-least-once — correct and convergent (applies are idempotent upserts keyed on the PK), but a crash may re-apply the last batch. This is harmless by design; it's noted only so you don't expect exactly-once parity with a Postgres-target sync.

Installing capture on a busy source

Adding or replacing a trigger needs an exclusive metadata lock on the table. If an application transaction that touched the table is still open, the trigger change has to wait, and while it waits MySQL queues every new query on that table behind it (lock_wait_timeout defaults to a year). replicare therefore:

  • leaves a trigger alone on restart when its stored body already matches, so a restart takes no lock on any application table;
  • creates its replicare database only when it is missing (even CREATE DATABASE IF NOT EXISTS waits behind open application transactions once capture is installed);
  • waits at most 2 seconds for a lock when it does need to change a trigger, then retries with a growing pause, and after repeated failures stops with an error naming the table.

MySQL cannot replace a trigger in one step (drop and create are separate), so changes to that table are not captured during the instant between them. That only happens when a trigger actually changes (an upgrade that alters its body), never on a plain restart.

Configuration

A MySQL sync is an engine-neutral envelope with a typed mysql: block per endpoint, plus the Postgres state store:

state_store:                       # replicare's own bookkeeping (always Postgres)
  engine: postgres
  postgres: { host: state-db, database: replicare_state, user: replicare, password: "${PW}", sslmode: require }

sources:
  app:
    engine: mysql
    mysql: { host: app-db, port: 3306, database: app, user: replicare, password: "${PW}", tls: verify-full }

targets:
  warehouse:
    engine: mysql
    mysql: { host: wh-db, port: 3306, database: warehouse, user: replicare, password: "${PW}", tls: require, local_infile: true }

syncs:
  - name: app-to-warehouse
    source: app
    targets: [warehouse]
    include: ["app.*"]          # db.table globs (a MySQL schema IS a database)
    exclude: ["*_audit"]
  • tls accepts the full spectrum disable | allow | prefer | require | verify-ca | verify-full (mapped onto the driver, including a custom verify-ca).
  • database is the DSN default; selection globs may span other databases, and cross-database FK components are handled.
  • The single-engine rule is enforced: a MySQL source's targets must all be MySQL — never cross-engine.

Validating

replicare validate config.yml

introspects both sides and classifies every column pair (block on incompatible, warn on risky), reports FK components and giant-component/dangling-FK warnings, and enforces the InnoDB / secondary-unique / no-key rules above — all before any sync starts.