How can I reach real-time index updating using ES + Logstash + MySQL when data was updated, added or deleted?

Question

I have MySQL DB with one table without joins with ~100 millions of rows. These items can be updated or removed and also new records are saved into MySQL DB periodically e.g. every minute. How can I reach updating indexes in Elasticsearch

when data is updated/changed/deleted in MySQL DB?

Actualizing data is must have for me it’s very important to stay up to date with MySQL. Is it possible? Thanks.

Also I have tried the solution with schedule => "* * * * *" and updated_at > :sql_last_value but it is very slow.

Answer 1

Not sure what do you mean by "very slow" , but if it's about frequency of sampling , then the expression : schedule => "/2 * * * * *" for instance, will execute the query you have defined in statement variable in the .config file every two seconds.

Answer 2

Thanks for the reply to all, I've found the solution for me thanks to Aaron Mildenstein who helped me on discuss.elastic.co. See the thread here.

The answer:

In order to achieve this, you will need a separate process that monitors binlog transactions and initiates updates to the Elasticsearch end of things. Elastic does not currently offer any tools that do this, so you’ll have to find one that someone open sourced, or write your own.

To parse binlogs I used this lib: https://github.com/siddontang/go-mysql-elasticsearch