Data Synchronization Strategies Between MySQL and Elasticsearch

Overview

In many projects MySQL serves as the core business database, but as data volume and query complexity grow, relying solely on MySQL for fast retrieval becomes a bottleneck. Introducing Elasticsearch (ES) as a dedicated query engine can greatly improve search performance, scalability, and user experience.

Effective synchronization between MySQL and ES is essential to ensure data consistency, timeliness, and system stability.

Synchronization Solutions

1. Synchronous Double‑Write

Writes are performed to MySQL and ES simultaneously, ensuring real‑time consistency and reducing read load on MySQL.

Implementation Methods

Direct write in business code (simple but tightly coupled).

Middleware such as Kafka, Logstash, or Debezium to capture changes and forward them to ES (decouples logic, improves scalability).

Triggers or stored procedures in MySQL to invoke ES writes (less invasive but may impact MySQL performance).

Pros

Simple business logic.

Real‑time query capability.

Cons

Hard‑coded dual writes increase code complexity.

High coupling between services.

Risk of data loss if one write fails.

Additional write overhead can degrade overall performance.

2. Asynchronous Double‑Write

Changes are written to MySQL first, then asynchronously propagated to ES, reducing write latency and improving system throughput.

Pros

Higher system availability.

Reduced write latency for the primary database.

Supports multiple downstream data stores.

Cons

Requires new consumer code for each added data source.

Increases system complexity with message queues.

Potential eventual consistency gaps.

3. Logstash Synchronization

Logstash is an open‑source data pipeline that can ingest data from multiple sources, transform it, and output to a 存储库 . It can be used to pull data from MySQL and push it to ES.

Pros

No code changes; non‑intrusive.

No strong coupling; preserves original performance.

Cons

Polling introduces latency; even with second‑level intervals there is delay.

Polling adds load to the database.

Does not handle delete synchronization automatically.

Requires matching IDs between MySQL and ES.

4. Binlog Real‑Time Synchronization

Binlog records all data‑changing statements in MySQL. Tools like Canal or Maxwell listen to binlog events and stream changes to ES in real time.

Pros

Real‑time capture.

Strong data consistency.

Flexibility across different targets.

Scalable and extensible.

No code intrusion.

Cons

Configuration and maintenance can be complex.

High write volume may affect MySQL performance.

Tooling depends on binlog availability; version changes may require reconfiguration.

5. Canal Data Synchronization

Canal, an open‑source Alibaba project, parses MySQL binlog, acts as a slave, and forwards changes to ES via RESTful APIs, providing millisecond‑level latency.

Workflow: Canal connects to MySQL master → receives dump protocol → parses binlog to JSON → client consumes via TCP or MQ → writes to ES.

6. Alibaba Cloud DTS (Data Transmission Service)

DTS offers a managed, high‑availability data transmission service supporting real‑time sync, migration, and subscription across heterogeneous data sources, including MySQL and ES.

Key Features

High availability with active‑standby modules.

Dynamic adaptation to source address changes.

Two‑stage sync: initialization (full load) and real‑time incremental sync.

DTS Serverless

Serverless instances automatically scale resources (CPU, memory, RPS) based on load, reducing waste and ensuring performance during traffic spikes.