MySQL data source

Parameter

Description

Data Source Name

The data source name must be unique within the workspace. We recommend using a name that clearly identifies the business and environment, for example, rds_mysql_order_dev.

Configuration Mode

Select Alibaba Cloud instance mode. For more information about configuration modes, see Scenario 1: Instance Mode (Current Cloud Account) and Scenario 2: Instance Mode (Other Cloud Account).

Alibaba Cloud Account

Select the cloud account to which the instance belongs. If you select Another Alibaba Cloud Account, you must configure cross-account permissions. For more information, see Cross-Account Authorization (RDS, Hive, or Kafka).

If you select other cloud account, provide the following information:

• Main account ID: The ID of the main account that owns the instance.

• RAM role name: The RAM role provided by the other cloud account. This role must have the required permissions to access the target instance.

Region

The instance's region.

Instance

Select the name of the instance to connect to.

Standby library settings

If your RDS instance has a read-only instance (standby instance), you can configure tasks to read data from it. This prevents read operations from interfering with the performance of the primary instance.

Instance Address

After selecting the correct instance, click Get Latest Address to view information such as its public/private address, VPC, and VSwitch.

Database

The name of the database to which the data source connects. Ensure that the specified user has the required permissions to access this database.

Access identity

Select the credential source that DataWorks uses to access MySQL. The following options are supported:

• Username and password: Enter the username and password for the MySQL database. DataWorks stores them with symmetric encryption. If you use an RDS instance, you can create and manage them in the account management section of the instance.

• Key Management Service: Use a generic secret hosted in Alibaba Cloud Key Management Service (KMS). You must first create a generic secret in KMS. For more information, see Manage and use generic secrets. Then, in the data source, select the Kms Region where the KMS generic secret resides, and select the target generic secret from the KMS List. The content of a KMS generic secret supports the following two JSON formats:

  { "username": "biz_rw", "password": "S3cr3t!" }
  { "AccessKeyId": "LTAI...", "AccessKeySecret": "..." }

  Note

  After the content of a KMS generic secret is changed, DataWorks caches the secret for up to 5 minutes. The new secret takes effect within 5 minutes at the latest.

Support OSS binlog reading

If enabled, DataWorks attempts to fetch binlogs from OSS when RDS binlogs are inaccessible. This prevents interruptions to real-time synchronization tasks. For more information, see Configure authorization for reading OSS binlogs. Based on your authorization configuration, set the OSS binlog access identity.

Authentication Method

Select no authentication or SSL authentication. If you select SSL authentication, ensure it is also enabled on the instance. Prepare the certificate file and upload it to Authentication File Management.

Version

Log on to the MySQL server and run the SELECT VERSION() query to check the version number.

Parameter

Description

Data Source Name

The data source name must be unique within the workspace. We recommend using a name that clearly identifies the business and environment, for example, rds_mysql_order_dev.

Configuration Mode

Select User-created Data Store with Public IP Addresses. In this mode, a JDBC URL is used to connect to the database.

Connection string preview

After entering the connection address and database name, DataWorks automatically generates a preview of the JDBC URL.

Connection Address

Host address: Enter the IP address or domain name of the database. If the database is an Alibaba Cloud RDS instance, you can find the address on the Database Connection page in the instance details.

Port number: The database port. The default value is 3306.

Database Name

The name of the database to which the data source connects. Ensure that the specified user has the required permissions to access this database.

Access identity

Select the credential source that DataWorks uses to access MySQL. The following options are supported:

• Username and password: Enter the username and password for the MySQL database. DataWorks stores them with symmetric encryption. If you use an RDS instance, you can create and manage them in the account management section of the instance.

• Key Management Service: Use a generic secret hosted in Alibaba Cloud Key Management Service (KMS). You must first create a generic secret in KMS. For more information, see Manage and use generic secrets. Then, in the data source, select the Kms Region where the KMS generic secret resides, and select the target generic secret from the KMS List. The content of a KMS generic secret supports the following two JSON formats:

  { "username": "biz_rw", "password": "S3cr3t!" }
  { "AccessKeyId": "LTAI...", "AccessKeySecret": "..." }

  Note

  After the content of a KMS generic secret is changed, DataWorks caches the secret for up to 5 minutes. The new secret takes effect within 5 minutes at the latest.

Version

Log on to the MySQL server and run the SELECT VERSION() query to check the version number.

Authentication Method

Select no authentication or SSL authentication. If you select SSL authentication, ensure it is also enabled on the instance. Prepare the certificate file and upload it to Authentication File Management.

Advanced Parameters

Parameter: Select a supported parameter from the drop-down list, for example, connectTimeout.

Value: Enter an appropriate value for the selected parameter, for example, 3000.

The URL is then automatically updated to: jdbc:mysql://192.168.90.28:3306/test?connectTimeout=3000.

Parameter

Description

Required

Default

datasource

The name of the data source to read from. This name must match a data source configured in the code editor.

Yes

None

table

The name of the source table to synchronize. A Data Integration task can read data from only one logical table.

Yes

None

column

A JSON array that specifies the columns to read from the source table. To select all columns, use ["*"].

• Column pruning: You can export a subset of columns.

• Column reordering: You can export columns in an order that is different from the table schema.

