DataWorks:MySQL

Supported versions

Both offline and real-time synchronization support MySQL 5.5.x, 5.6.x, 5.7.x, and 8.0.x. For MySQL 8.0.x, real-time synchronization supports all features except new 8.0-specific features such as functional indexes.

Supported instance types

For Amazon Aurora MySQL, connect real-time synchronization tasks to the primary (writer) instance. AWS does not allow enabling binary logging on Aurora MySQL read replicas, which real-time synchronization requires for incremental updates.

Offline synchronization also supports reading data from views.

Real-time synchronization

• Read-only instances: Synchronizing data from MySQL read-only instances earlier than version 5.6.x is not supported.

• Functional indexes: Synchronizing tables that contain functional indexes is not supported.

• XA transactions: XA ROLLBACK is not supported. Data in the XA PREPARE state is written to the destination, but if an XA ROLLBACK occurs, real-time synchronization does not roll back that data. To handle this, remove the affected table from the real-time synchronization task and add it back to restart synchronization.

• Binary log format: Only the ROW binary log format is supported. STATEMENT and MIXED formats are not supported.

• Cascade deletes: Records deleted by a cascade delete operation in associated tables are not synchronized.

• Online DDL: Only adding columns (Add Column) via Data Management (DMS) is supported for online DDL operations.

• Stored procedures: Reading stored procedures from MySQL is not supported.

Offline synchronization

• Sharded databases: When synchronizing from multiple tables in a sharded database, the number of sharded tasks equals the number of tables by default. To enable sharding for a single table, set the number of concurrent tasks to a value greater than the number of tables.

• Stored procedures: Reading stored procedures from MySQL is not supported.

Step 1: Confirm the MySQL version

Run the following statement to check your MySQL version:

SELECT version();

See the Compatibility section to confirm your version is supported.

Step 2: Configure account permissions

Create a dedicated MySQL account for DataWorks. Grant the account the minimum permissions required for your synchronization type.

Offline synchronization:

• Read: SELECT permission on the target tables

• Write: INSERT, DELETE, and UPDATE permissions on the target tables

Real-time synchronization:

The account needs SELECT, REPLICATION SLAVE, and REPLICATION CLIENT permissions on the database. Run the following command to grant permissions (replace sync_account with your account name):

-- Create the sync account (optional if the account already exists)
-- CREATE USER 'sync_account'@'%' IDENTIFIED BY 'password';

-- Grant permissions
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync_account'@'%';

*.* grants the permissions on all tables in all databases. To grant permissions on specific tables, use a targeted statement instead. For example:

GRANT SELECT, REPLICATION CLIENT ON test.user TO 'sync_account'@'%';

REPLICATION SLAVE is a global permission and cannot be granted on a specific table.

Step 3: Enable binary logging (real-time synchronization only)

Data Integration performs real-time synchronization by subscribing to MySQL binary logs. Complete the following checks before configuring a synchronization task.

Check 1: Confirm binary logging is enabled

SHOW variables LIKE "log_bin";

If the result is ON, binary logging is enabled. For a secondary database, also run:

SHOW variables LIKE "log_slave_updates";

If binary logging is not enabled, enable it based on your MySQL type:

• Open source MySQL: see the official MySQL documentation

• ApsaraDB RDS for MySQL: see RDS for MySQL log backup

• PolarDB for MySQL: see Enable binary logging

Check 2: Confirm the binary log format is ROW

SHOW variables LIKE "binlog_format";

Real-time synchronization requires the ROW format. If the result is STATEMENT or MIXED, update the binlog_format configuration.

Check 3: Confirm full row image logging is enabled

SHOW variables LIKE "binlog_row_image";

Real-time synchronization requires the FULL row image. If the result is MINIMAL, update the binlog_row_image configuration.

Step 4 (optional): Authorize binary log access from OSS

This step applies only when all of the following are true:

• You are adding the data source in Alibaba Cloud instance mode

• Your RDS for MySQL instance is in the same region as the DataWorks workspace

• You plan to enable Enable binary log reading from OSS

When enabled, if DataWorks cannot access the RDS binary logs directly, it falls back to obtaining them from OSS, preventing real-time synchronization task interruptions.

If you set Identity to access binary logs from OSS to Alibaba Cloud RAM user, configure the RAM user as follows:

1. Log on to the RAM console and find the RAM user.

2. In the Actions column, click Add permissions.

3. Set Resource scope to Account level, set Policy to System policy, and set Policy name to AliyunDataWorksAccessingRdsOSSBinlogPolicy. Click Grant permissions.

If you set Identity to access binary logs from OSS to Alibaba Cloud RAM role, configure the RAM role as follows:

