MySQL to Elasticsearch Data Synchronization Strategies and Tools

Overview

In many projects MySQL serves as the core business database, but as data volume and query complexity grow, relying solely on MySQL for efficient retrieval becomes difficult. Introducing Elasticsearch (ES) as a dedicated query engine can greatly improve search performance, flexibility, and scalability, making data synchronization between MySQL and ES a critical task.

Synchronization Schemes

1. Synchronous Double Write

Goal

Write data to both MySQL and ES simultaneously to ensure consistency and offload complex queries to ES.

Implementation

Direct Sync : Business code writes to MySQL and ES together, simple but adds code complexity.

Middleware : Use message queues (Kafka), CDC tools (Debezium), or ETL tools (Logstash) to capture MySQL changes and forward them to ES, decoupling business logic.

Triggers & Stored Procedures : MySQL triggers invoke ES writes, reducing code intrusion but increasing MySQL load.

Pros & Cons

Pros

Simple business logic

High real‑time query capability

Cons

Hard‑coded writes everywhere

Strong coupling with business code

Risk of data loss on double‑write failure

Performance degradation due to extra write

Use Cases

Suitable for scenarios requiring strong data consistency and high query performance, such as e‑commerce product and order data.

2. Asynchronous Double Write

This strategy writes to MySQL first and asynchronously propagates changes to ES, reducing write latency and improving system performance.

Pros & Cons

Pros

Higher system availability

Reduced primary DB write latency

Easy to add more downstream data sources

Cons

Hard‑coded consumer code for new sources

Increased system complexity due to message middleware

Potential delay in data visibility

Temporary inconsistency between source and target

Use Cases

Ideal for scenarios where absolute consistency is not critical but performance is, e.g., syncing order data to MySQL while sending browsing logs to ES for analytics.

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 move data from MySQL to ES without modifying application code.

Pros & Cons

Pros

No code changes, non‑intrusive

No strong coupling, preserves original performance

Cons

Lower timeliness due to scheduled polling

Adds polling load on the database

Cannot sync deletions automatically

Requires matching _id between ES and MySQL

4. Binlog Real‑Time Synchronization

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

Advantages

Real‑time capture and sync

Strong data consistency

Flexibility across different targets

Scalable and extensible

No code intrusion

Disadvantages

Configuration and maintenance complexity

Potential performance impact on high‑concurrency workloads

Dependency on binlog configuration and version

5. Canal Data Synchronization

Canal mimics a MySQL slave to subscribe to binlog, parses it into JSON, and forwards it to ES via TCP or MQ, achieving millisecond‑level latency.

Sync Flow

Canal server requests dump protocol from MySQL master.

Master pushes binlog to Canal, which converts it to JSON.

Canal client consumes the data and writes to ES.

6. Alibaba Cloud Data Transmission Service (DTS)

DTS provides real‑time data flow between heterogeneous data sources, supporting both migration and continuous sync. It offers high availability, dynamic endpoint adaptation, and a two‑stage sync process (initial load + real‑time changes).

Architecture Features

High availability with active‑standby modules

Dynamic adaptation to source address changes

Sync Process

Initialization : Collect incremental data, load schema and existing data.

Real‑time Sync : Continuously replicate ongoing changes.

DTS Serverless

Serverless instances automatically adjust resources (CPU, memory, RPS) based on load, avoiding over‑provisioning and reducing cost.