Sharding, Data Heterogeneity, and Real-Time Synchronization with MySQL Binlog, Canal, and MQ

In the Internet era, besides rapid business iteration, data growth is also fast; the era of a single application, single instance, and single database is long gone, and technical R&D teams that have not used sharding cannot claim experience with large projects.

Taking e‑commerce as an example, the system has buyer and seller roles. When the site scales, a single order table cannot satisfy demand, making sharding inevitable, and because there are two dimensions (buyer and seller), a single sharding key cannot meet business requirements.

The conventional solution is to trade space for time: with decreasing storage costs, we consider data replication and heterogeneous processing, synchronizing a copy of data to the seller's order database and using the seller UID as the sharding key for queries.

Data heterogeneity has two approaches:

1. Dual‑write mode when inserting into the order table: after creating a record in the buyer table, also create a record in the seller table, possibly using different sharding keys and writing to different shards. The extra write increases the sync interface response time, affecting overall QPS.

Some may suggest an asynchronous mode: initialize a thread pool at startup, encapsulate sync logic as tasks, and let the pool execute them asynchronously. However, local tasks are hard to monitor, and a restart during deployment can cause data loss.

2. Use a message queue (MQ): after writing to the buyer database, send a transactional message so the interface can finish the response; the seller database operation is handled asynchronously by a message task, with the MQ framework’s retry mechanism handling failures.

Drawback: when a business requires heterogeneous processing, all business actions must encapsulate MQ messages, leading to tight coupling with business logic and reduced genericity.

3. A more generic solution is to build real‑time data sync using binlog, for which Alibaba’s open‑source framework Canal can be used.

MySQL master‑slave replication principle:

MySQL master writes data changes to the binary log (binlog events), viewable via show binlog events.

MySQL slave copies the master’s binlog events to its relay log.

MySQL slave replays the relay log events to reflect the changes in its own data.

Canal disguises itself as a MySQL slave, receiving binlog in real time and writing to database tables. To support many downstream databases, the binlog data from Canal cannot be written directly to all of them; the write throughput would be insufficient, and each downstream may need data transformation or filtering. Therefore, an MQ is added to decouple upstream and downstream.

After Canal parses the binlog into structured data, it writes it to an MQ order‑binlog topic. Each business that needs order data subscribes to this topic, consumes the parsed binlog, and can either write directly to its database or perform transformation, filtering, or computation before persisting, providing flexibility.

Note: because asynchronous messaging is used, data sync may have some latency, generally at the millisecond level, but monitoring message backlog is necessary to avoid large delays under special circumstances, which could affect normal business.

Real‑time data sync considerations:

During e‑commerce peak periods, system concurrency is high, database writes are frequent, and binlog traffic is large. The sync program consuming MQ messages can become a performance bottleneck, affecting real‑time sync.

To address this, we can increase the number of sync program instances or threads to boost concurrency. However, MySQL master‑slave binlog sync is single‑threaded to preserve order for data consistency. The MQ topic must have only one partition (queue) to ensure ordered processing, so target database writes remain correct.

Because single‑threaded processing cannot keep up, we consider business‑aware solutions. For example, in an e‑commerce order database, concurrent binlog execution only conflicts on the same record; if the order’s binlog execution order is wrong, the synchronized order data will be incorrect, but different rows’ order does not affect consistency.

Side note: data with causal relationships must strictly preserve order; data without causal relationships can be unordered.

Based on this theory, we evaluate downstream sync program capacity, calculate concurrency, and set the number of MQ topic partitions equal to the concurrency. Since MQ guarantees order within a partition, we need to route binlogs with causal relationships to the same partition. For order databases, binlogs of the same order ID must go to the same partition.

For quick Canal usage, refer to the article: Canal Kafka/RocketMQ QuickStart

Recommended reading:

In‑Depth Analysis of Coupon Core Architecture Design

MySQL SQL Mode and Its Functions

Common MQ Interview Questions