Skip to content

MySQL source: CDC from the binlog

Stream a MySQL table into a queue from the binlog, with exactly-once effects, set up with one HTTP call to the broker and no other process.

Updated View as Markdown

The broker can stream a MySQL table into a queue, with no other process. The source reads the binlog the way a replica does and turns each committed row change into a message, in a partition named after the row’s key, in commit order. It is set up with one HTTP call, runs on every node of a cluster, and gives exactly-once effects.

Why
getting changes out of MySQL usually means Debezium, a Kafka Connect cluster and Kafka. Here it is one call on the broker you already run.

What MySQL needs

Setting Value Why
Version MySQL 8.0 or later, tested on 8.0, 8.4 and 9.7 (not MariaDB) the binlog format and GTIDs the source reads
binlog_format, binlog_row_image ROW, FULL (MySQL’s defaults) every event carries the whole row
binlog_row_metadata FULL column names and the key come from the binlog itself
gtid_mode, enforce_gtid_consistency ON the source keeps its place by GTID
The source’s user REPLICATION SLAVE, REPLICATION CLIENT, SELECT it reads the binlog, the server’s position, and the tables

The broker checks each one when the source starts, and a source that finds one wrong stops with the setting’s name and the fix. It also needs QUEEN_ENCRYPTION_KEY, because it stores the database password sealed with that key.

The source

Start a MySQL with the settings above:

docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=secret mysql:8.4 \
  --gtid-mode=ON --enforce-gtid-consistency=ON --binlog-row-metadata=FULL

Take a table with two rows:

docker exec -i mysql mysql -uroot -psecret <<'SQL'
CREATE DATABASE shop;
CREATE TABLE shop.orders (
  id         BIGINT PRIMARY KEY,
  customer   JSON NOT NULL,
  status     VARCHAR(20) NOT NULL,
  total      DECIMAL(12,2) NOT NULL,
  created_at TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
);
INSERT INTO shop.orders (id, customer, status, total) VALUES
  (1, '{"name": "Ada", "tier": "gold"}', 'new', 42.00),
  (2, '{"name": "Linus", "tier": "silver"}', 'new', 17.50);
SQL

Create the source. "engine": "mysql" is what makes it a MySQL source; the port is 3306 when the document does not name one:

curl -s -X PUT localhost:6632/api/v1/connectors/shop-src \
  -H 'content-type: application/json' -d '{
  "kind": "source",
  "engine": "mysql",
  "connection": {"host": "mysql", "database": "shop", "user": "root",
                 "password": "secret", "sslMode": "require"},
  "source": {"tables": [{"table": "orders", "queue": "shop.orders"}]}
}' >/dev/null
curl -s localhost:6632/api/v1/connectors/shop-src \
  | jq -c '.status | {phase, pointerGtids, messages}'
{"phase":"streaming","pointerGtids":"c2a725d4-de77-11f1-8a01-72a0352623ab:1-8","messages":2}

Within a few seconds the source copied the two rows already there and started streaming from the server’s GTID set. Each row is a message in the partition named after its key:

Q='batch=10&partitions=10&consumerGroup=reader&subscriptionMode=all'
curl -s "localhost:6632/api/v1/pop/queue/shop.orders?$Q" | jq -c '.messages[0].data'
{"op":"r","table":"shop.orders","key":{"id":1},
 "after":{"id":1,"customer":{"name":"Ada","tier":"gold"},"status":"new","total":42.00,
          "created_at":"2026-11-12T09:02:25.354534+00:00"},
 "before":null,"ts":"2026-11-12T09:02:25.653429Z","seq":0}
Field Example What it is
op r c insert, u update, d delete, r a row copied at the start.
key, after, before The row’s key, the new row (null for a delete), the old row (null for an insert).
gtid c2a7…23ab:9 The MySQL transaction. A copied row has none.
ts, seq The commit time, and the change’s place inside its transaction.

The message is the PostgreSQL source’s, with gtid in place of lsn and xid, so a consumer reads both the same way. Then we changed the table: one update, then an insert and a delete in one transaction:

docker exec -i mysql mysql -uroot -psecret shop <<'SQL'
UPDATE orders SET status = 'paid' WHERE id = 1;
START TRANSACTION;
INSERT INTO orders (id, customer, status, total) VALUES (3, '{"name": "Grace", "tier": "gold"}', 'new', 99.90);
DELETE FROM orders WHERE id = 2;
COMMIT;
SQL
{"op":"u","key":{"id":1},"gtid":"c2a725d4-de77-11f1-8a01-72a0352623ab:9"}
{"op":"d","key":{"id":2},"gtid":"c2a725d4-de77-11f1-8a01-72a0352623ab:10"}
{"op":"c","key":{"id":3},"gtid":"c2a725d4-de77-11f1-8a01-72a0352623ab:10"}

