How MySync Achieves Real-Time MySQL Binlog Synchronization Across Heterogeneous Stores

Background of MySync

MySQL is essential for daily business development. Many scenarios require syncing MySQL data to various storage systems such as Redis, ElasticSearch, and HBase for high‑frequency queries and full‑text search, which MySQL alone cannot efficiently provide.

Implementing multi‑storage writes manually leads to duplicated DML logic, schema‑change propagation, and consistency challenges, especially in distributed environments.

MySync was created as a generic component that, through simple configuration, can real‑time sync MySQL data to heterogeneous stores, greatly improving development efficiency.

Architecture of MySync

When a business writes data to MySQL, the Binlog is read by a Binlog Reader, transformed into messages, and pushed to a message queue. Writer modules (e.g., Redis Writer) consume these messages and write them to target stores. A Dump Tool can be used for initial data loading.

The system is modular: common configuration, logging, reporting, and messaging modules; checkpoint, high‑availability, and producer modules for the Reader; consumer, message, and storage‑write modules for the Writer.

Reader Component

The Reader focuses on Table_map_event and Row_event from row‑mode Binlog, generating Base messages that are serialized and sent to the message queue via ModProducer. ModCheckpoint records the latest Binlog position for resume capability. ModHA provides optional leader election for high availability.

Writer Component

The Writer consumes messages from the queue, deserializes them, and routes them to appropriate storage modules (ModWriter) based on configuration. It ensures ordered, successful writes to maintain strong consistency between MySQL and caches. Existing implementations support ElasticSearch and Redis, with more stores planned.

Message Queue Selection

MySync relies on a high‑availability, scalable message queue that can provide exactly‑once semantics. Kafka is chosen for its log compaction, high throughput, partitioned scalability, and ability to retain data indefinitely, enabling seamless replay for new services.

Production Guarantees

To achieve exactly‑once delivery, MySync buffers Binlog messages with sequence numbers, retransmits on failure, and only advances the cursor after receiving acknowledgments in order.

Consumption Guarantees

Writer modules process messages strictly in order, periodically committing offsets. If a failure occurs, the offset is not committed, triggering alerts for lag and allowing manual intervention.

Kafka Configuration

Key settings include request.required.acks=-1 for durable writes, disabling auto‑commit on consumers, setting enable.auto.commit=false, using auto.offset.reset=earliest, and configuring broker parameters unclean.leader.election.enable=false and min.insync.replicas=2 to enforce consistency.

Handling MySQL Master‑Slave Switch

When a master‑slave failover occurs, MySync uses GTID (if available) to locate the correct Binlog position. For non‑GTID environments, it creates a special table __db_mysync.t_binlog_record that records incremental anchors, allowing the system to resume reading from the appropriate Binlog file and position on the new master.

Current Usage

MySync is an award‑winning open‑source project used by many Tencent Qidian services, synchronizing tens of thousands of MySQL tables to various caches for years with proven data consistency and reduced development effort.