Setting up MySQL CDC for ELT & CDC
ELT & CDC uses binlog replication for MySQL database sources. The binary log records all changes to your database, and Integrate.io reads these log events to capture inserts, updates, and deletes in near real-time. This approach has minimal impact on your source database since it reads from the replication stream rather than querying tables directly. We recommend using a read-replica for syncing to eliminate any performance impact on your primary database instance.Supported providers
Select your provider above for platform-specific setup instructions covering read-replica creation, user configuration, and binary log settings.Requirements
- MySQL versions 5.7.x or above.
- Binary logging enabled with
binlog_formatset toROWandbinlog_row_imageset toFULL. - Tables with
PRIMARY KEY. - Superuser access for creating a sync user. The sync user itself only needs
SELECT,RELOAD,REPLICATION SLAVE,REPLICATION CLIENT, andLOCK TABLESprivileges. - Binlog retention set to a minimum of 1 day (86400 seconds). We recommend 7 days (604800 seconds) to allow time for recovery from any sync interruptions.
Features
Binlog retention
Binlog files must be retained long enough for Integrate.io to read changes since the last sync checkpoint. If binlog files are purged before the pipeline can read them, the pipeline will need to perform a full resync. The recommended retention settings depend on your provider:Connection monitoring and automatic recovery
MySQL CDC pipelines monitor the binlog connection for liveness. A source database restart, failover, or version upgrade can drop the binlog connection even while the database itself remains reachable. Integrate.io detects this instead of stalling silently:- If the binlog connection is lost, the pipeline restarts automatically and resumes from the last recorded binlog position. Changes made while the connection was down are delivered after the restart. The initial sync does not run again.
- If the connection goes silent without disconnecting, for example when the server freezes, a watchdog stops the pipeline after 5 minutes without any binlog traffic. MySQL sends replication heartbeats every few seconds even when no data changes, so an idle database or a quiet table does not trip the watchdog.
- The watchdog arms only after the source sends its first replication heartbeat. If your source never sends heartbeats, the watchdog stays disarmed and the pipeline logs a warning. Replication itself is unaffected.
Frequently Asked Questions (FAQs)
Do I need to use a read-replica?
Do I need to use a read-replica?
A read-replica is optional but recommended. Syncing from a replica eliminates any performance impact on your primary database. If you prefer to sync directly from the primary instance, that works too. The sync user only performs lightweight read operations.
What happens if binary logging is not enabled?
What happens if binary logging is not enabled?
Binary logging is required for CDC. Without it, Integrate.io cannot capture changes. You will need to enable it and restart your MySQL instance before setting up the pipeline. Each provider guide includes the specific steps.
What MySQL engines are supported?
What MySQL engines are supported?
InnoDB is the recommended and most commonly used engine. MyISAM tables can be replicated, but InnoDB provides better transaction support and crash recovery.
What happens if my source database restarts or is upgraded?
What happens if my source database restarts or is upgraded?
The pipeline detects the lost binlog connection and reports an error instead of continuing silently. It resumes from the last recorded binlog position, so changes made during the outage are delivered once replication restarts. The initial sync does not run again. Transient connection losses recover automatically. If the pipeline stays in an error state, restart it manually and it resumes from the same position.
Can I sync specific tables only?
Can I sync specific tables only?
Yes. When configuring your pipeline, you can select which tables to include. You can also exclude specific columns from synced tables.