Migration of data from Elasticsearch to Databases (MongoDB, MySQL)

[Andy_Cong]

Brief Principle: The real-time incremental data is written to both the old and new storage engines (ES and DB). Then data from the old storage engine (ES) is migrated to the new storage engine (DB). After that finish, data consistency is verified. Finally, read data are switched to the new storage system(DB).

This is my first time performing a data migration, so I would appreciate any reviews, suggestions, or references to mature solutions.

Steps:

1. Create Database Tables : Based on the schema of ES, create tables in MongoDB and MySQL respectively.
2. Dual Write Incremental Data to ES and DB : Write the real-time incremental data to both ES and DB simultaneously.
3. Migrate Historical Data from ES to DB : Write all data from ES to DB using the Logstash tool.
4. Data Verification : Traverse all data in ES, ensuring that each data can be found in DB. If the data is inconsistent, modify the data in DB according to the data in ES.
5. Switch Query Engine : Switch the query engine from ES to DB.
6. Monitor Data Read/Write : Check if the read/write operations are normal, especially whether the read performance meets the requirements.
7. Stop Dual Write and remove ES Cluster : Stop writing incremental data to ES and remove the ES cluster.

[jessgarson]

Thanks for reaching out, @Andy_Cong. Here are a few resources that may be helpful to take a look at:

• MongoDB native connector tutorial | Enterprise Search documentation [8.14] | Elastic
• Elastic Microsoft SQL connector reference | Enterprise Search documentation [8.14] | Elastic
• Elastic MongoDB connector reference | Enterprise Search documentation [8.14] | Elastic

[Christian_Dahlqvist]

@Andy_Cong

Dual Write Incremental Data to ES and DB : Write the real-time incremental data to both ES and DB simultaneously.

Relatively easy if you are only ingesting new immutable data. If you need to deal with deletes and updates it gets more complicated.

@Andy_Cong

Migrate Historical Data from ES to DB : Write all data from ES to DB using the Logstash tool.

Logstash was designed to move data into Elasticsearch. I do not think it is a good chioce when replicating or moving data in the other direction. You probably need to develop scripts or an application to do this as the data likely need to be transformed as well.

@Andy_Cong

Data Verification : Traverse all data in ES, ensuring that each data can be found in DB. If the data is inconsistent, modify the data in DB according to the data in ES.

If data is constantly being modified this can be tricky to do without downtime.

[Andy_Cong]

Very nice！ Thank you ！！

[Andy_Cong]

@Christian_Dahlqvist

Relatively easy if you are only ingesting new immutable data. If you need to deal with deletes and updates it gets more complicated.

You are right, very complicated！The real-time incremental data need update。

[Andy_Cong]

@Christian_Dahlqvist

Logstash was designed to move data into Elasticsearch. I do not think it is a good chioce when replicating or moving data in the other direction. You probably need to develop scripts or an application to do this as the data likely need to be transformed as well.

Good suggestion, I'm going to write a script to migrate history data.

[Andy_Cong]

@Christian_Dahlqvist

If data is constantly being modified this can be tricky to do without downtime.

Yes, very careful in doing this, so we need experienced people to review the solution and give some idea.

[Andy_Cong]

Thanks for you suggestions!! I will consider your suggestions。