OpenSearch:Configure an ApsaraDB RDS for MySQL data source

ApsaraDB RDS for MySQL is an online relational database service (RDS) that is provided by Alibaba Cloud. ApsaraDB RDS for MySQL is stable, reliable, and scalable, and provides out-of-box capabilities. For more information, visit the product page.

Considerations before you purchase an ApsaraDB RDS for MySQL instance

• OpenSearch supports ApsaraDB RDS for MySQL instances whose MySQL engine version is 5.2 to 8.0.

• You must purchase a regular ApsaraDB RDS for MySQL instance of High-availability Edition. ApsaraDB RDS for MySQL instances of Basic Edition or Enterprise Edition are not supported.

• You must use the Alibaba Cloud account that you use to log on to the OpenSearch console to purchase an ApsaraDB RDS for MySQL instance. Otherwise, OpenSearch cannot access the ApsaraDB RDS for MySQL instance that you purchase.

• The ApsaraDB RDS for MySQL instance that you purchase must reside in the same region as the OpenSearch application for which you want to configure a data source.

• You cannot configure a cloned ApsaraDB RDS for MySQL instance for an OpenSearch application. Otherwise, the application remains in the initializing state after it is activated.

• If you configure an ApsaraDB RDS for MySQL instance that is not supported or does not comply with the limits, reindexing fails for the OpenSearch application.

• The ApsaraDB RDS for MySQL data sources for OpenSearch applications must be deployed in virtual private clouds (VPCs).

Note:

• PolarDB-X data sources are not supported.

• You must authorize OpenSearch to obtain data from an ApsaraDB RDS for MySQL instance. If you have configured an IP address whitelist for the ApsaraDB RDS for MySQL instance, the IP address of OpenSearch is added to the whitelist. Otherwise, you cannot connect to the ApsaraDB RDS for MySQL instance.

Supported features

• After all the data of the source tables is synchronized to the application, reindexing can be triggered manually or triggered at the scheduled time. For more information, see Manual and scheduled reindexing.

• You can merge rows of multiple tables from one or more data sources. To merge the rows, make sure that the source tables use the same schema and the configurations of the data processing plug-ins are the same in the data sources. In addition, the primary key values cannot be duplicated. Data with duplicate primary key values is overwritten. This feature can be used in the following scenarios:

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

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

• You can use plug-ins to convert the values of data source fields.

• You can specify filter conditions to filter full data.

• You can use an asterisk (*) to match tables whose names have the same prefix.

Limits

• You must set the binlog_row_image parameter to FULL for an ApsaraDB RDS for MySQL instance.

• Only regular ApsaraDB RDS for MySQL instances of High-availability Edition are supported.

• The version of the MySQL engine for an ApsaraDB RDS for MySQL instance must be 5.2 to 8.0.

• For ApsaraDB RDS for MySQL instances whose MySQL engine version is 5.6, we recommend that you change the thresholds of the loose_max_execution_time and loose_max_statement_time parameters to greater values. Otherwise, an error message similar to the following one appears during reindexing: Query execution was interrupted, max_statement_time exceeded.

• You must use the Alibaba Cloud account that you use to log on to the OpenSearch console to purchase an ApsaraDB RDS for MySQL instance. Otherwise, OpenSearch cannot access the ApsaraDB RDS for MySQL instance that you purchase.

• The ApsaraDB RDS for MySQL instance that you purchase must reside in the same region as the OpenSearch application for which you want to configure a data source.

• After you configure an ApsaraDB RDS for MySQL data source for a standard application, you cannot use SDKs or call API operations to push incremental data to the application.

• If you configure an ApsaraDB RDS for MySQL data source for a standard application, you cannot specify filter conditions to filter data of the data source.

• REPLACE INTO statements are not supported.

• You cannot use TRUNCATE or DROP statements to delete data. To delete data, use DELETE statements.

• You cannot configure a cloned ApsaraDB RDS for MySQL instance for an OpenSearch application. Otherwise, the application remains in the initializing state after it is activated.

• The password that is used to connect to an ApsaraDB RDS for MySQL data source cannot contain the percent sign (%). Otherwise, reindexing fails.

• You cannot use a privileged account to connect to an ApsaraDB RDS for MySQL data source. Otherwise, OpenSearch fails to connect to the ApsaraDB RDS for MySQL data source.

• You cannot merge columns of source tables from different databases.

• The replica client and replica slave modes with binary logging disabled are not supported. For more information, see Precheck on the binary logging of the source database.

Usage notes

• You can switch between the internal and public endpoints of an ApsaraDB RDS for MySQL data source. No traffic fee is generated when OpenSearch accesses the data of the ApsaraDB RDS for MySQL instance.

• OpenSearch allows you to pull full data only from the primary database. We recommend that you perform reindexing and import full data during off-peak hours based on your business requirements.

• If a source table from an ApsaraDB RDS for MySQL instance contains fields of the DATETIME or TIMESTAMP type, the values of these fields are converted to the values in milliseconds. You need to set the type of destination fields in your application to TIMESTAMP.

• You must apply for an internal endpoint for an ApsaraDB RDS for MySQL instance so that OpenSearch can access the instance. Otherwise, the error message Failed to connect to RDS service. Try again later appears.

