DataWorks:MySQL data source

Real-time reading

• Does not support synchronizing data from MySQL read-only instances.

• Does not support synchronizing tables with Functional index.

• Does not support XA ROLLBACK.

  For transaction data that has already been XA PREPARED, real-time synchronization will synchronize it to the target end. If XA ROLLBACK occurs, real-time synchronization will not perform rollback write operations for the XA PREPARED data. To handle XA ROLLBACK scenarios, you need to manually remove the XA ROLLBACK table from the real-time synchronization task, then add the table and resynchronize.

• Only supports synchronizing MySQL servers with Binlog configuration format set to ROW.

• Real-time synchronization will not synchronize associated table records that are cascade deleted.

• For Amazon Aurora MySQL databases, you need to connect to your primary/write database because AWS does not allow activating the Binlog feature on Aurora MySQL read replicas. Real-time synchronization tasks require Binlog to perform incremental updates.

• Real-time synchronization of online DDL changes only supports adding columns (Add Column) to MySQL tables through Data Management DMS.

• Does not support reading stored procedures from MySQL.

Offline reading

• When MySQL Reader plugin performs multi-table synchronization such as sharding, if you want to split a single table, the number of task concurrency must be greater than the number of tables. Otherwise, the number of split tasks equals the number of tables.

• Does not support reading stored procedures from MySQL.

Confirm MySQL version

Data Integration has requirements for MySQL versions. You can refer to the Supported MySQL versions section above to check if your MySQL to be synchronized meets the version requirements. You can check the current MySQL database version using the following statement in the MySQL database.

SELECT version();

Configure account permissions

It is recommended that you plan and create a MySQL account dedicated to DataWorks data source access in advance. The operation is as follows.

1. Optional: Create an account.

   For operation details, see Create MySQL account.

2. Configure permissions.

   • Offline

     In offline synchronization scenarios:

     • When reading MySQL data offline, this account needs to have read (SELECT) permission for the synchronization table.

     • When writing MySQL data offline, this account needs to have write (INSERT, DELETE, UPDATE) permissions for the synchronization table.

   • Real-time

     In real-time synchronization scenarios, this account needs to have SELECT, REPLICATION SLAVE, REPLICATION CLIENT permissions for the database.

   You can refer to the following commands to add permissions to the account, or directly grant the account SUPER permission. When using the following execution statements, please replace 'sync account' with the account created above.

   -- CREATE USER 'sync account'@'%' IDENTIFIED BY 'password'; //Create a sync account and set a password to allow it to log in to the database from any host. % represents any host.
   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync account'@'%'; //Grant the sync account SELECT, REPLICATION SLAVE, REPLICATION CLIENT permissions on the database.

   *.* means granting the sync account the above permissions on all tables in all databases. You can also specify granting the sync account the above permissions on specific tables in the target database. For example, to grant the sync account the above permissions on the test database's user table, you can use the statement GRANT SELECT, REPLICATION CLIENT ON test.user TO 'sync account'@'%';.

   Note

   The REPLICATION SLAVE statement is a global permission and cannot be specified to grant the sync account permissions on specific tables in the target database.

(Only required for real-time synchronization) Enable MySQL binlog

Data Integration implements real-time incremental data synchronization through real-time subscription to MySQL Binlog. You need to enable the MySQL Binlog service before configuring synchronization in DataWorks. The operation is as follows:

1. Check if Binlog is enabled.

   • Use the following statement to check if Binlog is enabled.

     SHOW variables LIKE "log_bin";

     When the return result is ON, it indicates that Binlog is enabled.

   • If you use a standby database to synchronize data, you can also check if Binlog is enabled using the following statement.

     SHOW variables LIKE "log_slave_updates";

     When the return result is ON, it indicates that Binlog is enabled on the standby database.

   If the returned result does not match the above results:

   • For open-source MySQL, please refer to the MySQL official documentation to enable Binlog.

   • For Alibaba Cloud RDS MySQL, please refer to RDS MySQL log backup to enable Binlog.

   • For Alibaba Cloud PolarDB MySQL, please refer to Enable Binlog to enable Binlog.

