If you encounter slow queries when you use a PolarDB for MySQL database, you can use Data Transmission Service (DTS) to synchronize production data from the database to an Elasticsearch cluster in real time. Then, you can search for and analyze the synchronized data in the Elasticsearch cluster. This topic describes how to synchronize data from a PolarDB for MySQL database to an Elasticsearch cluster.
Background information
The following cloud services are used:- DTS is a data transmission service that integrates data migration, data subscription, and real-time data synchronization. For more information, see DTS. You can use DTS to synchronize these SQL statements: INSERT, DELETE, and UPDATE. Important When you synchronize data, you must select a data source and a version that are supported by DTS.
- PolarDB is a next-generation relational database service developed by Alibaba Cloud. It is compatible with MySQL, PostgreSQL, and Oracle database engines. A PolarDB cluster can provide a maximum of 100 TB of storage space and can be scaled to a maximum of 16 nodes. PolarDB provides superior performance in storage and computing to meet diverse requirements of enterprises. For more information, see PolarDB for MySQL overview.
- Elasticsearch is a Lucene-based, distributed, real-time search and analytics engine. It allows you to store, query, and analyze large amounts of datasets in near real time. In most cases, it is used as a basic engine or technology to accommodate complex queries and high application performance. For more information, see What is Alibaba Cloud Elasticsearch?.
This topic can be used to guide real-time synchronization for data in relational databases.
Precautions
- DTS uses read and write resources of the source and destination databases during initial full data synchronization. This may increase the loads of the database servers. If the database performance or specifications are unfavorable, or the data volume is large, database services may become unavailable. For example, DTS occupies a large amount of read and write resources in the following cases: a large number of slow SQL queries are performed on the source database, the tables have no primary keys, or a deadlock occurs in the destination database. Before you synchronize data, evaluate the impact of data synchronization on the performance of the source and destination databases. We recommend that you synchronize data during off-peak hours. For example, you can synchronize data when the CPU utilization of the source and destination databases is less than 30%.
- DTS does not synchronize data definition language (DDL) operations. If a DDL operation is performed on a table in the source database during data synchronization, you must perform the following steps: Remove the table from the required objects, remove the index for the table from the Elasticsearch cluster, and then add the table to the required objects. For more information, see Remove an object from a data synchronization task and Add an object to a data synchronization task.
- To add columns to the table that you want to synchronize, perform the following steps: Modify the mapping of the table in the Elasticsearch cluster, perform DDL operations in the source MySQL database, and then pause and start the data synchronization task.
Preparations
- Create an Alibaba Cloud Elasticsearch cluster and enable the Auto Indexing feature for the cluster.
For more information, see Create an Alibaba Cloud Elasticsearch cluster and Enable the Auto Indexing feature. In this topic, an Elasticsearch V6.7 cluster is used.
Note To ensure data security, Alibaba Cloud Elasticsearch disables Auto Indexing by default. When you use DTS to synchronize data to an Elasticsearch cluster, you must create indexes on the Elasticsearch cluster by submitting data instead of calling the create index operation. Therefore, before you use DTS to synchronize data, you must enable Auto Indexing for the cluster. - Create a PolarDB for MySQL cluster and enable binary logging.
For more information, see Purchase a pay-as-you-go cluster and Enable binary logging.
- Create a PolarDB for MySQL database and a table, and insert test data into the table.
For more information, see Database Management.
- Table creation statement
CREATE TABLE `product` ( `id` bigint(32) NOT NULL AUTO_INCREMENT, `name` varchar(32) NULL, `price` varchar(32) NULL, `code` varchar(32) NULL, `color` varchar(32) NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;
- Test data
INSERT INTO `estest`.`product` (`id`,`name`,`price`,`code`,`color`) VALUES (1,'mobile phone A','2000','amp','golden'); INSERT INTO `estest`.`product` (`id`,`name`,`price`,`code`,`color`) VALUES (2,'mobile phone B','2200','bmp','white'); INSERT INTO `estest`.`product` (`id`,`name`,`price`,`code`,`color`) VALUES (3,'mobile phone C','2600','cmp','black'); INSERT INTO `estest`.`product` (`id`,`name`,`price`,`code`,`color`) VALUES (4,'mobile phone D','2700','dmp','red'); INSERT INTO `estest`.`product` (`id`,`name`,`price`,`code`,`color`) VALUES (5,'mobile phone E','2800','emp','silvery');
- Table creation statement
Procedure
- PreparationsCreate an Elasticsearch cluster and a PolarDB for MySQL cluster and prepare test data.
- Step 1: Configure and enable a data synchronization channelUse DTS to create and start a real-time task to synchronize data from the PolarDB for MySQL database to the Elasticsearch cluster.
- Step 2: View the data synchronization resultLog on to the Kibana console of the Elasticsearch cluster and query the synchronized data.
- Step 3: Verify incremental data synchronizationAdd data to the PolarDB for MySQL database and check whether the data is synchronized to the Elasticsearch cluster.
Step 1: Configure and enable a data synchronization channel
- Create a data synchronization task in the DTS console.
- On the page that appears, select the region. Then, find the target instance and click Configure Synchronization Channel in the Actions column.
- In the Create Data Synchronization Task wizard, configure the PolarDB for MySQL cluster and Elasticsearch cluster for synchronization.
Field/Section Parameter Description Synchronization Task Name None - DTS automatically generates a task name. You do not need to use a unique task name.
- We recommend that you use an informative name for easy identification.
Source Instance Details Instance Type The value of this parameter is PolarDB Instance and cannot be changed. Instance Region The value of this parameter is the region that you selected for the PolarDB for MySQL cluster when you purchased the data synchronization instance. The value cannot be changed. PolarDB Instance ID The ID of the PolarDB for MySQL cluster. Database Account The account of the PolarDB for MySQL database from which you want to synchronize data. Note The account must have the read permissions on the database.Database Password The password for the account of the PolarDB for MySQL database. Destination Instance Details Instance Type The value of this parameter is Elasticsearch and cannot be changed. Instance Region The value of this parameter is the region that you selected for the Elasticsearch cluster when you purchased the data synchronization instance. The value cannot be changed. Elasticsearch The ID of the Elasticsearch cluster. Database Account The username of the Elasticsearch cluster. Default value: elastic. Database Password The password of the Elasticsearch cluster. Enter the password that corresponds to the username specified by Database Account. - Click Set Whitelist and Next. After the synchronization account is created, click Next.Important In this step, the IP address of the DTS server is automatically added to the whitelists of the PolarDB for MySQL cluster and Elasticsearch cluster. This ensures that the DTS server communicates with both clusters.
- Select the objects that you want to synchronize.
Parameter Description Index Name - Table Name
If you select Table Name, the indexes and tables created on the Elasticsearch cluster use the same names as those on the ApsaraDB RDS for MySQL instance.
- DatabaseName_TableName
If you select DatabseName_TableName, the indexes created on the Elasticsearch cluster are named in the format of Database name_Table name.
Processing Mode In Existed Target Table - Pre-check and Intercept: The system checks whether the destination Elasticsearch cluster contains indexes that have the same names as tables in the source database. If the destination Elasticsearch cluster does not contain indexes that have the same names as tables in the source database, the precheck is passed. Otherwise, the system displays an error message during the precheck and does not start the data synchronization task. Note If indexes in the destination Elasticsearch cluster have the same names as tables in the source database, and cannot be deleted or renamed, you can perform the operations described in Rename an object to be synchronized to avoid table name conflicts.
- Ignore: The system skips the precheck for identical table names in the source database and destination Elasticsearch cluster. Warning If you select Ignore, data inconsistency may occur and your business may be affected.
- The source database and destination Elasticsearch cluster have the same mappings. If the primary key of a record in the destination Elasticsearch cluster is the same as that in the source database, the record remains unchanged during initial data synchronization. However, the record is overwritten during incremental data synchronization.
- The source database and destination Elasticsearch cluster have different mappings. This may cause initial data synchronization to fail, only some columns to be synchronized, or the entire data synchronization to fail.
Objects to be synchronized Select objects from the Available section and click the button to move the objects to the Selected section.
- Table Name
- In the Selected section, move the pointer over the name of the table whose data you want to synchronize and click Edit. In the Edit Table dialog box, configure parameters for the table in the Elasticsearch cluster, such as Index Name and Type Name. Then, click OK.
Parameter Description Index Name For more information, see Terms. Type Name For more information, see Terms. Filter Specifies SQL filter conditions to filter data. Only data that meets the specific conditions is synchronized to the Elasticsearch cluster. For more information, see Use SQL conditions to filter data. IsPartition Specifies whether to set partitions. If you select Yes, you must also specify the partition key column and number of partitions. _id value - the primary key of table
Composite primary key fields are merged into one column.
- Bis id
If you select a business key, you must also specify the business key column.
add param Select the required column param and column param value parameters. For more information, see Mapping parameters. - the primary key of table
- In the lower-right corner of the page, click Precheck. Important
- You can start a data synchronization task only after the task passes the precheck.
- If the task fails to pass the precheck, click the icon next to each failed item to view the details. Troubleshoot the issues and run the precheck again.
- After the The precheck is passed message appears, close the Precheck dialog box.The data synchronization task starts. Data starts to synchronize until initial synchronization is complete and the synchronization task is in the Synchronizing state.Important PolarDB for MySQL and Elasticsearch support different data types. During initial schema synchronization, DTS maps the data types of the PolarDB for MySQL database to those of the Elasticsearch cluster. For more information, see Data type mappings for schema synchronization.
Step 2: View the data synchronization result
- Log on to the Kibana console of the Elasticsearch cluster. For more information, see Log on to the Kibana console.
- Use a command to query the synchronized data.
- Perform operations in the console to query the synchronized data.
Step 3: Verify incremental data synchronization
- Log on to the PolarDB console.
- Execute the following statement to insert a data record into the PolarDB for MySQL database:
INSERT INTO `estest`.`product` (`id`,`name`,`price`,`code`,`color`) VALUES (6,'mobile phone F','2750','fmp','white');
- Log on to the Kibana console.For more information, see Log on to the Kibana console.
- In the left-side navigation pane, click Discover.
- Select the index pattern that you created to view the synchronized incremental data.Note After you delete or modify data in the source PolarDB for MySQL database, you can use the same method to verify data synchronization.