ApsaraDB RDS configuration

ApsaraDB RDS configuration

ApsaraDB RDS (Relational Database Service) is a stable, reliable, and scalable online database service from Alibaba Cloud ( Learn more about ApsaraDB RDS ).

Before you begin

• OpenSearch supports ApsaraDB RDS for MySQL instances that run MySQL 5.5, 5.6, 5.7, or 8.0.

• You must purchase a regular instance of ApsaraDB RDS High-availability Edition. Basic Edition and three-node Enterprise Edition instances are not High-availability Edition instances and are not supported.

• The ApsaraDB RDS instance must be under the same Alibaba Cloud account and in the same region as your OpenSearch application.

• Cloned ApsaraDB RDS instances are supported only if the source instance is a High-availability Edition instance.

• If you use an unsupported ApsaraDB RDS instance or an instance that does not meet the requirements, reindexing will fail.

• ApsaraDB RDS data sources must be in a Virtual Private Cloud (VPC).

Supported features

• You can pull full data from specified database tables by using manual and scheduled reindexing.

• You can merge data from multiple source tables. The source tables must have the exact same schema and data source plug-in configurations. The primary key values in the source tables must be unique. If primary key values are duplicated, the data is overwritten. This feature supports the following two scenarios:

  • An application table is configured with a single data source that contains multiple source tables.

  • An application table is configured with multiple data sources, and each data source contains one or more source tables.

• Data source field conversion plug-ins are supported.

• The following data synchronization methods are supported:

  • Automatic synchronization.

  • Real-time synchronization by using a Data Transmission Service (DTS) instance that you purchase.

  • No automatic synchronization.

• You can specify filter conditions for full data synchronization.

• You can use a wildcard character (*) to match database table names.

Limitations

• The binlog_row_image parameter of the ApsaraDB RDS instance must be set to FULL.

• Only ApsaraDB RDS High-availability Edition and Cluster Edition instances are supported.

• Supported ApsaraDB RDS for MySQL versions: 5.5, 5.6, 5.7, and 8.0.

• For ApsaraDB RDS instances that run MySQL 5.6, increase the values of the loose_max_execution_time and loose_max_statement_time parameters. This prevents errors like Query execution was interrupted, max_statement_time exceeded during reindexing.

• The ApsaraDB RDS instance must be under the same Alibaba Cloud account and in the same region as your OpenSearch application.

• After you configure an ApsaraDB RDS data source for a Standard Edition application, you cannot push incremental data by using an SDK or calling an API.

• For Standard Edition applications in regions outside the Chinese mainland, you cannot specify filter conditions for ApsaraDB RDS data sources.

• The REPLACE INTO statement is not supported.

• The TRUNCATE and DROP statements are not supported. Use the DELETE statement to delete data.

• Cloned ApsaraDB RDS instances are supported only if the source instance is a High-availability Edition instance.

• The password for the ApsaraDB RDS instance cannot contain the percent sign (%). Otherwise, the reindexing task fails.

• You cannot use a privileged account to access the ApsaraDB RDS instance. Otherwise, the connection fails.

• You cannot merge field columns from source tables with different database schemas.

• The replica client and replica slave modes with binary logging disabled are not supported. To learn how to check and enable binary logging, see the documentation.

Usage notes

• You can access ApsaraDB RDS over an internal or public network. Alibaba Cloud does not charge data transfer fees when OpenSearch retrieves data from ApsaraDB RDS.

• OpenSearch can only pull full data from the primary database. Perform full data import and reindexing during off-peak hours.

• The system automatically converts datetime and timestamp values in ApsaraDB RDS tables to milliseconds. You must set the corresponding field type in your application schema to TIMESTAMP.

• An internal endpoint for the ApsaraDB RDS instance is enabled by default. You must manually apply for a public endpoint to access the instance over the internet.

• Documents that do not meet the data source filter conditions are filtered out during full data synchronization. If documents with the same primary key values exist in the destination application table, they are also deleted.

• If no incremental data is generated at the data source for an extended period (15 days or more), data synchronization may be disrupted. If this occurs, you can resolve the issue by manually performing reindexing or an offline change.

• To synchronize data from an ApsaraDB RDS data source, you must add the CIDR blocks of the OpenSearch servers to the IP address whitelist of the ApsaraDB RDS instance. The following table lists the CIDR blocks for each region.

  Region	CIDR block
  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 recommendations for connecting to MySQL 5.7 and 8.0:

• When you connect to a MySQL 5.7 or later instance, you must authorize access to the ApsaraDB RDS instance and provide an account and password. Exercise caution when you enter credentials for the first time.

