All Products
Search
Document Center

Configure an ApsaraDB RDS for MySQL data source

Last Updated: Sep 10, 2021

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 features high reliability and auto scaling. For more information, visit the product page.

Precautions before you purchase an ApsaraDB RDS for MySQL instance

  • You must purchase a common ApsaraDB RDS for MySQL instance of High-availability Edition. The version of the ApsaraDB RDS for MySQL instance must be later than 5.2 but earlier than or equal to 8.0. Note: 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 use a cloned ApsaraDB RDS for MySQL instance to configure an OpenSearch application. Otherwise, the application remains in the initializing state after it is activated.

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

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

  • PolarDB-X data sources are not supported.

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. 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 will 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 sources 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.

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

  • You can specify filter conditions to filter full or incremental 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. Otherwise, part of the incremental data may not be pulled from the instance.

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

  • The version of the ApsaraDB RDS for MySQL instance must be later than 5.2 but earlier than or equal to 8.0.

  • 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 API or SDKs 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 the 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 use views to synchronize incremental data.

  • You cannot use a cloned ApsaraDB RDS for MySQL instance to configure 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, the 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 different source tables.

  • The replica client and replica slave modes with binary logging disabled are not supported. For more information, see Binlog-related prechecks fail when you use DTS to migrate incremental data.

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 data source.

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

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

  • If an ApsaraDB RDS for MySQL instance is created in a public cloud region, you must apply for an internal endpoint for the instance so that OpenSearch can access the data source. Otherwise, the error message Failed to connect to RDS service. Try again later appears.

  • 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

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

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

  • Make sure that your account has the permissions to view all the tables in the database. 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 cause real-time synchronization to fail. 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.

  • 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. 2. The permissions to execute the REPLICATION SLAVE or REPLICATION CLIENT statement. 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.

FAQ

  • To use a RAM user to configure an ApsaraDB RDS for MySQL data source for an application in the OpenSearch console, you must authorize the RAM user. Otherwise, the error message Failed to connect to RDS service. Try again later appears. For more information, see RAM authentication rules of API operations.

  • The password that is used to connect to an ApsaraDB RDS for MySQL data source cannot contain the percent sign (%). Otherwise, the reindexing fails.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, 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 ApsaraDB RDS for MySQL data source. $table is the default system variable, which indicates the table name. Concatenate the source fields by using a hyphen (-) or a custom character. For example, a table in the ApsaraDB RDS for MySQL data source 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.

  • Filter data based on the fields of DATE or DATETIME type in a database table. For example, the name of a source field in the database table is createtime. In this case, the time format that is specified in the filter conditions to filter the data of the data source must be createtime>'2018-03-01 00:00:00'. If the createtime>'2018-3-1 00:00:00' format is used, an error is returned.

Configure an ApsaraDB RDS for MySQL data source

  • You can configure an ApsaraDB RDS for MySQL data source when you configure a new 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 click Offline Change.

Procedure and notes

1.Select RDS and click New Database.

1

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

2

Parameter

Description

Instance ID

The ID of the ApsaraDB RDS for MySQL instance. You can obtain the ID in the ApsaraDB RDS for MySQL console. The ID is case-sensitive. Read-only instances are not supported. Sample ID: rm-bp19b4g5n11111111.

Database Name

The name of the database to be connected in the ApsaraDB RDS for MySQL instance. The name is not case-sensitive.

Username

The database account that is used to obtain the table schemas and full data in the database. The account must be case-sensitive and have read-only permissions.

Password

The password of the database account.

Authorize

You must authorize OpenSearch to obtain data from the ApsaraDB RDS for MySQL instance. If you have configured an IP whitelist, the IP address of OpenSearch is added to the whitelist. If you do not select Authorize, the Connect button is unavailable.

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

Error message

Solution

The ApsaraDB RDS for MySQL instance does not reside in the same region as the OpenSearch application.

Check whether the instance ID is correct and make sure that the ApsaraDB RDS for MySQL instance resides in the same region as the OpenSearch application. If the ApsaraDB RDS for MySQL instance resides in the same region as the OpenSearch application for which you want to configure a data source but the error message still appears, submit a ticket.

Failed to connect to RDS service.

Check whether the instance ID, database name, username, and password in the connection string that is used to connect to the ApsaraDB RDS for MySQL instance are valid.

The table does not exist in the current ApsaraDB RDS for MySQL instance.

Check whether the table name is valid and whether the ApsaraDB RDS for MySQL data source contains the table.

3.The following figure shows the interface after the connection is established. You can select tables in the Data Tables section on the left, and click the rightwards arrow to add the tables to the Selected Tables section on the right.

3
  • Select or enter the name of the table to be accessed. 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.Configure field mappings. Select the fields to be pulled from the database. Click OK.

4
  • On the page as shown in the preceding figure, you can add fields from the database 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, click here.

5

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

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

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

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