Cross-Database Migration via CDC (MySQL → Postgres)

A change-data-capture (CDC) pipeline that streams every insert, update and delete from MySQL into Postgres within seconds, through Debezium and Kafka. This is the foundation a near-zero-downtime database migration is built on.

Scope, honestly: the CDC pipeline is live for the project1_jobs database (8 tables, about 11,000 jobs, kept in sync daily). The migration itself is not done:

• no app has been cut over to Postgres,
• no cloud target is in use,
• the other 9 databases still need their schemas translated by hand.

Tools

Tool	Used for
Debezium MySQL connector 3.2.6 (Kafka Connect)	Reads MySQL's binlog like a replica would, with no polling SELECTs
Apache Kafka + ZooKeeper (Confluent 7.6.1)	Durable, replayable buffer, one topic per table
Confluent JDBC sink connector 10.7.6	Applies the changes to Postgres as upserts and deletes
PostgreSQL 16	Target database
Custom Kafka Connect image	Adds Java 17 and both connector plugins

Steps

1. Check the source is already CDC-ready

What it does: Debezium needs row-based binlogs with full row images. Replication had already set these.

SHOW VARIABLES WHERE Variable_name IN
  ('binlog_format','binlog_row_image','log_bin','gtid_mode','binlog_expire_logs_seconds');
-- binlog_format ROW, binlog_row_image FULL, log_bin ON, gtid_mode ON,
-- binlog_expire_logs_seconds 2592000  (30 days of catch-up room)

Result: no MySQL changes needed. CDC and native replication read the same binlog.

2. Build a Connect worker that can talk to MySQL 8.4

What it does: MySQL 8.4 removed SHOW MASTER STATUS. Only Debezium 3.0+ uses the new syntax, and that needs Java 17, while the Connect base image ships Java 11. So Java 17 is installed alongside it, instead of swapping the whole image and risking a mismatch with the running Kafka broker.

FROM confluentinc/cp-kafka-connect:7.6.1
USER root
RUN microdnf install -y java-17-openjdk-headless && microdnf clean all
ENV JAVA_HOME=/usr/lib/jvm/jre-17-openjdk
ENV PATH="${JAVA_HOME}/bin:${PATH}"
USER appuser
RUN confluent-hub install --no-prompt debezium/debezium-connector-mysql:3.2.6-3 && \
    confluent-hub install --no-prompt confluentinc/kafka-connect-jdbc:10.7.6

3. Source connector: binlog to Kafka

What it does: takes an initial snapshot, then streams changes. It unwraps Debezium's change envelope into plain rows, and turns deletes into rewritten events so the sink can apply them.

{
  "connector.class": "io.debezium.connector.mysql.MySqlConnector",
  "database.hostname": "mysql",
  "database.user": "debezium",
  "topic.prefix": "swe2mysql",
  "database.include.list": "project1_jobs",
  "snapshot.mode": "initial",
  "snapshot.locking.mode": "minimal",
  "transforms": "unwrap",
  "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
  "transforms.unwrap.delete.handling.mode": "rewrite"
}

4. Sink connector: Kafka to Postgres

What it does: subscribes to every project1_jobs topic, strips the prefix to get the table name, and upserts on the source primary key. Replaying a topic from the start is therefore idempotent, not a source of duplicates.

{
  "connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
  "connection.url": "jdbc:postgresql://postgres-target:5432/postgres",
  "topics.regex": "swe2mysql\\.project1_jobs\\..*",
  "transforms": "route",
  "transforms.route.type": "org.apache.kafka.connect.transforms.RegexRouter",
  "transforms.route.regex": "swe2mysql\\.(.*)",
  "transforms.route.replacement": "$1",
  "insert.mode": "upsert",
  "pk.mode": "record_key",
  "delete.enabled": "true",
  "auto.create": "false"
}

Postgres tables are created by hand, translated from the MySQL schema, with id as GENERATED BY DEFAULT AS IDENTITY so the source's real IDs are kept.

5. Verify: counts on both sides

-- same query on MySQL and on Postgres
SELECT (SELECT COUNT(*) FROM project1_jobs.jobs), (SELECT COUNT(*) FROM project1_jobs.job_descriptions),
       (SELECT COUNT(*) FROM project1_jobs.companies);

Result (2026-10-13): MySQL 11,135 / 9,752 / 7,627 and Postgres 11,135 / 9,752 / 7,627, an exact match. All 8 tables flow through it: jobs, companies, locations, sources, descriptions, legitimacy checks, alternate links, rejects. Since the failover project, a post-failover hook repoints the source connector automatically when MySQL's primary changes.

Issues encountered

Connector version didn't exist

• Cause: debezium-connector-mysql:2.6.2 wasn't on Confluent Hub.
• Fix: looked up the real version list through the Hub API instead of guessing.

SHOW MASTER STATUS rejected by MySQL 8.4

• Fix: Debezium 3.x plus Java 17 (step 2). Same class of bug as mysqld_exporter v0.15 in the monitoring project.

Sink refused deletes

• Cause: delete.enabled with pk.mode=record_key needs a typed key.
• Fix: schemas.enable=true on both key and value converters.

IDs rewritten in Postgres

• Cause: GENERATED ALWAYS AS IDENTITY overrides inserted IDs.
• Fix: GENERATED BY DEFAULT, since a migration must keep the source IDs.

Child rows arrived before their parents

• Cause: Kafka orders events within a topic, not across topics. A burst of about 390 jobs reached Postgres before their companies did, and the foreign keys stopped the sink.
• Fix: first, backfilled about 340 missing parent rows from a MySQL/Postgres ID diff. Then, after a 380-company batch halted it again, dropped the Postgres-side FKs. MySQL stays the integrity authority, and the FK definitions are kept in a backup file.

Deletes from ON DELETE CASCADE never reached Postgres

• Cause: InnoDB cascades aren't written to the binlog, so Debezium never sees them. A purge left 3,879 orphaned descriptions in Postgres.
• Fix: the pipeline now deletes child rows explicitly before parents.

A new table stalled all CDC

• Cause: the sink subscribes by regex with auto.create=false, so a new MySQL table without its Postgres twin stopped everything.
• Fix: always create the Postgres table first. Topic discovery takes about 5 minutes.

Task said RUNNING while disconnected

• Cause: during the failover drill, Debezium kept reporting RUNNING while its log showed Failed to restore connection to mysql:3306 every minute.
• Fix: check the log, not the status. The post-failover hook now repoints it.

Characters looked different on each side

• Cause: suspected CDC corruption. Hex comparison (HEX(title) vs encode(title::bytea,'hex')) proved the bytes were identical. The difference came from how the two CLIs rendered text; the real bug was double-encoding at scrape time (see the Data modeling project).

Still to do for an actual migration

• Schemas: translate the other 9 databases (analytics, bookstack_db, budget_db, metabase_app, movielens, portfolio, project3_perf, sandbox_db, todo_db) to Postgres by hand.
• Cutover: cut one app over to Postgres with a measured write pause.
• Cloud: optionally, a cloud Postgres target.