Synchronizing MySQL Data to Elasticsearch: Methods and Practices

In the previous article we introduced the basic concepts of Elasticsearch. To make Elasticsearch useful, data must first be stored, and in many real‑world scenarios the data in Elasticsearch is kept synchronized with a MySQL database.

1. Direct synchronization from the business layer

The most common method is to add hooks in the ORM or other data‑access code and perform Elasticsearch operations inside those hooks. This keeps the ES logic scattered across the business code, which can hinder scalability and management. Some systems introduce a dedicated data‑proxy layer to centralize data operations, but the principle remains the same.

2. Independent synchronization

This approach separates the ES synchronization logic from the business layer, allowing the business code to focus solely on queries. After a successful write to MySQL, data is copied to Elasticsearch. The independent sync can be implemented in two main ways:

2.1 Plugin‑based

Third‑party plugins handle the data transfer, but flexibility is limited by the plugin’s capabilities. Commonly used plugins include:

logstash‑input‑jdbc

go‑mysql‑elasticsearch

2.2 Script‑based

Custom scripts provide greater flexibility. A simple method is to poll MySQL periodically, checking a “last_update” timestamp column to determine which rows need to be added, updated, or deleted in Elasticsearch. Deletions often require separate handling.

For real‑time synchronization, subscribing to MySQL binlog events is recommended. In Node.js the zongji library is a popular choice. By filtering binlog events for specific databases and tables, you can obtain four key dimensions for each change: database name, table name, operation type (insert, update, delete), and the affected data.

The demo shown filters out unrelated changes and returns only the relevant four‑dimensional information. This example is a proof‑of‑concept; interested readers can try it themselves.

End of article.