DataWorks:MySQL data source

Supported MySQL versions

• Batch data read and write:

  MySQL 5.5.X, MySQL 5.6.X, MySQL 5.7.X, and MySQL 8.0.X. Amazon RDS for MySQL, Azure MySQL, and Amazon Aurora MySQL data sources are compatible.

  Data of views can be read during batch synchronization.

• Real-time data read:

  Real-time synchronization of data from MySQL is performed based on real-time subscription to MySQL binary logs and is supported only in MySQL 5.5.X, MySQL 5.6.X, MySQL 5.7.X, and MySQL 8.0.X. The real-time synchronization feature is incompatible with new features in MySQL 8.0.X, such as functional index. Amazon RDS for MySQL, Azure MySQL, and Amazon Aurora MySQL data sources are compatible.

  Important

  DRDS data sources that run MySQL cannot be configured as MySQL data sources. If you want to synchronize data from a DRDS data source that runs MySQL in real time, you can refer to the Add a DRDS data source topic to add a DRDS data source and configure a real-time synchronization task for the data source.

Limits

Real-time data read

• Data Integration does not support real-time synchronization of data from a read-only MySQL instance by using MySQL Reader.

• Data Integration does not support real-time synchronization of data from tables that contain functional indexes.

• Data Integration does not support real-time synchronization of data on which XA ROLLBACK statements are executed.

  For transaction data on which XA PREPARE statements are executed, you can use the real-time synchronization feature to synchronize the data to a destination. If XA ROLLBACK statements are executed later on the data, the rollback changes to the data cannot be synchronized to the destination. If the tables that you want to synchronize include tables on which XA ROLLBACK statements are executed, you must remove the tables on which XA ROLLBACK statements are executed from the real-time synchronization task and add the removed tables back to synchronize data.

• Real-time synchronization of data from MySQL supports only binary logs in the row format.

• Data Integration does not support real-time synchronization of data records in associated tables on which the cascade delete operation is performed.

• If you want to read data from an Amazon Aurora MySQL database, you must connect the Amazon Aurora MySQL database to your primary database for data writing. This is because AWS does not allow you to enable the binary logging feature on read-only replicas of Amazon Aurora MySQL. The binary logging feature is required for real-time synchronization tasks to synchronize incremental data.

• Among all online DDL operations on a MySQL table, only the Add Column operation that is performed by using Data Management (DMS) can be synchronized in real time.

Batch data read

When you use MySQL Reader to synchronize data from MySQL in multi-table synchronization scenarios such as sharding, if you want to split tables, the number of parallel threads that you specified must be greater than the number of the tables. If the number of parallel threads that you specified is equal to or lower than the number of the tables, the tables are not split, and the number of parallel threads that are actually used is the same as the number of tables to be synchronized.

Supported data types

Prepare a MySQL environment before data synchronization

Before you use DataWorks to synchronize data from or to a MySQL data source, you must prepare a MySQL environment. This ensures that a data synchronization task can be configured and can synchronize data from or to the MySQL data source as expected. The following information describes how to prepare a MySQL environment for data synchronization from or to a MySQL data source.

Preparation 1: Check the version of your MySQL database

SELECT version();

Preparation 2: Prepare an account that has the required permissions

We recommend that you plan and create an account for DataWorks to access your MySQL database. To prepare such an account, perform the following steps:

1. Optional. Create an account.

   For more information, see Create an account to access a MySQL database.

