MySQL to Kafka: How to Stream Your Data

Getting MySQL changes onto Kafka is usually attempted twice. The first attempt is a poller: a job that queries rows where updated_at is greater than last time and publishes them. It is quick to write and it silently misses every deletion, plus any update that does not touch the timestamp. The second attempt is a full change data capture stack, which works properly and is a system your team now operates.

This guide covers the managed path in between: replicating MySQL into Kafka topics with Airbyte, reading the binary log so deletes and silent updates both arrive. It also covers one detail of the Kafka destination that decides whether what lands in your topic is a change stream or just a pile of events in an unreliable order.

MySQL to Kafka at a glance:

Why move data from MySQL to Kafka?

Two situations account for most of these pipelines, and one common situation does not justify it at all.

Fan-out is the strongest case. Several independent consumers need the same change stream: a search index, a cache invalidator, an audit store, a downstream service. Without Kafka each of them queries MySQL directly and you have four sources of load on a transactional database. With it, they subscribe to a topic and the database is read once.

Replay is the second. Kafka retains what it receives, so a consumer with a bug can be fixed and re-run from an offset rather than losing the window it was broken for. The case that does not justify it is a single consumer that happens to be a warehouse. There, Kafka is an extra system to operate for no benefit, and syncing MySQL straight into the warehouse is simpler and cheaper.

What do you need before you start?

Most of the prerequisites are on the MySQL side, and one of them causes almost every problem people report:

Binary logging enabled. CDC reads the binary log, so logging has to be on. On a self-managed server this is a configuration file change, and most cloud providers expose it as a single option.

Enough binlog retention. Around ten days, expressed as 864000 seconds, is the figure commonly used. On Amazon RDS this is a different setting called binlog retention hours, and it defaults to zero, which purges logs immediately and makes CDC impossible until you change it through an RDS-specific procedure.

A dedicated read-only user. With replication permissions, rather than an application account. Encryption is required by default and the connection fails if the source will not encrypt. The MySQL source documentation covers the available SSL modes.

Topics created in advance. The destination fails on an unknown topic unless the broker creates topics automatically, and automatic creation is discouraged in production where partition counts and retention should be chosen on purpose. On a database source the set of tables changes rarely, so creating topics by hand is a one-time task.

If your organisation restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list on the database before you begin.

How do you build a MySQL to Kafka pipeline in Airbyte?

Step 1: Prepare MySQL and the broker

Enable binary logging, set retention generously, and create the read-only replication user. On the Kafka side, create the topics you intend to write to, with partition counts and retention chosen for the volume you expect. Doing both first means the connection test in Airbyte tells you something useful rather than failing for reasons that have nothing to do with Airbyte.

Step 2: Configure the MySQL source

Click Sources in the left navigation, then New Source, and select MySQL. Supply the host, port, database, credentials and SSL mode, and choose Read Changes using Binary Log as the replication method. Airbyte tests the connection and takes you to the new connection page. The general flow is described in adding a source.

Step 3: Configure the Kafka destination

Click Destinations, then New Destination, and select Kafka, following adding a destination. Supply the bootstrap servers, security protocol and credentials, and a topic pattern. The pattern accepts namespace and stream variables, so one topic per table is easy to arrange and is usually the structure consumers want.

Step 4: Create the connection and schedule it

Click Connections, then New connection, pick your source and destination, and let Airbyte fetch the schema. Select the tables you want and an incremental sync mode so the binlog is used rather than a full re-read. Set a replication frequency, save, and trigger the first sync, which performs the initial snapshot before switching to reading changes.

Two things to check afterwards. Confirm the topic names on the broker, because a naming transformation replaces certain special characters. And confirm the snapshot completed inside your binlog retention window, since on a very large database it may not have.

Why does the message key matter so much here?

In a purpose-built CDC-to-Kafka setup, change events are keyed by the primary key of the row that changed. That is not decoration. It guarantees every change to a given row lands in the same partition, and therefore that consumers see those changes in the order they happened. An update followed by a delete arrives as an update followed by a delete.

The Airbyte Kafka destination keys each message with the UUID it assigns that record, which is unique per message rather than per row. Records therefore spread across partitions arbitrarily. For raw throughput that is ideal, since load distributes evenly. For a change stream it means the ordering guarantee you probably assumed you had is not there.

How much this matters depends entirely on the consumer. If something downstream materialises current state by applying each change in sequence, out-of-order delivery will corrupt it, and you need a repartitioning step keyed on the primary key before that consumer sees anything. If consumers are appending to a log, feeding a search index that tolerates eventual consistency, or triggering notifications, it matters much less. Decide during design, not after someone reports a resurrected deleted row.

What contract should you give your consumers?

The destination writes JSON and only JSON. There is no Avro output, so consumers cannot validate against a schema registry, and if your organisation standardised on Avro this connector does not fit that convention.

Each message value carries the record identifier, the timestamp Airbyte pulled it, the stream name, and your row data as a nested JSON blob. Consumers reach inside that envelope for the actual columns. Deletes need spelling out too: a deleted row arrives as a record carrying a deletion timestamp in a metadata column, not as a Kafka tombstone with a null value, so anything built for tombstone semantics will not recognise it.

Finally, be clear about timing. Airbyte syncs on a schedule rather than tailing the log continuously, so the topic receives batches at your sync interval rather than a steady trickle. Size partitions and retention for that arrival pattern, and do not promise anyone real-time semantics you are not delivering.

Frequently asked questions

Are MySQL change events ordered correctly in Kafka?

Not per row. Messages are keyed by an Airbyte-generated UUID rather than the primary key, so changes to the same row can land in different partitions. Repartition on the primary key if a consumer applies changes in sequence.

How do deletes appear in the topic?

As a record carrying a deletion timestamp in a metadata column, not as a Kafka tombstone with a null value. Consumers expecting tombstone semantics need adapting.

Can Airbyte write Avro to Kafka?

No. The destination writes JSON, so a schema registry cannot validate what Airbyte produces.

Why does my connection keep doing full snapshots?

The binlog Airbyte needed was purged before it could resume. Extend retention, sync more often, or check whether the initial snapshot is taking longer than the retention window itself.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven, from creating the source through to scheduling the connection. If you would rather manage pipelines as infrastructure, the same source, destination and connection can be provisioned from configuration files instead.

Get your MySQL data into Kafka

Set binlog retention generously before the first sync, create topics deliberately rather than letting the broker do it, and write down the contract for consuming teams: the payload is wrapped, deletes are flagged rather than tombstoned, and per-row ordering needs a repartition. The failures people report on this pipeline almost always come from that contract being assumed rather than stated.

Airbyte's connector catalog includes 600+ pre-built connectors, so the same MySQL source can feed a warehouse alongside the topic. If a warehouse is your only consumer, MySQL to Snowflake covers the simpler route, and for the same destination fed by a warehouse rather than a database see BigQuery to Kafka.

Start syncing now →