Elastic MySQL connector

edit

Elastic MySQL connector

edit

The Elastic MySQL connector is a native connector for MySQL data sources.

Availability and prerequisites

edit

The MySQL connector is available in Elastic versions 8.5.0+.

Currently, this connector is available in technical preview. Features in technical preview are subject to change and are not covered by the service level agreement (SLA) of features that have reached general availability (GA).

To use this connector, you must satisfy all prerequisites for native connectors. There are no additional prerequisites unique to this connector.

Usage and troubleshooting

edit

To connect to a MySQL data source, you will need the information described in MySQL configuration. And the data source must satisfy the compatibility requirements described in MySQL compatibility.

You may want to review the known issues for this connector.

Otherwise, there are no instructions unique to this connector. Review the following documentation for native connectors:

Known issues

edit

The MySQL connector has the following known issues:

  • Users should have only one MySQL connector index per deployment.

    Using multiple MySQL connector indices can result in non-deterministic data.

    This issue was discovered in Enterprise Search 8.5.0. Fixed in 8.5.1.

  • Using the MySQL connector to index large datasets (greater than 10 thousand rows per database) can cause memory issues and fail to index all documents.

    This issue was discovered in Enterprise Search 8.5.0. Fixed in 8.5.1.

Reference

edit

The following sections describe technical details for this connector.

MySQL compatibility

edit

The MySQL connector is compatible with MySQL 5.6+.

The connector is also compatible with MariaDB databases compatible with the above.

The MySQL data source and your Elastic deployment must be able to communicate with each other over a network.

MySQL configuration

edit

When configuring an index for use with this connector, you will need to provide some or all of the following information about the data source.

MySQL host

The IP address or domain name of the host. Exclude port. Examples:

  • 192.158.1.38
  • localhost
MySQL port

The port of the host. Examples:

  • 3306
  • 3307
MySQL username

The MySQL username the connector will use.

The user must have access to the configured databases. You may want to create a dedicated, read-only user for each connector.

See Security for details.

MySQL password

The MySQL password the connector will use.

See Security for details.

List of MySQL databases

The MySQL databases to sync. One or more database names, separated by commas. Each database must be accessible using the configured username and password.

See Security for details.

Examples:

  • products,categories
  • orders

Elasticsearch documents

edit

All records in the MySQL databases included in your connector configuration are extracted and transformed into documents in your Elasticsearch index.

  • For each row in your MySQL database table, the connector creates one Elasticsearch document.
  • For each column, the connector transforms the column into an Elasticsearch field.
  • Elasticsearch dynamically maps MySQL data types to Elasticsearch data types.
  • Field values that represent other records are replaced with the primary key for that record (composite primary keys are joined with _).

As soon as your first documents are synced, you can view the documents and inspect the mapping for the index. This will help you understand how your data is structured in Elasticsearch, and how this differs from the structure of the original MySQL records.

Syncing

edit

Users can launch a sync manually or schedule a recurring sync.

When you select the Sync button, the sync job has to wait until the connector picks up the task.

Each sync is a "full" sync.

For each MySQL row discovered:

  • If it does not exist, the document is created in Elasticsearch.
  • If it already exists in Elasticsearch, the Elasticsearch document is replaced and the version is incremented. See View documents.
  • If an existing Elasticsearch document no longer exists in the MySQL table, it is deleted from Elasticsearch.