Bridging MySQL and BigQuery: Why CDC Trumps Periodic Syncing

Most data pipelines moving information from MySQL to a data warehouse follow a predictable, yet flawed, pattern: a scheduled task executes a SELECT query, compares the results to the existing destination data, and uploads the delta.

The Blind Spots of Periodic Syncs

A sync based on SELECT statements only captures a snapshot of the current state. This approach has several critical failures:

1. Invisible Deletions: If a row is deleted between two sync cycles, the system has no way of knowing it ever existed.
2. Lost Transitions: If a record changes multiple times (e.g., Pending $\rightarrow$ Processing $\rightarrow$ Shipped) between runs, the intermediate states are erased.
3. Resource Exhaustion: Scanning massive production tables repeatedly just to find a few changes creates unnecessary overhead.

"Just run the sync more often" is not a solution. Even a sync running every minute is inferior to a system that captures the actual stream of events.

Comparison: Batch Sync vs. CDC

---

How CDC Solves the Problem

Change Data Capture (CDC) leverages MySQL's binary log (binlog)—the same engine used for internal replication. Instead of asking the database "what does the data look like now?", CDC asks "what has changed since the last event?".

Every INSERT, UPDATE, and DELETE is recorded sequentially. This ensures that the destination reflects the exact history of the source.

The Mathematical Reality of Data Loss

In a periodic sync, the amount of missed information ($\Delta L$) can be expressed as: $\Delta L = \sum (\text{Deleted Rows}) + \sum (\text{Intermediate State Changes})$ With CDC, $\Delta L \approx 0$.

---

MySQL Configuration Requirements

CDC isn't "plug and play"; it requires specific server-side settings to function reliably.

📋 Setup Checklist

• Binary Logging: Must be enabled.
• Binlog Format: Must be set to ROW.
• Row Image: Must be set to FULL.
• User Permissions: Replication and Select privileges granted.
• Server ID: A unique identifier assigned to the client.

[!WARNING] If binlog_row_image is not set to FULL, UPDATE and DELETE events will only contain the modified columns. For JSON columns, this means only the specific changed fragment is logged, not the entire object, leading to corrupted data in BigQuery.

Permissions and Connectivity

The replication user requires a specific set of privileges to monitor the logs and perform the initial data load:

• REPLICATION SLAVE & REPLICATION CLIENT (for log access)
• SELECT, RELOAD, and SHOW DATABASES (for the initial snapshot)

Additionally, every client must have a unique server-id. If two clients share an ID, you will encounter silent failures that are notoriously difficult to troubleshoot.

Note on Retention: MySQL purges binlogs after a set period (usually 30 days). If your pipeline stays offline longer than this window, it cannot resume and must be restarted.

---

Implementation Guide

To configure your environment, execute the following SQL commands:

-- Grant necessary permissions to the CDC user
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT 
ON *.* TO 'your_user'@'%'; 
FLUSH PRIVILEGES;

-- Verify current binlog settings
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';

If the variables are incorrect, modify your my.cnf file and restart MySQL to apply the changes.

---

Landing Data in BigQuery

Once the stream is active, the destination logic is straightforward: each binlog event is mapped to a corresponding operation in BigQuery.

The priority here is integrity over velocity. It is far better to have a pipeline that is slightly delayed but 100% accurate than one that delivers incomplete data instantly.

The Erathos Advantage

Building this from scratch is complex. Tools like Erathos automate the heavy lifting:

• Snapshot Management: Handles the transition from full initial load to binlog streaming.
• Collision Avoidance: Manages server-id assignments automatically.
• Recovery Logic: Handles the "purged binlog" scenario so you don't have to manually rebuild the pipeline.

If you are still relying on full-table batch syncs, stop asking how to make them faster and start asking how to make them accurate. You can test this by creating an Erathos account and enabling CDC for your MySQL source in minutes.

For deeper technical details, visit the official connector documentation.