2. Grant the required permissions to the account.

   • Batch synchronization

     Different permissions are required for batch data read and batch data write:

     • Batch data read: The account must have the SELECT permission.

     • Batch data write: The account must have the INSERT, DELETE, and UPDATE permissions.

   • Real-time synchronization

     The account must have the SELECT, REPLICATION SLAVE, and REPLICATION CLIENT permissions on your MySQL database.

   You can execute the following statement to grant permissions to the account. Alternatively, you can grant the SUPER permission to the account. Replace Account for data synchronization in the statement with the created account.

   -- CREATE USER 'Account for data synchronization'@'%' IDENTIFIED BY 'Password'; //Create an account that can be used for data synchronization and specify a password. This way, you can use the account and password to access the database from any host. % indicates a host. 
   GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'Account for data synchronization'@'%'; // Grant the SELECT, REPLICATION SLAVE, and REPLICATION CLIENT permissions to the account.

   *.* indicates that the account is granted the preceding permissions on all tables in all databases. You can also grant the preceding permissions on specific tables in a database to the account. For example, to grant the account the preceding permissions on the user table in the test database, execute the following statement: GRANT SELECT, REPLICATION CLIENT ON test.user TO 'Account for data synchronization'@'%';.

   Note

   The REPLICATION SLAVE permission is a global permission. You cannot grant this permission on specific tables in a database to the account.

Preparation 3: Enable the binary logging feature only in real-time synchronization scenarios

1. Check whether the binary logging feature is enabled.

   • Execute the following statement to check whether the binary logging feature is enabled:

     SHOW variables like "log_bin";

     If ON is displayed in the returned result, the binary logging feature is enabled.

   • If you want to synchronize incremental data from a secondary MySQL database, execute the following statement to check whether the binary logging feature is enabled for the secondary MySQL database:

     SHOW variables LIKE "log_slave_updates";

     If ON is displayed in the returned result, the binary logging feature is enabled for the secondary MySQL database.

   If ON is not displayed in the returned result:

   • Open source MySQL: Follow the instructions that are described in the official MySQL documentation to enable the binary logging feature.

   • ApsaraDB RDS for MySQL: Follow the instructions that are described in Use the log backup feature for an ApsaraDB RDS for MySQL instance to enable the binary logging feature.

   • PolarDB for MySQL: Follow the instructions that are described in Enable binary logging to enable the binary logging feature.

2. Query the format of binary logs.

   Execute the following statement to query the format of binary logs:

   SHOW variables LIKE "binlog_format";

   The system may return one of the following results:

   • ROW: The format of binary logs is row.

   • STATEMENT: The format of binary logs is statement.

   • MIXED: The format of binary logs is mixed.

   Important

   Real-time synchronization of data from MySQL supports only binary logs in the row format. If the format of binary logs is not row, change the format to row.

3. Query the setting of the binlog_row_image parameter.

   Execute the following statement to query the setting of the binlog_row_image parameter.

   show variables like "binlog_row_image";

   The system may return one of the following results:

   • FULL: logs all columns in both the before image and the after image.

   • MINIMAL: logs only specific columns in the before image or in the after image.

   Important

   Real-time synchronization of data from MySQL supports only the FULL value specified for the binlog_row_image parameter. If the value of the binlog_row_image parameter is not FULL, change the value to FULL.

Authorization for reading binary logs from OSS

When you add a MySQL data source, you can turn on Enable Binary Log Reading from OSS if you set the Configuration Mode parameter to Alibaba Cloud Instance Mode and set the Region parameter to the region in which the current DataWorks workspace resides. After you turn on this switch, DataWorks attempts to obtain binary logs from Object Storage Service (OSS) when it cannot read binary logs from ApsaraDB RDS for MySQL. This prevents real-time synchronization tasks from being interrupted.

If you set the Identity to Access Binary Logs from OSS parameter to Alibaba Cloud RAM User or Alibaba Cloud RAM Role, you must also perform the following operations to grant permissions to the RAM user or RAM role.

Develop a data synchronization task

For information about the entry point for and the procedure of configuring a data synchronization task, see the following sections. For information about the parameter settings, view the infotip of each parameter on the configuration tab of the task.

Add a data source

Before you configure a data synchronization task to synchronize data from or to a specific data source, you must add the data source to DataWorks. For more information, see Add and manage data sources.

Configure a batch synchronization task to synchronize data of a single table