• Constant values: You can configure constant values. The values must follow MySQL SQL syntax. Example: ["id","`table`","1","'mingya.wmy'","'null'","to_char(a+1)","2.3",true].

  • id is a regular column name.

  • `table` is a column name that is a reserved keyword.

  • 1 is an integer constant.

  • 'mingya.wmy' is a string constant. It must be enclosed in single quotation marks.

  • Handling null values:

    • " " represents a string containing a single space.

    • null represents a NULL value.

    • 'null' represents the literal string "null".

  • to_char(a+1) is a string conversion function.

  • 2.3 is a floating-point number.

  • true is a boolean value.

• You must explicitly specify the columns in the column parameter. It cannot be empty.

Yes

None

splitPk

When MySQL Reader extracts data, if you specify the splitPk parameter, it indicates that you want to use the field represented by splitPk for data sharding. Data Integration therefore launches concurrent tasks to perform data synchronization, which improves synchronization efficiency.

• For best performance, use the primary key of the table as the splitPk.

• Currently, splitPk supports data splitting for integer types only. It does not support other types such as string, floating-point, or date. If you specify an unsupported data type, the splitPk feature is ignored, and a single channel is used for synchronization.

• If you do not specify splitPk, that is, you do not provide splitPk or the splitPk value is empty, the data synchronization is treated as a single-channel synchronization for the table.

No

None

splitFactor

The sharding factor. This parameter controls how many shards are created. If concurrency is enabled, the data is split into concurrency × splitFactor shards. For example, if the concurrency is 5 and splitFactor is 5, the data is split into 25 shards that are processed by 5 concurrent threads.

No

5

where

A filter condition. In a typical scenario, to synchronize only the current day's data, you can set the where condition to gmt_create > $bizdate.

• The where clause allows you to effectively perform incremental synchronization. If you do not specify the where statement, including not providing a key or value for where, the data synchronization is treated as a full data synchronization.

• Do not use clauses like LIMIT 10, as they are not valid in a MySQL WHERE clause.

No

None

querySql (Available only in the code editor; not supported in the codeless UI.)

In some use cases, the where parameter is insufficient for describing the required filtering conditions. You can use this parameter to define a custom SQL query for filtering. After this parameter is configured, the data synchronization system ignores the tables, columns, and splitPk parameters and directly uses the content of this parameter to filter data. For example, if you need to synchronize data after a multi-table join, use select a,b from table_a join table_b on table_a.id = table_b.id. When you configure querySql, MySQL Reader directly ignores the configurations of the table, column, where, and splitPk conditions. The querySql parameter has a higher priority than the table, column, where, and splitPk options. The datasource is used to parse information such as the username and password.

No

None

useSpecialSecret

Specifies whether to use the password of each individual data source when you have multiple source data sources. Valid values:

• true

• false

If you configure multiple source data sources that have different usernames and passwords, set this parameter to true to use the credentials of each respective data source.

No

false

Parameter

Description

Required

Default

datasource

The name of the destination data source. This name must match a data source configured in the code editor.

Yes

None

table

The name of the destination table.

Yes

None

writeMode

The write mode. The supported options correspond to the MySQL INSERT INTO, ON DUPLICATE KEY UPDATE, and REPLACE INTO statements:

• insert: If a primary key or unique index conflict occurs, the task does not write the conflicting rows and records them as dirty data.

  If you configure the task in script mode, set writeMode to insert.

• on duplicate key update: If no conflict with a primary key or unique index occurs, the statement behaves the same as insert into. If a conflict occurs, the statement updates the values of the specified fields with the values from the new row and writes the data to MySQL.

  If you configure the task in script mode, set writeMode to update.

• replace into: When no conflict with a primary key or unique index occurs, it behaves the same as insert into. If a conflict occurs, it first deletes the original row and then inserts the new row. This means that the new row replaces all fields of the original row.

  If you configure the task in script mode, set writeMode to replace.

No

insert

nullMode

Specifies the policy for handling NULL values from the source. Valid values:

• writeNull: If a source field is NULL, a NULL value is written to the destination field.

• skipNull: If a source field is NULL, the destination field is omitted from the write operation, allowing the database's default value to be applied. If no default value is defined, the result is typically NULL. You must specify the applicable columns in the skipNullColumn parameter.

No

writeNull

skipNullColumn

When nullMode is set to skipNull, the columns specified by this parameter are not forced to NULL. Instead, their respective default values are used if available.

Format: ["c1","c2",...]. The specified columns such as c1 and c2 must be a subset of the columns defined in the column parameter.

No

All columns configured for the task.

column

The destination columns to write to, specified as a JSON array of strings. Example: "column":["id","name","age"]. To write to all columns in their schema order, use an asterisk (*). Example: "column":["*"].

Yes

None

preSql

One or more SQL statements to execute before the synchronization task starts. The codeless UI supports only one statement, while the code editor supports multiple. For example, you can clear existing data from a table: TRUNCATE TABLE tablename.

No

None

postSql

One or more SQL statements to execute after the synchronization task completes. The codeless UI supports only one statement, while the code editor supports multiple. For example, you can add a timestamp column by using the following statement: ALTER TABLE tablename ADD colname TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

No

None

batchSize

The number of records per write batch. Larger values reduce network round-trips and can improve throughput, but setting the value too high may cause an out-of-memory (OOM) error.

No

256

updateColumn

When writeMode is set to update, this parameter specifies which columns to update when a primary key or unique index conflict occurs. Specify the columns in a JSON array. Example: "updateColumn":["name","age"].

No

None