2. Query the Binlog format being used.

   Use the following statement to query the Binlog format being used.

   SHOW variables LIKE "binlog_format";

   Explanation of return results:

   • Returns ROW, indicating that the enabled Binlog format is ROW.

   • Returns STATEMENT, indicating that the enabled Binlog format is STATEMENT.

   • Returns MIXED, indicating that the enabled Binlog format is MIXED.

   Important

   DataWorks real-time synchronization only supports synchronizing MySQL servers with Binlog configuration format set to ROW. If the return is not ROW, please modify the Binlog Format.

3. Check if the complete Binlog log is enabled.

   Use the following statement to check if the complete Binlog log is enabled.

   SHOW variables LIKE "binlog_row_image";

   Explanation of return results:

   • Returns FULL, indicating that the complete log is enabled for Binlog.

   • Returns MINIMAL, indicating that the minimal log is enabled for Binlog, and the complete log is not enabled.

   Important

   DataWorks real-time synchronization only supports synchronizing data from MySQL servers with complete Binlog logs enabled. If the query result returns non-FULL, please modify the binlog_row_image configuration.

Please select the MySQL connection mode according to your network environment:

Configure a batch synchronization task by using the code editor

If you want to configure a batch synchronization task by using the code editor, you must configure the related parameters in the script based on the unified script format requirements. For more information, see Configure a batch synchronization task by using the code editor. The following information describes the parameters that you must configure for data sources when you configure a batch synchronization task by using the code editor.

Reader script demo

This article provides configuration examples for single database single table and sharding:

• Configure single database single table

  {
    "type": "job",
    "version": "2.0",//Version number.
    "steps": [
      {
        "stepType": "mysql",//Plugin name.
        "parameter": {
          "column": [//Column names.
            "id"
          ],
          "connection": [
            {
              "querySql": [
                "select a,b from join1 c join join2 d on c.id = d.id;"
              ],
              "datasource": ""//Data source name.
            }
          ],
          "where": "",//Filter condition.
          "splitPk": "",//Split key.
          "encoding": "UTF-8"//Encoding format.
        },
        "name": "Reader",
        "category": "reader"
      },
      {
        "stepType": "stream",
        "parameter": {},
        "name": "Writer",
        "category": "writer"
      }
    ],
    "setting": {
      "errorLimit": {
        "record": "0"//Error record count.
      },
      "speed": {
        "throttle": true,//When throttle value is false, mbps parameter is not effective, meaning no flow control; when throttle value is true, it means flow control.
        "concurrent": 1,//Job concurrency.
        "mbps": "12"//Flow control, here 1mbps = 1MB/s.
      }
    },
    "order": {
      "hops": [
        {
          "from": "Reader",
          "to": "Writer"
        }
      ]
    }
  }

• Configure sharding

  Note

  Sharding refers to the ability to select multiple MySQL data tables on the MySQL Reader side, with consistent table structures. The 'sharding' here means multiple MySQL tables writing to the same target table. If you want to support database-level sharding configuration, please create a task on the Data Integration site and select the whole database sharding capability

  {
    "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 script parameters

Writer script demo

{
  "type": "job",
  "version": "2.0",//Version number.
  "steps": [
    {
      "stepType": "stream",
      "parameter": {},
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "mysql",//Plugin name.
      "parameter": {
        "postSql": [],//Preparation statement after import.
        "datasource": "",//Data source.
        "column": [//Column names.
          "id",
          "value"
        ],
        "writeMode": "insert",//Write mode, you can set it to insert, replace, or update.
        "batchSize": 1024,//Batch size for one-time batch submission of records.
        "table": "",//Table name.
        "nullMode": "skipNull",//NULL value handling strategy.
        "skipNullColumn": [//Columns that need to skip NULL values.
          "id",
          "value"
        ],
        "preSql": [
          "delete from XXX;"//Preparation statement before import.
        ]
      },
      "name": "Writer",
      "category": "writer"
    }
  ],
  "setting": {
    "errorLimit": {//Error record count.
      "record": "0"
    },
    "speed": {
      "throttle": true,//When throttle value is false, mbps parameter is not effective, meaning no flow control; when throttle value is true, it means flow control.
      "concurrent": 1,//Job concurrency.
      "mbps": "12"//Flow control, controls the highest rate of synchronization to prevent excessive read/write pressure on upstream/downstream databases, here 1mbps = 1MB/s.
    }
  },
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  }
}

Writer script parameters