• Ensure account permissions: The account must have permissions to view all tables in the database, which is a requirement of the upstream DTS. Ensure that the show create table *.* statement can be executed correctly. If not, real-time data synchronization may fail.

• Minimize changes to account permissions: The OpenSearch real-time task acts as a MySQL client. Changes to the account can disrupt the real-time task and affect the creation of new application versions. If you change the account password, you must update the data source configuration by reconnecting to the database.

• OpenSearch requires the following minimum permissions to access your ApsaraDB RDS instance:

  1. show create table permission.

  2. REPLICATION SLAVE or REPLICATION CLIENT permission.

  3. binlog_row_image must be set to FULL.

  4. binlog_format must be set to ROW.

FAQ

• If you use a RAM user to configure an ApsaraDB RDS data source in the console, you must grant the required permissions to the RAM user. Otherwise, the error message Failed to connect to RDS service. Try again later. appears. For more information, see Create and authorize a RAM user. Make sure that the AliyunServiceRoleForOpenSearch role exists in your Alibaba Cloud account.

• The password for the ApsaraDB RDS instance cannot contain the percent sign (%). Otherwise, the reindexing task fails and returns the error message: Illegal hex characters in escape (%) pattern.

• Primary key values in an application table must be unique. If you synchronize data from multiple tables, primary key values may be duplicated, which causes data to be overwritten. To prevent this issue, you can use the StringCatenateExtractor plug-in to merge multiple field values. Set the source fields to pk,$table. Replace pk with the primary key field of the ApsaraDB RDS table. $table is a default system variable that represents the table name. You can use $table only when you use a wildcard to match multiple tables. Set the delimiter to - or a custom character. For example, if the ApsaraDB RDS table is named my_table_0 and the primary key value is 123456, the new primary key value after concatenation is 123456-my_table_0.

• To filter data based on a field of the date or datetime type, you must use the correct time format in the filter condition. For example, if the source field is named createtime, the filter condition must be in the format createtime>'2018-03-01 00:00:00'. An error occurs if you use a format like createtime>'2018-3-1 00:00:00'.

Configuration process

• You can configure an ApsaraDB RDS data source when you quickly build an application.

• For an existing application, go to the application details page and modify the configuration by performing an offline change.

Procedure

1. Select ApsaraDB RDS as the data source and click Create Database.

2. Enter the ApsaraDB RDS instance information and click Connect.

   Parameter	Description
   Instance ID	The ID of the ApsaraDB RDS database instance, not its name. You can find the instance ID in the ApsaraDB RDS console. The ID is case-sensitive. Read-only instances are not supported. Example instance ID format: rm-bp19b4g5n11111111
   Database Name	The name of the database in the instance. The name is not case-sensitive.
   Username	The case-sensitive username for a read-only database account used to retrieve the database schema and full data.
   Password	The password for the read-only account.

   OpenSearch attempts to connect and displays a message based on the result:

   Message	Solution
   The ApsaraDB RDS instance is not available for the current user in the current region.	Verify that the instance ID is correct and make sure the ApsaraDB RDS instance is in the same region as the OpenSearch application. If the issue persists, submit a ticket.
   Failed to connect to RDS service.	Verify that the connection details are correct, including the instance ID, database name, username, and password.
   The table does not exist in the current ApsaraDB RDS database.	Verify that the table name is correct and whether the table exists in the ApsaraDB RDS database.

3. After the data source connection is established, select the source tables, click the right arrow to move them to the selected list, and click OK.

   In the Select Data Source dialog box, click the RDS, MaxCompute, PolarDB, or PolarDB-X tab to specify the data source type. Then, in the Select Database section, associate the target database, select the tables, and click OK.

   • Select or enter the names of the tables to access in the database. The names are case-sensitive.

   • You can use a pattern like table_* to match multiple tables, such as table_a and table_b.

4. Map the fields. Select the database fields to pull and click OK to complete the application creation.

   OpenSearch automatically maps source fields to application fields with the same name. If the names are different, you must manually create the mapping.

   • On this page, you can add database fields to be mapped for synchronization.

   • In the Content Conversion section, you can add data source plug-ins. For more information about how to use these plug-ins, click here.

   

5. Configure filter conditions for the ApsaraDB RDS data source.

   For the data table test_table_1, select Data Transmission Service (DTS) instance as the synchronization method. In the Actions column, you can click Edit or Delete to manage the added data source. Click + Add Data to add more data tables, and set conditions in the Filter Conditions area.

   • You can configure multiple data sources for an application table, but their table structures and configurations must be identical.

   • The filter conditions configured for the ApsaraDB RDS data source pull only records that meet the specified conditions. For more information, see Data source filter conditions.