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. Elasticsearch (ES) is introduced as a high‑performance search database to improve query speed and user experience.

Ensuring reliable data synchronization between MySQL and ES is critical for real‑time accuracy and system stability.

Synchronization Options

1. Synchronous Double‑Write

Writes to MySQL are simultaneously written to ES, guaranteeing data consistency and reducing read load on MySQL.

Implementation Methods

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

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

Triggers or stored procedures in MySQL to push changes to ES (less invasive but may affect MySQL performance).

Pros

Simple business logic.

High real‑time query performance.

Cons

Hard‑coded writes increase code complexity.

Strong coupling between services.

Risk of data loss if double‑write fails.

Potential performance degradation.

2. Asynchronous Double‑Write

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

Pros

Higher system availability.

Reduced primary‑database write latency.

Easy to add more downstream data sources.

Cons

Hard‑coded integration for each new data source.

Increased system complexity due to message middleware.

Potential delay in data visibility.

Temporary data inconsistency between source and target.

3. Logstash Synchronization

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

Pros

No code intrusion; non‑invasive.

No strong coupling; original program performance unchanged.

Cons

Lower timeliness due to scheduled polling.

Additional load on the database during polling.

Cannot sync deletions automatically; manual cleanup required.

ES document IDs must match MySQL IDs.

4. Binlog Real‑Time Synchronization

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

Pros

Real‑time capture and synchronization.

Ensures data consistency.

Supports many target systems.

Scalable and extensible.

No code changes required.

Cons

Configuration and maintenance can be complex.

Potential performance impact on MySQL under high concurrency.

Tooling depends on Binlog support; version changes may require reconfiguration.

5. Canal Data Synchronization

Canal, an open‑source Alibaba project, pretends to be a MySQL slave to subscribe to Binlog, converting binary logs to JSON and forwarding them to ES via TCP or MQ.

Typical workflow: Canal server requests dump from MySQL master → MySQL streams Binlog → Canal parses to JSON → Canal client pushes to ES.

6. Alibaba Data Transmission Service (DTS)

DTS provides real‑time data transmission across heterogeneous data sources, supporting both initial data load and continuous incremental sync.

Key features include high availability, dynamic source address adaptation, and serverless resource scaling based on load.

Typical Use Cases

E‑commerce systems syncing product or order data for complex search.

Scenarios requiring high consistency and query performance.

Overall, the choice of synchronization method depends on requirements for real‑time accuracy, system complexity, performance impact, and operational cost.