Connecting Opensearch with MySQL. Is it possible?

OpenSearch
1

I need to establish a connection between Opensearch and a data source like MySQL to enable the recreation of indexes in Opensearch using SQL queries to a database, similar to how SOLR does it.

Is it feasible to accomplish this task? Currently, I’m unable to find information that sheds light on this topic, and my understanding is that this functionality might not be viable.

Tx!

2

Hello @Teo - welcome to the OpenSearch Community. Interesting question - @anirudha @yuryf-bq - would you or your team have any insight to share on this question?

3

Hi!

I think it is possible with using third party software. You need a data lake application which will be connected to OpenSearch and to MySQL as different data sources. Then, you can start data migration. Probably, Dremio could do this for you.
As another option, you can make a simple java/python/C#/etc application, which connects to OpenSearch SQL using JDBC or ODBC driver and exports all indices to csv with select * queries. Then you can create corresponding tables into MySQL and import data from CSV files.

Unfortunately, OpenSearch SQL has no option to export DML queries like mysqldump does.

4

Hello yuryf-bq,
Thank you for your response.

I have some experience working with SOLR before, primarily using it as an indexer rather than a data source; we utilized MySQL as our data source.

SOLR offers support for complete or incremental data imports, a feature that I find particularly valuable when there’s a need to recreate data and indexes.

This is the reason I inquired about the possibility of OpenSearch being able to establish a connection with MySQL, enabling direct data imports and avoiding intermediary processes.

Thank you very much.

5

I think you can add OpenSearch to Solr as a datasource using the JDBC driver.