DataWorks:MySQL data source

Parameter

Description

Data Source Name

The name of the data source. The name must be unique within the workspace. Use a name that identifies the business and environment, such as rds_mysql_order_dev.

Configuration Mode

Select the Alibaba Cloud instance mode. For more information, see Scenario 1: Instance mode (current Alibaba Cloud account) and Scenario 2: Instance mode (other Alibaba Cloud accounts).

Alibaba Cloud Account

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

If you select Other Alibaba Cloud Account, you must specify the following information:

• Other Alibaba Cloud Account ID: The ID of the Alibaba Cloud account to which the instance belongs.

• RAM Role for Authorization: The RAM role provided by the other Alibaba Cloud account. This role must have the permissions to use the destination instance.

Region

The region where the instance is located.

Instance

The name of the instance to which you want to connect.

Read Replica Settings

If the data source has a read-only instance (read replica), you can select the read replica for read operations when you configure a task. This prevents interference with the primary database and does not affect its performance.

Instance Endpoint

After you select the correct instance, click Get Latest Endpoint to view the public or private endpoint, VPC, and vSwitch of the instance.

Database

The name of the database that the data source needs to access. Make sure that the specified user has the permissions to access the database.

Username/Password

The username and password of the MySQL database account. If you use an RDS instance, you can create and manage the account in the Account Management section of the instance.

Read OSS Binlog

If you enable this feature, when DataWorks cannot access the RDS binary logs, it attempts to obtain the binary logs from OSS. This prevents real-time synchronization tasks from being interrupted. For more information about the configuration, see Configure authorization for reading binary logs from OSS. Then, set OSS Binlog Access Identity based on the authorization configuration.

Authentication Options

You can select No Authentication or SSL Authentication. If you select SSL Authentication, SSL authentication must be enabled for the instance itself. Prepare the certificate file and upload it to Certificate File Management.

Version

You can log on to the MySQL server and run the `SELECT VERSION()` command to view the version number.

Parameter

Description

Data Source Name

The name of the data source. The name must be unique within the workspace. Use a name that identifies the business and environment, such as rds_mysql_order_dev.

Configuration Mode

Select Connection String Mode. This mode connects to the database using a JDBC URL.

Connection String Preview

After you enter the endpoint and database name, DataWorks automatically concatenates them into a JDBC URL for you to preview.

Endpoint

Host IP: The IP address or domain name of the database host. If the database is an Alibaba Cloud RDS instance, you can view the endpoint on the instance details page.

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

Database Name

The name of the database that the data source needs to access. Make sure that the specified user has the permissions to access the database.

Username/Password

The username and password of the MySQL database account. If you use an RDS instance, you can create and manage the account in the Account Management section of the instance.

Version

You can log on to the MySQL server and run the `SELECT VERSION()` command to view the version number.

Authentication Options

You can select No Authentication or SSL Authentication. If you select SSL Authentication, SSL authentication must be enabled for the instance itself. Prepare the certificate file and upload it to Certificate File Management.

Advanced Parameters

Parameter: Click the parameter drop-down list and select a supported parameter name, such as connectTimeout.

Value: Enter a value for the selected parameter, such as 3000.

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

Parameter

Description

Required

Default value

datasource

The name of the data source. The code editor supports adding data sources. The value of this parameter must be the same as the name of the added data source.

Yes

None

table

The name of the table from which to synchronize data. A data integration task can read data from only one table.

Yes

None

column

A collection of column names to be synchronized from the configured table. Use a JSON array to describe the field information. By default, all columns are configured, for example, [*].

• Column cropping: You can select a subset of columns to export.

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

• Constant configuration: You must follow the MySQL SQL syntax format. 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 word.

  • 1 is an integer constant.

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

  • About null:

    • " " indicates an empty string.

    • null indicates a null value.

    • 'null' indicates the string "null".

  • to_char(a+1) is a function to calculate the length of a string.

  • 2.3 is a floating-point number.

  • true is a Boolean value.

• The column parameter must explicitly specify the collection of columns to be synchronized. It cannot be empty.

Yes

None

splitPk

When MySQL Reader extracts data, specifying the splitPk parameter shards the data based on the field that splitPk represents. This allows data synchronization to run as concurrent tasks, which improves synchronization efficiency.

