How to Sync MySQL Data to Elasticsearch: Strategies, Pros, and Pitfalls

Overview

MySQL is often the primary transactional store, but as data volume and query complexity increase, using MySQL alone for fast retrieval becomes a bottleneck. Elasticsearch (ES) is introduced as a dedicated search engine to provide low‑latency full‑text and analytics queries. Keeping MySQL and ES synchronized is essential for data freshness.

Synchronization Strategies

1. Synchronous Dual‑Write

Writes to MySQL are duplicated to ES within the same transaction, guaranteeing immediate consistency.

Implementation options

Direct write in application code.

Middleware (Kafka, Debezium, Logstash) that captures change events and forwards them to ES.

MySQL triggers or stored procedures that invoke ES indexing.

Pros

Simple business logic.

Real‑time query freshness.

Cons

Code coupling and hard‑coded writes.

Potential data loss if either write fails.

Additional write latency.

2. Asynchronous Dual‑Write

Changes are captured from MySQL and propagated to ES asynchronously, reducing write latency for the primary database.

Pros

Higher availability; failures in the sync path do not affect primary writes.

Lower write latency.

Easy to add additional downstream stores.

Cons

Increased system complexity due to message middleware.

Eventual consistency; data may appear in ES with delay.

Requires safeguards for data loss.

3. Logstash Synchronization

Logstash acts as a data pipeline that periodically polls MySQL, transforms rows, and indexes them into ES.

Pros

No code changes; non‑intrusive.

Loose coupling; no impact on application performance.

Cons

Polling introduces latency (seconds to minutes).

Polling load on MySQL; can be mitigated with a read replica.

Deletes are not automatically propagated; manual cleanup required.

Document _id in ES must match MySQL primary key.

4. Binlog Real‑Time Sync

MySQL binary log (Binlog) records all data‑changing statements. Tools such as Canal or Maxwell read Binlog events and push them to ES in near real time.

Pros

Real‑time capture.

Strong consistency between source and target.

Supports many destination systems.

No application code changes.

Cons

Configuration and maintenance complexity.

Potential performance impact on MySQL under high concurrency.

Tooling depends on Binlog availability; version changes may require re‑setup.

5. Canal Data Sync

Canal, an open‑source project from Alibaba, pretends to be a MySQL slave, subscribes to Binlog, converts events to JSON, and writes them to ES via REST API.

Canal server requests a dump from the MySQL master.

Master streams Binlog to Canal; Canal parses binary data into JSON.

Canal client consumes JSON (TCP or MQ) and indexes documents into ES.

6. Alibaba Cloud Data Transmission Service (DTS)

DTS is a managed real‑time data integration service that can synchronize MySQL to ES.

Key features

High availability with active‑standby modules and automatic failover.

Dynamic source address adaptation for endpoint changes.

Sync workflow

Initialization: DTS captures incremental changes while loading full data and schema into the target.

Real‑time sync: Ongoing changes are continuously replicated to keep source and target in sync.