MySQL to Elasticsearch

NineData Data Replication lets you replicate MySQL data to Elasticsearch, so Elasticsearch can index, search, and analyze operational data from MySQL.

Overview

Use this workflow to build and maintain Elasticsearch indexes from MySQL tables for search, aggregation, and analytics workloads.

Before you begin

• Add the source and target data sources to NineData. For details, see Add data source.

• The supported MySQL source versions range from 5.1 to 9.0.

• The target database type must be Elasticsearch 8.7, 7.0, 6.0, or 5.0.

• Enable Binlog for the source data source and set the following parameters:

  • binlog_format=ROW

  • binlog_row_image=FULL

    tip

    If the source data source is a standby database, enable log_slave_updates to capture the complete Binlog.

Procedure

1. Sign in to the NineData Console.

2. In the navigation menu, click Replication > Data Replication.

3. On the Replication page, click Create Replication in the page header.

4. On the Source & Target tab, configure the source and target, and then click Next.

   Parameter	Description
   Name	Enter a meaningful data replication task name for later search and management. Up to 64 characters are supported.
   Source	Select the MySQL data source that contains the data to replicate.
   Target	Select the Elasticsearch data source that receives the replicated data.
   Index Name	Select how NineData names Elasticsearch indexes after data is replicated from MySQL. TableName (default): Use the original MySQL table name. For example, an index named users stores documents that map to the source users table. DbNameTbName: Use `[source database name][source table name]. For example, an index named mydb_usersstores documents from theuserstable in themydb` database.
   Type	Select what to replicate to the target data source. Schema: Replicate the database and table schema from the source data source. Full: Replicate all selected source objects and data. This is full data replication. Incremental: After full replication completes, replicate incremental changes based on the source logs. Click the icon to clear operation types that you do not want to replicate during incremental synchronization. Cleared operations are ignored during incremental synchronization. Note: Expand Target Table Preparation to choose the handling strategy for same-name tables or existing data in the target.
   Target Table Preparation	If target table already exists (required when Schema is selected) Pre-Check Error and Stop Task: Stop the task if the precheck detects a table with the same name. Skip and Continue Task: If the precheck detects a table with the same name, continue the task and display a message. During schema replication, NineData skips that table. If data replication is also enabled, NineData appends data to the existing table without overwriting the original data. Delete Objects and Rewrite: If the precheck detects a table with the same name, continue the task and display a message. During schema replication, NineData deletes the table with the same name in the target database and copies the table schema from the source database again. If data replication is also enabled, NineData writes data after the schema is copied. Keep Schema and Clear Data, then Rewrite (available when schema and data are replicated together): If the precheck detects a table with the same name, continue the task and display a message. During schema replication, NineData keeps the target table schema. When data replication starts, NineData clears the data in the table with the same name and then copies data from the source table again. Target Table Exists Data (required when Schema is not selected) Pre-Check Error and Stop Task: Stop the task if the precheck detects existing data in the target table. Ignore existing target data and append to it.: If the precheck detects existing data in the target table, ignore that data and append new data. Clear target existing data before write: If the precheck detects existing data in the target table, delete the existing data before writing new data. Dynamic Desensitization (available only for MySQL to MySQL): If this option is enabled in a supported task, data from configured sensitive columns is replicated to the target in desensitized form.

5. On the Objects tab, configure the replication objects, and then click Next.

   Parameter	Description
   Replication Objects	Select the objects to replicate. Select Full Instance to replicate all objects in the source database, or select Customized Object, choose the objects in the Source Object list, and click > to move them to the Target Object list.

6. On the Mapping tab, configure the tables and fields to replicate to Elasticsearch. By default, NineData replicates all tables and fields in the selected database.

   Configure the DOC ID for each index if you need a custom Elasticsearch document identifier. A DOC ID is used to retrieve, update, or delete a document. Specify a DOC ID, or leave it empty so Elasticsearch can generate one.

   tip

   Click Mapping & Filtering to customize field names after synchronization to Elasticsearch. Set Data Filter so that only rows matching the filter condition are synchronized to Elasticsearch.

7. On the Pre-check tab, wait for NineData to complete the precheck. After the precheck passes, click Launch.

   tip

   • If the precheck fails, click Details in the Actions column for the failed item to view the cause. After fixing the issue, click Check Again to run the precheck again.
   • For items whose Result is Warning, fix the issue or ignore the warning when appropriate.

8. On the Launch page, the Launch Successfully message appears, and the replication task starts running. Use these actions to review the task:

   • Click View Details to view the execution status of each replication phase.
   • Click Back to list to return to the Replication task list.