• We recommend that you use the primary key of the table for splitPk. The primary key is usually evenly distributed, which helps prevent data hot spots in the shards.

• Currently, splitPk supports sharding only for integer data types. It does not support strings, floating-point numbers, or dates. If you specify an unsupported data type, the splitPk function is ignored, and a single channel is used for synchronization.

• If you do not specify splitPk, which includes not providing splitPk or leaving the splitPk value empty, data synchronization uses a single channel to sync the table data.

No

None

where

The filter condition. In many business scenarios, you might want to synchronize only the data for the current day. You can set the where condition to gmt_create>$bizdate.

• The where condition can be used for efficient incremental synchronization. If you do not specify a where statement, or if the key or value of where is empty, all data is synchronized.

• Do not set the where condition to `limit 10`. This does not comply with the MySQL SQL WHERE clause constraints.

No

None

querySql (Advanced mode. This parameter is not supported in the codeless UI.)

In some business scenarios, the `where` parameter is not sufficient to describe the filter condition. You can use this parameter to define a custom filter SQL statement. If you configure this parameter, the system ignores the `tables`, `columns`, and `splitPk` parameters and uses the content of this parameter to filter data. For example, 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 ignores the `table`, `column`, `where`, and `splitPk` configurations. The querySql parameter has a higher priority than the table, column, where, and splitPk parameters. The datasource parameter is used to parse information such as the username and password.

No

None

useSpecialSecret

If you have multiple source data sources, specifies whether to use the password of each data source. Valid values:

• true

• false

If you have configured multiple source data sources and each data source uses a different username and password, you can set this parameter to true to use the password of each data source.

No

false

Parameter

Description

Required

Default value

datasource

The name of the data source. The code editor supports adding data sources. The value of this parameter must be the same as the name of the added data source.

Yes

None

table

The name of the table to which to synchronize data.

Yes

None

writeMode

The import mode. You can select insert into, on duplicate key update, or replace into.

• insert into: If a primary key or unique index conflict occurs, the conflicting row is not written and is treated as dirty data.

  If you configure the task in the code editor, set writeMode to insert.

• on duplicate key update: If no primary key or unique index conflict occurs, the behavior is the same as insert into. If a conflict occurs, the values of the specified fields in the existing row are replaced with the values from the new row.

  If you configure the task in the code editor, set writeMode to update.

• replace into: If no primary key or unique index conflict occurs, the behavior is the same as insert into. If a conflict occurs, the existing row is deleted, and the new row is inserted. The new row replaces all fields of the existing row.

  If you configure the task in the code editor, set writeMode to replace.

No

insert

nullMode

The policy for handling null values. Valid values:

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

• skipNull: If a source field has a NULL value, the destination field is not written. If the destination field has a default value defined, that default value is used. If no default value is defined, the value of the column is NULL. When you configure this parameter, you must also configure the skipNullColumn parameter.

No

writeNull

skipNullColumn

When nullMode is set to skipNull, the columns configured for this parameter are not forced to be written as NULL. The default value of the corresponding column is used with priority.

Format: ["c1","c2",...], where c1 and c2 must be a subset of the column parameter.

No

By default, all columns configured for the task.

column

The fields in the destination table to which data is to be written. Separate the fields with commas (,), for example, "column":["id","name","age"]. To write to all columns in order, use an asterisk (*), for example, "column":["*"].

Yes

None

preSql

The SQL statement to be executed before the data synchronization task starts. In the codeless UI, you can execute only one SQL statement. In the code editor, you can execute multiple SQL statements. For example, you can clear old data from the table before execution: `truncate table tablename`.

No

None

postSql

The SQL statement to be executed after the data synchronization task is complete. In the codeless UI, you can execute only one SQL statement. In the code editor, you can execute multiple SQL statements. For example, you can add a timestamp: ALTER TABLE tablename ADD colname TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

No

None

batchSize

The number of records to be submitted in a single batch. A larger value can significantly reduce network interactions between the data synchronization system and MySQL and improve overall throughput. If this value is set too high, it may cause an out-of-memory (OOM) error in the data synchronization process.

No

256

updateColumn

When writeMode is set to update, these are the fields to be updated when a primary key or unique index conflict occurs. Separate the fields with commas (,), for example, "updateColumn":["name","age"].

No

None