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');

Procedure

  1. Preparations
    Create an Elasticsearch cluster and a PolarDB for MySQL cluster and prepare test data.
  2. Step 1: Configure and enable a data synchronization channel
    Use DTS to create and start a real-time task to synchronize data from the PolarDB for MySQL database to the Elasticsearch cluster.
  3. Step 2: View the data synchronization result
    Log on to the Kibana console of the Elasticsearch cluster and query the synchronized data.
  4. Step 3: Verify incremental data synchronization
    Add 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

  1. Create a data synchronization task in the DTS console.
    1. Log on to the Data Transmission Service console.
    2. In the left-side navigation pane, click Data Synchronization.
    3. On the page that appears, click Create Data Synchronization Task. Then, purchase a data synchronization instance on the buy page as prompted.
      For more information, see Purchase a data synchronization instance. On the buy page, set Source Instance to PolarDB, Target Instance to Elasticsearch, and Synchronization Topology to One-Way Synchronization.
  2. On the page that appears, select the region. Then, find the target instance and click Configure Synchronization Channel in the Actions column.
  3. In the Create Data Synchronization Task wizard, configure the PolarDB for MySQL cluster and Elasticsearch cluster for synchronization.
    Field/SectionParameterDescription
    Synchronization Task NameNone
    • 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 DetailsInstance TypeThe value of this parameter is PolarDB Instance and cannot be changed.
    Instance RegionThe 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 IDThe ID of the PolarDB for MySQL cluster.
    Database AccountThe 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 PasswordThe password for the account of the PolarDB for MySQL database.
    Destination Instance DetailsInstance TypeThe value of this parameter is Elasticsearch and cannot be changed.
    Instance RegionThe 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.
    ElasticsearchThe ID of the Elasticsearch cluster.
    Database AccountThe username of the Elasticsearch cluster. Default value: elastic.
    Database PasswordThe password of the Elasticsearch cluster. Enter the password that corresponds to the username specified by Database Account.
  4. 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.
  5. Select the objects that you want to synchronize.
    ParameterDescription
    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 Rightwards arrow button to move the objects to the Selected section.

  6. 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.
    ParameterDescription
    Index NameFor more information, see Terms.
    Type NameFor more information, see Terms.
    FilterSpecifies 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.
    IsPartitionSpecifies 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 paramSelect the required column param and column param value parameters. For more information, see Mapping parameters.
  7. 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 Information icon icon next to each failed item to view the details. Troubleshoot the issues and run the precheck again.
  8. 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

  1. Log on to the Kibana console of the Elasticsearch cluster.
    For more information, see Log on to the Kibana console.
  2. Use a command to query the synchronized data.
    1. In the left-side navigation pane, click Dev Tools.
    2. On the Console tab of the page that appears, run the following command to query the synchronized data:
      GET /product/_doc/_search
      If the command is successfully executed, the result shown in the following figure is returned.Query result
  3. Perform operations in the console to query the synchronized data.
    1. Create an index pattern for the destination index.
      1. In the left-side navigation pane, click Management.
      2. In the Kibana section, click Index Patterns.
      3. Click Create index pattern.
      4. In the Create index pattern section, enter a name in the Index pattern field.
      5. Click Next step.
      6. Click Create index pattern.
    2. In the left-side navigation pane, click Discover.
    3. Select the index pattern that you created to view the synchronized data.
      View the synchronized data

Step 3: Verify incremental data synchronization

  1. Log on to the PolarDB console.
  2. 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');
  3. Log on to the Kibana console.
    For more information, see Log on to the Kibana console.
  4. In the left-side navigation pane, click Discover.
  5. Select the index pattern that you created to view the synchronized incremental data.
    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.