• After full 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.

• If no incremental data is generated for a long period of time (15 days or more), a data synchronization error may occur. In this case, you can manually perform offline changes.

• When you use OpenSearch to synchronize data from an ApsaraDB RDS for MySQL instance, you must add the IP addresses and CIDR blocks of the servers in the region in which OpenSearch resides to the IP address whitelist of the ApsaraDB RDS for MySQL instance. The following table lists the IP address whitelists of different regions.

  Region	IP address
  China (Hangzhou)	100.104.190.128/26,100.104.241.128/26
  China (Beijing)	100.104.16.192/26,100.104.179.0/26
  China (Shanghai)	100.104.37.0/26,100.104.46.0/26
  China (Shenzhen)	100.104.87.192/26,100.104.132.192/26
  China (Qingdao)	100.104.240.128/26,100.104.111.128/26
  China (Zhangjiakou)	100.104.155.192/26,100.104.238.64/26
  Germany (Frankfurt)	100.104.127.0/26,100.104.35.192/26
  US (Silicon Valley) and US (Virginia)	100.104.193.128/26,100.104.119.128/26
  Singapore	100.104.58.192/26,100.104.74.192/26

Account authorization

Take note of the following items if you configure an ApsaraDB RDS for MySQL data source whose MySQL engine version is 5.7 or later for an OpenSearch application:

• To connect to the data source, you must obtain the required permissions to access the data source and enter an account and a password. Exercise caution when you select an account and password to connect to the data source for the first time.

• If you want to use Data Transmission Service (DTS) to synchronize data, make sure that your account has permissions to query all the tables in the source databases. In addition, 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.

• Do not change the account permissions unless necessary. A real-time OpenSearch task is deemed as a MySQL client. Account changes may lead to a failure of 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 ApsaraDB RDS for MySQL instance again as a new data source.

• To allow OpenSearch to access an ApsaraDB RDS for MySQL data source, make sure that the following minimum requirements are met: 1. The permissions to execute the SHOW CREATE TABLE statement are granted to OpenSearch. 2. The permissions to execute the REPLICATION SLAVE or REPLICATION CLIENT statement are granted to OpenSearch. 3. The value of the binlog_row_image parameter is set to FULL. 4. The value of the binlog_format parameter is set to ROW.

• If SSL encryption is enabled for an ApsaraDB RDS for MySQL instance, make sure that SSL certificates are valid. If the SSL certificates expire, a connection error may occur. You must update the validity periods of the SSL certificates in a timely manner.

Troubleshooting

• To configure an ApsaraDB RDS for MySQL data source for an application in the OpenSearch console as a RAM user, you must authorize the RAM user first. Otherwise, the error message Failed to connect to RDS service. Try again later appears. For more information, see Create a RAM user. The AliyunServiceRoleForOpenSearch role must be created by using your Alibaba Cloud account.

• The password that is used to connect to the ApsaraDB RDS for MySQL instance cannot contain the percent sign (%). Otherwise, reindexing fails and the error message "Illegal hex characters in escape (%) pattern" appears.

• 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, duplicate 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 ApsaraDB RDS for MySQL instance. $table is the default system variable, which indicates the table name. To use $table, you must use a wildcard to match multiple tables with the same table schema. Concatenate the values of multiple source fields by using a hyphen (-) or a custom character. For example, a table in the ApsaraDB RDS for MySQL instance 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.

• If data is filtered based on a field of the DATE or DATETIME type in a database table, make sure that the format of the time specified in the filter conditions is correct. For example, if data is filtered based on the source field named createtime in a database table, the format of the time specified in the filter conditions must be createtime>'2018-03-01 00:00:00'. If the time format is createtime>'2018-3-1 00:00:00', an error is returned.

Methods for configuring an ApsaraDB RDS for MySQL data source

• You can configure an ApsaraDB RDS for MySQL data source when you configure an application.

• If you want to configure an ApsaraDB RDS for MySQL data source for an existing application, you can go to the details page of the application and perform offline changes.

Procedure and usage notes

1. In the Select Data Source panel, select ApsaraDB RDS for MySQL and click Connect to Database.

2. In the Connect to Database dialog box, set the parameters as required and click Connect.

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

3. After the connection is established, the panel shown in the following figure is displayed. In the Select Table section, select tables in the Table box on the left, click the rightwards arrow to add the tables to the Selected box on the right, and then click OK.

• Select or enter the names of the tables to be accessed. The names are 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. In the Field Mapping step, select the fields to be pulled from the database and click OK.

• In the Field Mapping step, you can add database fields to be synchronized to OpenSearch.

• You can click the plus sign (+) of a field in the Content Conversion column to add data processing plug-ins to process the field. For more information about how to use data processing plug-ins, see Use data processing plug-ins.

5. Specify filter conditions to filter data of the ApsaraDB RDS for MySQL data source.

• You can configure multiple data sources for a table in an OpenSearch application. The schemas and configurations of the source tables in the data sources must be the same.

• After the filter conditions that you specified take effect, only data entries that meet the filter conditions are pulled. For more information, see Configure conditions to filter fields in ApsaraDB RDS and PolarDB data sources.