Learn
What is change data capture (CDC)?
Change data capture explained: how CDC works, log-based vs query-based CDC, common CDC tools, and how MySQL change data capture uses the binary log.
Change data capture (CDC) is a way of copying data by capturing each change (insert, update or delete) as it happens, instead of repeatedly re-copying whole tables. It keeps a destination such as BigQuery in sync with a source database with low delay and little load on the source.
Three ways to capture changes
- Full reloads. Copy the whole table on a schedule. Simple, but slow and expensive for large tables, and the copy is only as fresh as the last run.
- Query-based (polling). Repeatedly query for rows with a newer
updated_at. It misses deletes entirely, misses rows whose timestamp isn't updated, and puts query load on the database. - Log-based. Read the database's own transaction log, in MySQL the binary log. Every committed change is recorded in order, including deletes, and reading it barely affects the database. This is the method Quayen uses.
Why deletes matter
Polling can't see a row that no longer exists, so polled copies slowly fill with rows that were deleted in MySQL. Log-based CDC sees the delete event and removes the row from the BigQuery copy, while the change history still records that it existed.
Snapshot plus stream
A CDC pipeline starts with a consistent snapshot of existing rows, records the exact log position, then streams changes from that position, so nothing is lost or applied twice. See how it works.
How change data capture works, step by step
- The database writes every committed change to its transaction log, as it already does for replication and recovery.
- The CDC tool connects as a replica and reads that log from a known position.
- Each event (insert, update or delete, with the row's values) is turned into a change record and sent to the destination in commit order.
- The destination applies the changes: inserting new rows, updating existing ones by primary key and removing deleted ones. The tool saves its log position, so after a restart it carries on where it stopped.
What CDC is used for
- Analytics. Keeping a warehouse such as BigQuery, Snowflake or Redshift minutes or seconds behind production, without running heavy queries on the production database.
- Audit and history. Recording every version of every row, including deleted ones.
- Migrations. Moving a database with near-zero downtime by copying it once and streaming changes until cutover.
- Event-driven systems. Publishing database changes to queues such as Kafka or Pub/Sub so other services can react.
Change data capture in MySQL
MySQL records changes in its binary log. For CDC it needs binlog_format = ROW and binlog_row_image = FULL, so each event carries the complete row, and a retention period long enough to recover from an outage. See binlog_format and binlog_row_image explained and binary log retention. The binlog CDC checker tells you whether a server is ready.
CDC tools
Open-source Debezium streams database changes into Kafka. Managed services include Google Cloud Datastream, AWS DMS, Fivetran, Striim and Estuary. Quayen is a managed CDC service built only for MySQL to BigQuery. For a side-by-side comparison, see the best MySQL to BigQuery replication tools.