# Sync MySQL to Elasticsearch **URL:** \ **Category:** Logstash\ **Created:** [September 16, 2016, 12:10pm UTC](https://discuss.elastic.co/t/sync-mysql-to-elasticsearch/60705 "2016-09-16T12:10:00Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![adrianolimit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adrianolimit/32/11930_2.png) [@adrianolimit](https://discuss.elastic.co/u/adrianolimit)\ **Post date:** [September 16, 2016, 12:10pm UTC](https://discuss.elastic.co/t/sync-mysql-to-elasticsearch/60705/1 "2016-09-16T12:10:00Z")
Hello everyone, In order to synchronise my MySQL database with Elasticsearch I set up the following process : 1. I activated the binary log in MySQL in order to see the different transactions 2. After that, I execute Logstash which read the different transactions that are stored in a file. The file that Logstash read looks like this : > ``` > INSERT INTO `mytable` (`field1`, `field2`, `field3`) VALUES (1, 'first value', 'second value') > UPDATE `mytable` SET `field1` = 'first value modified', `field2` = 'second value modified' WHERE `mytable`.`id` = 1 > DELETE FROM `mytable` WHERE `mytable`.`id` = 1 > > ``` I used the grok filter to check if the transaction is "INSERT", "UPDATE" or "DELETE" and after that I try to apply another filter to get the field's names and their values. The problem is that the structure often changes, for example I could have : > ``` > UPDATE `mytable` SET `field1` = 'first value modified', `field2` = 'second value modified' WHERE `mytable`.`id` = 1 > UPDATE `mytable` SET `field1` = 'first value modified' WHERE `mytable`.`id` = 1 > > ``` Here, you can notice that in the first update there is two fields that are updated and in the second line only one. Do you know how can I solve my issue ? Do you think it's a good idea to process like this or do you have another suggestions? Thanks in advance for your help. ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [September 16, 2016, 12:44pm UTC](https://discuss.elastic.co/t/sync-mysql-to-elasticsearch/60705/2 "2016-09-16T12:44:44Z")
Any particular reason you're not using the jdbc input plugin? ---
**Author:** ![adrianolimit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adrianolimit/32/11930_2.png) [@adrianolimit](https://discuss.elastic.co/u/adrianolimit)\ **Post date:** [September 16, 2016, 1:12pm UTC](https://discuss.elastic.co/t/sync-mysql-to-elasticsearch/60705/3 "2016-09-16T13:12:20Z")
@magnusbaeck I don't use the jdbc input plugin because (if I well understand), it will reindex all my data. For example if I have 2 millions of lines in my table, I have to do : SELECT \* FROM .... in order to see the DELETE operations for example. ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [September 16, 2016, 1:40pm UTC](https://discuss.elastic.co/t/sync-mysql-to-elasticsearch/60705/4 "2016-09-16T13:40:55Z")
Is there a way to tell old data from new data? Like an auto-incrementing id field (if rows aren't updated) or a timestamp that changes when a row is updated. ---
**Author:** ![adrianolimit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adrianolimit/32/11930_2.png) [@adrianolimit](https://discuss.elastic.co/u/adrianolimit)\ **Post date:** [September 16, 2016, 9:53pm UTC](https://discuss.elastic.co/t/sync-mysql-to-elasticsearch/60705/5 "2016-09-16T21:53:53Z")
I'm not sure that is possible to know which data are been deleted because the "id" field is not an auto-increment but a specific id like this "d44fdkd\_s5sd4". Do you have any idea how can I sync my database with my elasticsearch? ---
**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\ **Post date:** [September 17, 2016, 5:38pm UTC](https://discuss.elastic.co/t/sync-mysql-to-elasticsearch/60705/6 "2016-09-17T17:38:00Z")
Okay, you also have deletions. That clearly makes things more difficult. I guess I don't have any great suggestions then. ---
**Author:** ![adrianolimit](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/adrianolimit/32/11930_2.png) [@adrianolimit](https://discuss.elastic.co/u/adrianolimit)\ **Post date:** [September 18, 2016, 11:25am UTC](https://discuss.elastic.co/t/sync-mysql-to-elasticsearch/60705/7 "2016-09-18T11:25:37Z")
Ok ,thank you @magnusbaeck for your suggestions, I will try with these options : - jdbc input plugin - trigger in the database in order to execute command to Elasticsearch for each transaction - execute transaction in the database and in Elasticsearch at the same time. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 4:38am UTC](https://discuss.elastic.co/t/sync-mysql-to-elasticsearch/60705/8 "2017-07-06T04:38:04Z")