Mastering MySQL‑Elasticsearch Sync: Strategies, Pros, Cons, and Real‑World Use Cases

Overview

In project development and operations, MySQL often serves as the core business database, but as data volume and query complexity grow, relying solely on MySQL for efficient retrieval becomes difficult, especially for massive complex queries.

To alleviate this, read‑write separation is commonly used by introducing Elasticsearch (ES) as a dedicated query database. ES offers excellent search performance, flexible data models, and scalability, enabling fast retrieval and analysis.

Synchronizing data between MySQL and ES is critical for real‑time, accurate data and system stability.

Synchronization can be achieved via tools such as Logstash, Kafka Connect, Debezium, or scheduled jobs (Cron) combined with SQL queries and batch imports, considering real‑time needs, architecture complexity, operational cost, and incremental update characteristics.

Synchronization Schemes

1. Synchronous Dual‑Write

When MySQL data is modified, the same changes are written to ES simultaneously, ensuring consistency and improving read/write performance.

Goal

The aim is to replicate business data from MySQL to ES in real time, leveraging ES’s efficient query capabilities while relieving MySQL’s query load.

Implementation

Direct sync : Application code writes to both MySQL and ES in the same transaction. Simple but adds code complexity and risk.

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

Triggers & Stored Procedures : Define MySQL triggers or procedures to write to ES upon data changes, reducing code intrusion but potentially burdening MySQL.

Pros & Cons

Pros

Simple business logic

High real‑time query capability

Cons

Hard‑coded writes in every MySQL update

High coupling between code and databases

Risk of data loss if dual‑write fails

Additional write overhead can degrade performance

2. Asynchronous Dual‑Write

Data changes in MySQL are asynchronously propagated to ES, reducing write latency on the primary database and improving overall system performance.

Pros & Cons

Pros

Higher availability; backup failures don’t affect primary writes

Reduced primary write latency

Multiple data sources can be added independently

Cons

Hard‑coded integration for each new data source

Increased system complexity due to message middleware

Potential delay in data visibility because of asynchronous processing

Eventual consistency issues require additional measures

Use Cases

Suitable for scenarios where absolute consistency is not critical but performance is, e.g., syncing user browsing logs or click counts to ES for analytics while keeping order data in MySQL.

3. Logstash Sync

Logstash is an open‑source data‑processing pipeline that can ingest data from multiple sources, transform it, and output to a destination repository. It can be used to capture MySQL changes and push them to ES.

Pros & Cons

Pros

Non‑intrusive, no code changes required

No strong coupling, preserves original application performance

Cons

Lower timeliness; relies on scheduled polling, leading to latency

Adds polling load on the database

Cannot handle delete synchronization automatically

Requires ES document IDs to match MySQL IDs

4. Binlog Real‑Time Sync

Binlog (binary log) records all data‑changing SQL statements in MySQL. Real‑time sync tools (e.g., Canal, Maxwell) listen to binlog events, parse them, and replicate changes to ES or other targets.

Advantages

Real‑time capture

Data consistency between source and target

Flexibility across multiple databases

Scalability and extensibility

No code intrusion

Disadvantages

Configuration and maintenance complexity

Potential performance impact on high‑concurrency workloads

Dependency on binlog configuration; version changes may require re‑setup

5. Canal Sync

Canal, an open‑source Alibaba product, parses MySQL binlog as a slave, providing incremental data subscription. It streams changes to ES via RESTful APIs, suitable for high‑real‑time requirements.

Principle

Canal pretends to be a MySQL slave, receives binlog from the master, parses it into JSON, and forwards it to ES.

Workflow

Canal client connects to MySQL master using dump protocol.

Master pushes binlog; Canal parses to JSON.

Canal client consumes JSON via TCP or MQ and writes to ES.

6. Alibaba Cloud DTS

Data Transmission Service (DTS) provides real‑time data flow between heterogeneous data sources, supporting RDBMS, NoSQL, and OLAP. It offers high availability, dynamic source address adaptation, and both initialization and real‑time incremental sync.

Key Features

High availability with active‑standby modules

Dynamic adaptation to source address changes

Two‑phase sync: initial full load then real‑time incremental sync