The insert and the delete arrived with the same gtid, in one QueenMQ transaction, so no consumer could see one without the other. Each message id is built from the GTID and the change’s place in it (my:9984e7b3:c2a725d4de7711f18a0172a0352623ab:10:1), so it is the same however many times it is sent.

Why
the source writes the messages and the GTID set it reached in the same QueenMQ transaction. After a crash the next owner asks MySQL for every transaction outside that set: nothing is lost and nothing is sent twice.

Options

The source block:

Field Default What it does
tables[].table database.table, or table in connection.database. Every table needs a primary key.
tables[].queue The queue its messages go to.
tables[].partitionBy "key" "key" (the primary key), "single" (one partition, all: total commit order), or a list of columns.
snapshot "initial" "initial" copies the rows already there first; "never" sends only new changes.
snapshotChunkRows 2000 Rows per read while copying.
onTruncate "skip" "emit" sends a t message for a TRUNCATE (it needs partitionBy: "single").
serverId from the name The replica id the source registers with, in 2^31..2^32 unless you set it.
heartbeatSeconds 10 How often an idle MySQL says so. A connection silent for three of them and ten seconds more is opened again.
maxBundleMessages, maxBundleBytes, lingerMs 1000, 4 MiB, 20 How big a QueenMQ transaction gets and how long it waits to fill.

The connection block takes host, port (3306), database, user, password, sslMode (disable, prefer, require, verify-full), sslRootCert and connectTimeoutMs, or a url such as mysql://repl:pw@db:3306/shop?ssl-mode=REQUIRED.

Why
the copy runs while the binlog streams, so a large table needs no lock and no pause in writes. A row that a transaction changes while it is being copied is left to that transaction’s message, which carries the whole row.

Text is read in the column’s own charset: utf8mb4, latin1, the UTF-16 and UTF-32 forms, GBK, GB18030, Big5, Shift-JIS, EUC-JP, EUC-KR, and the Windows, ISO-8859 and KOI8 pages. A column in any other charset is read as UTF-8.

Seven things to know

  • MySQL keeps its binlog for a time, not for the source. A stopped source never fills the database’s disk. One stopped for longer than binlog_expire_logs_seconds (30 days by default) finds its binlog purged and stops with binlog_purged: POST /api/v1/connectors/shop-src/resync copies the tables again under a new epoch.
  • A failover keeps the place. A GTID is the same on every server of a replication group, so after a failover the source goes on from its pointer on the new primary.
  • A schema change follows; a rename stops the source. After ALTER TABLE, the next messages carry the new columns, and a copy still running reads them again for each chunk. A RENAME or DROP of a source table stops the source with table_gone, naming the statement: put the new name in source.tables and it goes on from where it stopped. A swap in one statement, as gh-ost and pt-online-schema-change make (RENAME TABLE orders TO _del, _new TO orders), keeps the name and does not stop it.
  • A row change must fit in one QueenMQ transaction: 96 MiB by default (QUEEN_RAFT_ENTRY_MAX_BYTES). An update carries the row twice, before and after, and binary values travel as hex at twice their size, so the update of a row holding a 24 MB BLOB is about 96 MiB. A bigger one stops the source with queen_refused (too_large) until the limit is raised; each node then holds entries that big in memory while it replicates them.
  • Two readers with one replica id push each other out. If your own replicas use ids above 2^31, set serverId.
  • Watch queen_mysql_source_lag_seconds. It is the time since the commit of the last transaction the source has sent, and 0 once it has read to the end of the binlog.
  • Not covered: MariaDB, partial JSON updates (binlog_row_value_options = PARTIAL_JSON), and a sink into MySQL. An XA transaction is sent at its XA PREPARE: a later XA ROLLBACK is not undone.

DELETE /api/v1/connectors/shop-src removes a MySQL source at once: nothing of it lives on the database.

See also

  • PostgreSQL: the source and the sink for PostgreSQL.
  • HTTP API: the /api/v1/connectors endpoints.
  • Metrics: queen_mysql_source_* and queen_mysql_connector_*.
Navigation

Type to search…

↑↓ navigate↵ selectEsc close