• For more information about the configuration procedure, see Configure a batch synchronization task by using the codeless UI and Configure a batch synchronization task by using the code editor.

• For information about all parameters that are configured and the code that is run when you use the code editor to configure a batch synchronization task, see Appendix: Code and parameters.

Configure a real-time synchronization task to synchronize data of a single table

For more information about the configuration procedure, see Configure a real-time synchronization task in DataStudio.

Configure synchronization settings to implement batch synchronization of all data in a database, real-time synchronization of full data or incremental data in a database, and real-time synchronization of data from sharded tables in a sharded database

For more information about the configuration procedure, see Configure a synchronization task in Data Integration.

FAQ

• When I run a real-time synchronization task to synchronize data from MySQL, data can be read at the beginning but cannot be read after a period of time. What do I do?

• What do I do if an error is returned during the real-time synchronization of data from MySQL?

• Why does my real-time synchronization task that is used to synchronize data from MySQL slows down?

• How do I configure a batch synchronization task to synchronize data from tables in sharded MySQL databases to the same MaxCompute table?

• What do I do if the Chinese characters that are synchronized to a MySQL table contain garbled characters because the encoding format of the related MySQL data source is utf8mb4?

• How do I handle a dirty data error that is caused by encoding format configuration issues or garbled characters?

• What do I do if a batch synchronization task runs for an extended period of time?

• What do I do if the error message "Application was streaming results when the connection failed. Consider raising value of 'net_write_timeout/net_read_timeout,' on the server" is returned when I run a batch synchronization task to read data from or write data to ApsaraDB RDS for MySQL?

For information about other common issues in Data Integration, see FAQ about Data Integration.

Appendix: Code and parameters

Appendix: Configure a batch synchronization task by using the code editor

If you use the code editor to configure a batch synchronization task, you must configure parameters for the reader and writer of the related data source based on the format requirements in the code editor. For more information about the format requirements, see Configure a batch synchronization task by using the code editor. The following information describes the configuration details of parameters for the reader and writer in the code editor.

Code for MySQL Reader

Parameters in code for MySQL Reader

Code for MySQL Writer

{
  "type": "job",
  "version": "2.0",// The version number. 
  "steps": [
    {
      "stepType": "stream",
      "parameter": {},
      "name": "Reader",
      "category": "reader"
    },
    {
      "stepType": "mysql",// The plug-in name. 
      "parameter": {
        "postSql": [],// The SQL statement that you want to execute after the synchronization task is run. 
        "datasource": "",// The name of the data source. 
        "column": [// The names of the columns. 
          "id",
          "value"
        ],
        "writeMode": "insert",// The write mode. Valid values: insert, replace, and update. 
        "batchSize": 1024,// The number of data records to write at a time. 
        "table": "",// The name of the table. 
        "nullMode": "skipNull",// The policy for processing a NULL value. 
        "skipNullColumn": [// Specifies the columns in which NULL values need to be skipped. 
          "id",
          "value"
        ],
        "preSql": [
          "delete from XXX;"// The SQL statement that you want to execute before the synchronization task is run. 
        ]
      },
      "name": "Writer",
      "category": "writer"
    }
  ],
  "setting": {
    "errorLimit": {// The maximum number of dirty data records allowed. 
      "record": "0"
    },
    "speed": {
      "throttle": true,// Specifies whether to enable throttling. The value false indicates that throttling is disabled, and the value true indicates that throttling is enabled. The mbps parameter takes effect only when the throttle parameter is set to true. 
      "concurrent": 1,// The maximum number of parallel threads. 
      "mbps": "12"// The maximum transmission rate. Unit: MB/s. You can specify a maximum transmission rate to prevent heavy read workloads on the source or heavy write workloads on the destination. 
    }
  },
  "order": {
    "hops": [
      {
        "from": "Reader",
        "to": "Writer"
      }
    ]
  }
}

Parameters in code for MySQL Writer