Result

1. Sign in to the NineData Console.

2. In the navigation menu, click Replication > Data Replication.

3. On the Replication page, click the ID of the target replication task to open the Details page. The task detail page contains the following areas.

   

   No.	Area	Description
   1	Synchronization Delay	Shows the synchronization delay between the source and target. 0 seconds means the Elasticsearch target has caught up with the MySQL source.
   2	Configure Alerts	After alerts are configured, NineData notifies users when the task fails. For more information, see Introduction to operational monitoring.
   3	More	Pause: Pause a task whose status is Running. Terminate: Terminate a task that is not complete or is still being monitored, such as a task in incremental synchronization. A terminated task cannot be restarted. Proceed with caution. Delete: Delete the task. Deleted tasks cannot be restored. Proceed with caution.
   4	Schema Replication (shown when schema replication is included)	Shows schema replication progress and details. Click Log to view the schema replication execution log. Click to view the latest information.
   5	Full Replication (shown when full replication is included)	Shows full replication progress and details. Click Monitor to view full replication metrics. During full replication, click Flow Control Settings on the monitoring page to limit the write rate to the target data source. The unit is MB/s. Click Log to view the full replication execution log. Click to view the latest information.
   6	Incremental Replication (shown when incremental replication is included)	Shows incremental replication metrics. Click View Threads to view currently running operations for the replication task, including: Thread ID: The thread ID. A replication task can use multiple threads for replication operations. Execute SQL: Details of the SQL statement that the current thread is executing. Response Time: The response time of the current thread. If this value increases, the thread may be blocked. Event Time: The timestamp when the current thread started. Status: The current thread status. Click Flow Control Settings to limit the write rate to the target data source. The unit is rows per second. Click Log to view the incremental replication execution log. Click to view the latest information.
   7	Modify Object	Shows the modification history of replicated objects. Click Modify Objects to configure replication objects . Click to view the latest information.
   8	Expand	Shows task details, including Type, Replication Objects, and Started.

Appendix 1: MySQL to Elasticsearch data type mapping

During data replication, NineData maps MySQL data types to corresponding Elasticsearch data types.

Category	MySQL Data Type	Elasticsearch Data Type
Numeric	TINYINT	Short
	TINYINT UNSIGNED
	SMALLINT	Integer
	SMALLINT UNSIGNED
	MEDIUMINT
	MEDIUMINT UNSIGNED
	INT
	INT UNSIGNED
	BIGINT	Long
	BIGINT UNSIGNED
	BIT(M)	Uint64
	DECIMAL	Decimal(10, 0)
	NUMERIC
	DECIMAL(P, S)	Decimal(P, S)
	FLOAT	Float32
	DOUBLE	Float64
	BOOL, BOOLEAN	Boolean
DATE AND TIME	DATE	Date
	DATETIME[(fsp)]
	TIMESTAMP[(fsp)]
	TIME[(fsp)]
	YEAR[(4)]
STRING	CHAR/VARCHAR	Text
	TINYTEXT/TEXT/MEDIUMTEXT/LONGTEXT
	BINARY/VARBINARY	Binary
	TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB
	ENUM	Keyword
	SET
JSON	JSON	Object
SPATIAL	POINT	Geo_point
	LINESTRING	Geo_shape
	POLYGON
	MULTIPOINT
	MULTILINESTRING
	MULTIPOLYGON
	GEOMETRYCOLLECTION GEOMCOLLECTION
	GEOMETRY

Appendix 2: Precheck items

Check item	Check content
Source Data Source Connection Check	Checks the source data source gateway status, instance accessibility, username, and password.
Target Data Source Connection Check	Checks the target data source gateway status, instance accessibility, username, and password.
Target Database Permission Check	Checks whether the target database account has the required permissions.
Source Database Permission Check	Checks whether the source database account has the required permissions.
Check if Source Database log_slave_updates is Supported	Checks whether log_slave_updates is set to ON when the source database is a standby database.
Source Data Source and Target Data Source Version Check	Checks whether the source and target database versions are compatible.
Check if Source Database is Enabled with Binlog	Checks whether Binlog is enabled for the source database.
Check if Source Database Binlog Format is Supported	Checks whether the source database binlog_format is ROW.
Check if Source Database binlog_row_image is Supported	Checks whether the source database binlog_row_image is FULL.
Target Database Data Existence Check	Checks whether data already exists for the objects to be replicated in the target database.

Next steps

• Introduction to Data Replication