1. Log on to the RAM console and create a RAM role for a trusted Alibaba Cloud account with these parameters:

   • Principal type: Alibaba Cloud Account

   • Principal name: Other Alibaba Cloud Account — enter the ID of the Alibaba Cloud account that owns the DataWorks workspace

   • Role name: A custom name of your choice

2. Grant permissions to the RAM role with these parameters:

   • Policy: System policy

   • Policy name: AliyunDataWorksAccessingRdsOSSBinlogPolicy

3. Modify the trust policy of the RAM role to the following:

{
    "Statement": [
        {
            "Action": "sts:AssumeRole",
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "<Alibaba_Cloud_account_ID_of_the_DataWorks_user>@di.dataworks.aliyuncs.com",
                    "<Alibaba_Cloud_account_ID_of_the_DataWorks_user>@dataworks.aliyuncs.com"
                ]
            }
        }
    ],
    "Version": "1"
}

Alibaba Cloud instance mode

Use this mode if your MySQL database is an ApsaraDB RDS for MySQL instance.

Connection string mode

Use this mode to connect using a JDBC URL, which works for any MySQL-compatible database.

Offline synchronization (single table)

• Codeless UI: Configure an offline synchronization task in the codeless UI

• Code editor: Configure an offline synchronization task in the code editor

For a complete parameter reference and script examples, see Appendix: Script examples and parameter reference.

Real-time synchronization (single table)

Configure a real-time synchronization task in DataStudio

Database-level synchronization (offline, full + incremental, and sharded)

Configure a synchronization task in Data Integration

Reader script examples

The comments in the following JSON examples are for demonstration purposes only. Remove comments before using the script.

Single table in a single database

{
  "type": "job",
  "version": "2.0",
  "steps": [
    {
      "stepType": "mysql",
      "parameter": {
        "column": [
          "id"
        ],
        "connection": [
          {
            "querySql": [
              "select a,b from join1 c join join2 d on c.id = d.id;"
            ],
            "datasource": ""
          }
        ],
        "where": "",
        "splitPk": "",
        "encoding": "UTF-8"
      },
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "stream",
      "parameter": {},
      "name": "Writer",
      "category": "writer"
    }
  ],
  "setting": {
    "errorLimit": {
      "record": "0"
    },
    "speed": {
      "throttle": true,
      "concurrent": 1,
      "mbps": "12"
    }
  },
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  }
}

Sharded tables

The MySQL Reader supports reading from multiple tables with the same schema across different data sources and writing to a single destination table. To configure database-level sharding, create a task in Data Integration and select the database-level sharding feature.

{
  "type": "job",
  "version": "2.0",
  "steps": [
    {
      "stepType": "mysql",
      "parameter": {
        "indexes": [
          {
            "type": "unique",
            "column": [
              "id"
            ]
          }
        ],
        "envType": 0,
        "useSpecialSecret": false,
        "column": [
          "id",
          "buyer_name",
          "seller_name",
          "item_id",
          "city",
          "zone"
        ],
        "tableComment": "Test order table",
        "connection": [
          {
            "datasource": "rds_dataservice",
            "table": [
              "rds_table"
            ]
          },
          {
            "datasource": "rds_workshop_log",
            "table": [
              "rds_table"
            ]
          }
        ],
        "where": "",
        "splitPk": "id",
        "encoding": "UTF-8"
      },
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "odps",
      "parameter": {},
      "name": "Writer",
      "category": "writer"
    },
    {
      "name": "Processor",
      "stepType": null,
      "category": "processor",
      "copies": 1,
      "parameter": {
        "nodes": [],
        "edges": [],
        "groups": [],
        "version": "2.0"
      }
    }
  ],
  "setting": {
    "executeMode": null,
    "errorLimit": {
      "record": ""
    },
    "speed": {
      "concurrent": 2,
      "throttle": false
    }
  },
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  }
}

Reader parameters

Writer script example

{
  "type": "job",
  "version": "2.0",
  "steps": [
    {
      "stepType": "stream",
      "parameter": {},
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "mysql",
      "parameter": {
        "postSql": [],
        "datasource": "",
        "column": [
          "id",
          "value"
        ],
        "writeMode": "insert",
        "batchSize": 1024,
        "table": "",
        "nullMode": "skipNull",
        "skipNullColumn": [
          "id",
          "value"
        ],
        "preSql": [
          "delete from XXX;"
        ]
      },
      "name": "Writer",
      "category": "writer"
    }
  ],
  "setting": {
    "errorLimit": {
      "record": "0"
    },
    "speed": {
      "throttle": true,
      "concurrent": 1,
      "mbps": "12"
    }
  },
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  }
}

Writer parameters

Write mode behavior