Kafka Connect
Building a Real Pipeline — MySQL → Kafka
Step-by-step CDC pipeline setup with Debezium.
Interview: Tested in senior engineering interviews when discussing real-time data pipelines, CDC patterns, and production Kafka architecture. Demonstrates end-to-end systems design knowledge.
Introduction
In this topic, we build a complete, production-grade Change Data Capture (CDC) pipeline: MySQL row changes → Kafka → searchable in Elasticsearch. This is one of the most common real-world Kafka Connect use cases and a frequently asked system design question in engineering interviews.
The pipeline uses Debezium — the industry-standard open-source CDC connector — to stream every insert, update, and delete from MySQL into Kafka in real time, with sub-second latency and zero impact on the MySQL write path.
Pipeline Architecture Overview
┌─────────────┐ binlog ┌─────────────────────┐ produce ┌────────────────────────┐
│ │ ─────────────│ Debezium MySQL CDC │ ────────────│ │
│ MySQL │ │ Source Connector │ │ Kafka Topics │
│ (Primary) │ │ (Kafka Connect │ │ db.ecommerce.orders │
│ │ │ Worker Cluster) │ │ db.ecommerce.users │
└─────────────┘ └─────────────────────┘ └───────────┬────────────┘
│
consume │
┌───────────────────────┘
▼
┌─────────────────────┐
│ Elasticsearch Sink │
│ Connector │
│ (Kafka Connect │
│ Worker Cluster) │
└──────────┬──────────┘
│ index
▼
┌─────────────────────┐
│ Elasticsearch │
│ (Search Engine) │
└─────────────────────┘
Step 1: Configure MySQL for CDC
Debezium reads MySQL's binary log (binlog), which records every row-level change. Enable binlog in MySQL's configuration:
Create a dedicated MySQL user with minimal required permissions for Debezium:
Step 2: Deploy Kafka Connect Workers
Step 3: Deploy the Debezium MySQL Source Connector
After deployment, Debezium performs an initial snapshot of the current table data, then switches to streaming from the binlog. Topics created: db.ecommerce.orders, db.ecommerce.users, db.ecommerce.products.
Understanding Debezium Event Envelope
Each Debezium CDC event has a rich envelope containing the before and after state of the row, plus metadata:
Step 4: Deploy the Elasticsearch Sink Connector
The sink connector will create Elasticsearch indices named after each topic (db.ecommerce.orders, etc.) and upsert documents using the Kafka record key as the document ID — ensuring idempotent updates.
Step 5: Monitor and Verify the Pipeline
Production Considerations
| Concern | Risk | Solution |
|---|---|---|
| Binlog rotation | Debezium loses its binlog position if the log rotates before it's read | Set expire_logs_days ≥ 7; monitor binlog lag |
| Schema changes (DDL) | Adding/removing columns in MySQL can break sink deserialization | Use Schema Registry with backward-compatible Avro schemas |
| Large initial snapshot | Snapshotting a 500M-row table takes hours and creates high Kafka throughput spikes | Use snapshot.mode=schema_only + backfill separately |
| Tombstone records | Delete events produce null-value records; some sinks crash on null values | Configure behavior.on.null.values=delete in sink |
| Backpressure | Slow Elasticsearch causes sink connector task lag, eventually failing | Configure dead-letter queue + tune max.retries and retry.backoff.ms |
Common Mistakes
- Using
tasks.max > 1for Debezium MySQL source: MySQL binlog is a single sequential stream. Debezium MySQL source connector is inherently single-task (tasks.max=1). Setting it higher has no effect — one task reads the entire binlog. - Forgetting the database history topic: Debezium needs a Kafka topic (
database.history.kafka.topic) to store MySQL DDL history. Without it, the connector cannot recover after restart or handle schema changes. - Not applying ExtractNewRecordState SMT: Without the unwrap transform, the Elasticsearch sink receives the full Debezium envelope (before/after/source) instead of just the row data — creating deeply nested, hard-to-query Elasticsearch documents.
Quick Quiz
Q1: Why can't you set tasks.max=4 for a Debezium MySQL connector to process 4 tables in parallel?
Debezium MySQL reads a single, sequential binary log. Unlike JDBC connectors that can poll each table independently, CDC requires reading the binlog in order. The MySQL connector is therefore limited to a single task regardless of tasks.max. Parallelism for multiple databases can be achieved by deploying separate connector instances per database.
Q2: What does snapshot.mode=initial do, and when would you use schema_only?
initial performs a full consistent snapshot of all configured tables before streaming the binlog — useful for fresh deployments. schema_only only snapshots the schema (no row data), then starts streaming from the current binlog position — useful when the data already exists in the target system or you want to avoid a high-volume initial load.
Scenario-Based Challenge
The Challenge: CDC pipeline breaks after MySQL schema change
Your team adds a new NOT NULL column region VARCHAR(50) to the MySQL orders table with a default value. The Debezium connector transitions to FAILED state. Why, and how do you recover without data loss?
Debezium captures the DDL change from the binlog, updates its internal schema in the database history topic, and regenerates the Avro schema with the new field. However, if the Schema Registry is configured with backward compatibility (default), adding a required field without a default fails schema validation. Solution: (1) Add the new column with a default value in Avro schema, (2) update Schema Registry compatibility to BACKWARD, or (3) use FULL compatibility. The connector can then be restarted and will resume from the last committed binlog position — no data is lost because Debezium stores its exact binlog file/position offset.
Interview Questions
1. Explain how Debezium achieves sub-second CDC latency without impacting MySQL write performance.
Debezium connects to MySQL as a replica using the standard replication protocol. It reads from the binary log — which MySQL already writes as part of normal transaction commits — with no additional write overhead. The binlog is a sequential append-only file, so reading it is extremely fast. Debezium positions itself as a replication slave, receives log events pushed by MySQL, and converts them to Kafka records — achieving latency typically under 100ms from commit to Kafka.
2. Design a CDC pipeline for a multi-region e-commerce platform with MySQL in 3 regions. How do you avoid duplicate events in Elasticsearch?
Deploy a separate Debezium connector per region, each writing to region-namespaced topics (e.g., db.us.orders, db.eu.orders). A single Elasticsearch sink connector consumes all three topics. Use the MySQL primary key as the Kafka record key — the Elasticsearch sink uses it as the document ID (_id), making all upserts idempotent. Even if the same event is processed twice (at-least-once delivery), the document is simply overwritten with identical data.