Concepts
How MySQL to BigQuery CDC works
How change data capture works: consistent snapshot, binary log streaming, change history and refreshes, and how ordering and deletes are handled.
Change data capture (CDC) means copying a database's changes as they happen instead of re-copying whole tables. Quayen does it in three stages.
1. Initial copy
Quayen opens a consistent snapshot of the selected tables and records the binary log position at that moment. Every row is copied into BigQuery. Because the position is recorded, no change made during the copy is lost: streaming resumes exactly from it.
2. Streaming changes
MySQL's binary log records each committed insert, update and delete, the same mechanism replicas use. Quayen reads it continuously and writes each change, within seconds, to a change history table in BigQuery with the operation, its position in the log and the time it arrived. Progress is saved only after BigQuery has accepted a batch, so after any restart it resumes without gaps.
3. Refreshing the up-to-date tables
On the schedule you choose (every minute to every hour), Quayen applies new changes to an up-to-date copy of each table using a BigQuery MERGE: the latest change per primary key wins, updates overwrite, and deletes remove the row. Ordering uses the binary log position, so applying the same change twice, or in a different batch, can't produce a wrong result.
Schema changes
When a column is added in MySQL, it is added to both BigQuery tables automatically and fills in from the next change.
Fixing a table
- Re-copy a table reads it from MySQL again and rebuilds its BigQuery copy while other tables keep streaming. Changes made during the re-copy are kept.
- Change partitioning rebuilds the BigQuery copy with the new layout without reading MySQL again.
- If changes ever wait to be applied longer than the change-history retention, the table is re-copied automatically, so expired history can never leave a gap.
See table layout for the exact columns.