MySQL data source

Real-time reading

• Synchronization of data from MySQL read-only instance is not supported.

• Synchronization of tables containing Functional index is not supported.

• XA ROLLBACK is not supported.

  For transaction data that has been XA PREPARE, 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 PREPARE 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 again and resynchronize.

• Only MySQL server Binlog configuration format set to ROW is supported.

• 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).

• Reading stored procedures from MySQL is not supported.

Offline reading

• When MySQL Reader plugin synchronizes multiple tables such as in sharding scenarios, if you want to split a single table, the number of concurrent tasks must be greater than the number of tables. Otherwise, the number of split tasks equals the number of tables.

• Reading stored procedures from MySQL is not supported.

Confirm MySQL version

Data Integration has requirements for MySQL versions. You can refer to the Supported MySQL versions section above to check if the 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 for accessing data sources 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 synchronized tables.

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

   • Real-time

     In real-time synchronization scenarios, this account needs to have SELECT, REPLICATION SLAVE, and 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 login from any host. % represents any host.
   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync account'@'%'; //Grant SELECT, REPLICATION SLAVE, REPLICATION CLIENT permissions on the database to the sync account.

   *.* means granting the above permissions to the sync account for all tables in all databases. You can also specify granting the above permissions to the sync account for specific tables in the target database. For example, to grant the above permissions to the sync account for the user table in the test database, 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 for specific tables in the target database.

(Required only for real-time synchronization) Enable MySQL binlog

Data Integration achieves real-time incremental data synchronization through real-time subscription to MySQL Binlog. You need to enable the MySQL Binlog service before configuring synchronization on 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, refer to the MySQL official documentation to enable Binlog.

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

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

2. Query the Binlog format.

   Use the following statement to query the Binlog format.

   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 server 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 that have enabled the complete Binlog log. If the query result returns non-FULL, please modify the binlog_row_image configuration.

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 document 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"//Number of error records.
      },
      "speed": {
        "throttle": true,//When throttle is false, the mbps parameter is ineffective, indicating no rate limiting; when throttle is true, it indicates rate limiting.
        "concurrent": 1,//Number of concurrent jobs.
        "mbps": "12"//Rate limit, 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 refers to 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 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": [],//Statements to execute 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,//Number of records in a single batch commit.
        "table": "",//Table name.
        "nullMode": "skipNull",//NULL value handling strategy.
        "skipNullColumn": [//Columns to skip NULL values.
          "id",
          "value"
        ],
        "preSql": [
          "delete from XXX;"//Statements to execute before import.
        ]
      },
      "name": "Writer",
      "category": "writer"
    }
  ],
  "setting": {
    "errorLimit": {//Number of error records.
      "record": "0"
    },
    "speed": {
      "throttle": true,//When throttle is false, the mbps parameter is ineffective, indicating no rate limiting; when throttle is true, it indicates rate limiting.
      "concurrent": 1,//Number of concurrent jobs.
      "mbps": "12"//Rate limit, controls the maximum 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