All Products
Search
Document Center

Configure a PolarDB for MySQL data source

Last Updated: Sep 09, 2021

PolarDB for MySQL is an online database service developed by Alibaba Cloud that is stable, reliable, and scalable. For more information, see PolarDB for MySQL documentation.

Before you purchase a PolarDB for MySQL cluster, take note of the following items:

  • OpenSearch supports only PolarDB for MySQL 5.6, 5.7 and 8.0.

  • You must use the Alibaba Cloud account that you use to log on to the OpenSearch console to create the PolarDB for MySQL cluster. Otherwise, OpenSearch cannot access this cluster.

  • The PolarDB for MySQL cluster must reside in the same region as the OpenSearch application for which you want to configure a data source.

  • By default, the binary logging feature is disabled after the PolarDB for MySQL cluster is created. In this case, you may fail to add a database in the cluster as a data source. Therefore, the binary logging feature must be enabled. To enable the binary logging feature, set the loose_polar_log_bin parameter whose default value is OFF to ON_WITH_GTID and the binlog_row_image parameter to FULL.

  • The PolarDB for MySQL cluster supports cloned instance.

  • The PolarDB for MySQL cluster supports read/write splitting.

Supported features

  • Incremental data can be synchronized in real time. By default, this feature is enabled.

  • All the data in a specified database or table can be manually pulled or automatically pulled at a specified time.

  • You can merge rows of multiple tables from one or more data sources. The schema of each source table must be the same as that is defined in the configurations of data processing plug-ins. In addition, the primary key values cannot be duplicated. Data with duplicate primary key values can be overwritten. This feature can be used in the following scenarios:

    • Data can be synchronized from multiple source tables in a data source to a table in an application.

    • Multiple data source can be configured for a table in an application. Data can be synchronized from one or more source tables in each data source to the table.

  • Plug-ins can be used to convert the values of source fields.

  • Filter conditions can be specified to filter full or incremental data.

  • The asterisk (*) can be used to match tables whose names have the same prefix.

Limits

  • The binlog_row_image parameter must be set to FULL for a PolarDB for MySQL cluster. Otherwise, not all the incremental data can be pulled from the cluster. To enable the binary logging feature, set the loose_polar_log_bin parameter whose default value is OFF to ON_WITH_GTID and the binlog_row_image parameter to FULL.

  • Only PolarDB for MySQL 5.6, 5.7 and 8.0 are supported.

  • You must use the Alibaba Cloud account that you use to log on to the OpenSearch console to create the PolarDB for MySQL cluster.

  • The PolarDB for MySQL cluster must reside in the same region as the OpenSearch application for which you want to configure a data source.

  • After you configure a PolarDB for MySQL data source for a standard application, you cannot use OpenSearch API or SDKs operation to push incremental data to the application.

  • If you configure a PolarDB for MySQL data source for a standard application, filter conditions cannot be specified to filter the data of the data source.

  • REPLACE INTO statements cannot be used.

  • TRUNCATE and DROP statements cannot be used to delete data. To delete data, use DELETE statements.

  • Views cannot be used to synchronize incremental data.

  • The password that is used to access the PolarDB for MySQL data source cannot contain the percent sign (%). Otherwise, the reindexing will fail.

  • You cannot merge columns of different source tables.

Usage notes

  • The ApsaraDB RDS for MySQL or PolarDB data source that is connected to OpenSearch is attached to a PolarDB-X instance. In this case, when you configure the data source, you must specify the name of the database shard that is actually used on the PolarDB-X instance. After database sharding, a database on the PolarDB-X instance has one shadow database and eight database shards. Data is randomly written to the database shards.

  • You can switch between the internal and public endpoints of a PolarDB for MySQL cluster. No traffic fee is generated when OpenSearch accesses the data of the PolarDB for MySQL cluster.

  • OpenSearch allows you to pull full data from only the primary database. We recommend that you import full data and rebuild indexes during off-peak hours as needed.

  • If a source table from PolarDB for MySQL cluster contains fields of the DATETIME or TIMESTAMP format, the values of these fields are converted to the number of milliseconds. You need to set the type of destination fields in your application to TIMESTAMP.

  • After full data or incremental data is synchronized, the documents that do not meet the filter conditions are filtered out. If specific documents have the same primary key values as those in the destination table of an application, these documents are also filtered out.

Account authorization

  • To connect to a PolarDB for MySQL cluster, you must obtain required permissions to access the cluster and enter an account and a password. Exercise caution when you use a specific account and password to connect to the cluster for the first time.

  • Make sure that your account has the permissions to query all the tables in the source database. Make sure that the show create table *.* statement can be executed as expected. If your account does not have the permissions, real-time synchronization may fail.

  • Make sure that the account and permissions are not changed unless necessary. Account changes may lead to failed real-time synchronization. In addition, if changes are made to the current account, a new version of the application may fail to be created. If the account and password are changed, you must remove the existing data source and add the destination database as a new data source.

