Apache Flink CDC Reveals Custom Optimization Case for Real-Time MySQL Integration

A data analytics platform organization shared a case study of building real-time integration (CDC) between MySQL databases using Apache Flink and Flink CDC to minimize service DB load. This approach resolved the large-scale snapshot delay issues of the existing Debezium-based method and customized security and stability features required for practical operations.

Step-wise Kafka Topic Separation and Restart Logic

Logic was implemented to separate Kafka topics to ensure parallel processing during the snapshot phase and order guarantee during the Binlog stream phase. While existing Flink Jobs could not change topics during execution, the adopted approach returns GTIDs via the Watchtower notification system upon snapshot completion and restarts with a Binlog-only topic.

• flink-connector-mysql-cdc modification: Added phase transition notification and GTID return logic to the onFinishedSplits and assignSplits functions
• Restart procedure: Stop the job after snapshot completion -> Start the Binlog stream phase with the returned GTIDs and the Binlog destination topic

DDL Event Detection and Security Response

To prevent sensitive data from being loaded without hashing when schema changes (DDL) occur in service tables, logic was applied to skip the detected DDL event and all subsequent events. The approach of stopping the job due to exceptions was rejected due to issues with message commit guarantees.

• Skip logic: Ignore all subsequent events when SchemaChangeEvent is detected in the handleQueryEvent function
• Reconnection support: Send GTID information of DDL events to the notification system to use as a reference point for reconnection

DB Switching Detection and Automatic Reconnection

Automatic reconnection logic was added to prevent switching to the primary server while connected via the MySQL replica server domain. The design prevents connection to the primary server and reconnects to the replica server domain.

• DNSIpChecker: Raises an exception and reconnects when DNS IP changes are detected in a primary-replica two-node configuration
• ReadOnlyChecker: Determines whether the connection is to the primary server by querying the read_only setting value in an InnoDB Cluster environment

Version and Compatibility

This case study is based on Apache Flink 1.17.1 and Flink CDC 2.4.1. Versions 3.1 and above of Flink CDC have improvements such as the ability to perform only the snapshot phase, but caution is required as version 3.x requires compatibility with Flink 1.18.