Synchronize MySQL data to Elasticsearch

Use JDBC polling when periodic refreshes are sufficient and the source table has a reliable change marker. Use binlog-based change data capture (CDC) when you need a stream of inserts, updates, and deletes. In either design, Elasticsearch is a derived search view; decide how to repair it when synchronization falls behind.

Poll with Logstash JDBC

This illustrative pipeline tracks an updated_at column and uses the product primary key as the Elasticsearch document ID:

input {
  jdbc {
    jdbc_connection_string => "jdbc:mysql://mysql:3306/shop"
    jdbc_user => "reader"
    jdbc_password => "${MYSQL_READER_PASSWORD}"
    jdbc_driver_library => "/opt/mysql-connector-j.jar"
    jdbc_driver_class => "com.mysql.cj.jdbc.Driver"
    statement => "SELECT * FROM products WHERE updated_at >= :sql_last_value ORDER BY updated_at ASC, id ASC"
    schedule => "*/1 * * * *"
    use_column_value => true
    tracking_column => "updated_at"
    tracking_column_type => "timestamp"
    last_run_metadata_path => "/var/lib/logstash/products-jdbc.yml"
  }
}
output {
  elasticsearch {
    hosts => ["https://es.example.com:9200"]
    index => "products"
    document_id => "%{[id]}"
  }
}

Supply database and Elasticsearch security settings appropriate to the deployment. Give each independent JDBC input its own persistent checkpoint path. use_column_value => true makes the checkpoint follow the selected tracking column; merely naming the column is insufficient.

The inclusive boundary rereads rows at the latest timestamp, while stable document IDs make those writes replace the same documents. This reduces one timestamp-boundary problem but does not guarantee consistency: late commits, backdated timestamps, clock behavior, and concurrent updates still require a designed overlap/reconciliation strategy or CDC. Ensure every relevant update advances the marker. Stable IDs prevent duplicate documents, but do not prevent a delayed older write from replacing newer data. Preserve per-document ordering or use an appropriate versioning policy, and reconcile the search index with the source.

Stream binlog changes

Debezium or Canal can capture MySQL changes; a downstream pipeline or connector must translate those changes into Elasticsearch writes and deletes. Capturing a DELETE event does not automatically delete the corresponding search document. Plan initial snapshots, offsets, binlog retention, retries, ordering, and recovery. Latency depends on the whole pipeline, not a universal seconds-level guarantee.

Managed CDC platforms can provide the same building blocks with less infrastructure to operate, but their delivery and deletion semantics still need verification.

Avoid fragile dual writes

Writing MySQL and Elasticsearch from application code looks simple, but the two writes are not one transaction. Retries alone do not resolve every failure window. A transactional outbox with a retryable consumer is a more explicit way to record changes durably before updating the search view.

Polling cannot observe a row that has been physically deleted. Use a soft-delete marker and an explicit downstream deletion policy, or CDC. For initialization, coordinate a consistent full load with the incremental checkpoint; removing a WHERE clause and switching later can miss concurrent changes.

Reference: Logstash JDBC input.