Answers to FAQ

  • After a PolarDB for MySQL data source is configured, reindexing may be suspended. In this case, create a test table in the database where source tables reside. Then, write or update some data entries in the test table every minute. This ensures that continuous binlog is generated when reindexing.

  • The PolarDB for MySQL cluster is connected to an advanced application, but the application has overdue payments. After you pay the overdue payments, reindexing can be manually triggered and a new version of the application will be created. In this case, incremental data can be synchronized to the new version.

  • The password that is used to access the PolarDB for MySQL cluster cannot contain the percent sign (%). Otherwise, the reindexing fails. (Error message: Illegal hex characters in escape (%) pattern).

  • The primary key value of a table in an application must be unique. If data is synchronized from multiple tables with the same table schema, duplicated primary key values may exist. In this case, data may be overwritten. To prevent this issue, you can use the StringCatenateExtractor plug-in to concatenate the values of multiple fields. The source fields are pk and $table. Replace pk with the primary key field of a table in the PolarDB for MySQL cluster. $table is the default system variable, which indicates the table name. Connect the source fields by using a hyphen (-) or a custom character.

For example, a table in the PolarDB for MySQL cluster is named my_table_0 and the primary key value is 123456. In this case, the new primary key value obtained after concatenation is 123456-my_table_0.

  • The fields of the date or datetime type in a source table are filtered. For example, the name of a source field is createtime and the time format that is specified in the filter conditions to filter the source data must be createtime>'2018-03-01 00:00:00'. If the value of the field is in the createtime>'2018-3-1 00:00:00' format, an error will be reported.

Configure a PolarDB for MySQL data source

  • You can configure a PolarDB for MySQL data source when you configure a new application.

  • If you want to configure a PolarDB for MySQL data source for an existing application, you can go to the details page of the application and click Offline Change.

Procedure and usage notes

  1. When you create an application or modify the configurations of an application, set the data source type to PolarDB and click Create Database in the Data Source step to add or edit a data source.

1

2. After you set the parameters of the PolarDB for MySQL data source, click Connect.

2

Parameter

Description

Cluster ID

The ID of the PolarDB for MySQL cluster. You can obtain the ID in the PolarDB console. The ID is case-sensitive. Sample ID format: pc-uf6c056ny9tiaj1l7.

Database Name

The name of the database to be connected in the PolarDB for MySQL cluster. The name is not case-sensitive.

Username

The database account that is used to obtain the table schemas and full data of the database. This account is case-sensitive.

Password

The password of the database account.

Authorize

After OpenSearch is authorized to access PolarDB, click Complete RAM Authorization.

Then, OpenSearch attempts to connect to the data source and returns error messages if the connection fails.

Error message

Solution

The PolarDB for MySQL cluster does not reside in the same region as the OpenSearch application.

Check whether the cluster ID is correct and make sure that the PolarDB for MySQL cluster resides in the same region as the OpenSearch application. If the PolarDB for MySQL cluster and the OpenSearch application reside in the same region but the error message is still returned, submit a ticket.

The connection to a PolarDB for MySQL data source failed.

Check whether the cluster ID, database name, username, and password in the connection string that is used to connect to the data source are valid.

The table does not exist in the current PolarDB for MySQL cluster.

Check whether the table name is valid and whether the PolarDB for MySQL cluster contains the table.

The configurations of the PolarDB for MySQL cluster are invalid.

Go to the parameter configuration page of this cluster in the PolarDB console to modify the configurations. Then, try again later.

3. After the data source is connected, select a table. The following figure shows the interface after the connection is established. After you select a table, click OK.

3
  • Select or enter the name of the table to be accessed in the field. The name is case-sensitive.

  • You can use an asterisk (*) to match multiple tables with the same table schema in a database. The names of these tables have the same prefix. For example, you can enter table_* to match tables such as table_a and table_b.

4. After the connection is successful, configure field mappings. OpenSearch automatically synchronizes source fields from the table. For more information about data processing plug-ins, see Data processing plug-ins.

4

5. Specify filter conditions to filter the source data. You cannot specify filter conditions for a standard application. After you complete the configurations in the Data Source step, click Submit. The application schema is configured.

6
  • You can configure multiple data sources for a table in OpenSearch, provided that the schemas and configurations of source tables are the same.

  • Only data entries that meet the filter conditions can be pulled from the PolarDB for MySQL data source. For more information, see Filter conditions of data sources.

  • Automatic data synchronization indicates automatic synchronization of incremental data. By default, automatic data synchronization is enabled.