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 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 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 node 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 node 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.

Batch data read

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 node 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 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.

Develop a data synchronization node

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

Add a data source

Before you configure a data synchronization node 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 node to synchronize data of a single table

• For more information about the configuration procedure, see Configure a batch synchronization node by using the codeless UI and Configure a batch synchronization node 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 node, see Appendix: Code and parameters.

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

For more information about the configuration procedure, see Configure a real-time synchronization node 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 data synchronization solution in Data Integration.

FAQ

• When I run a real-time synchronization node 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 node that is used to synchronize data from MySQL slows down?
• How do I configure a batch synchronization node 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?

Appendix: Code and parameters

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

If you use the code editor to configure a batch synchronization node, 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 node 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 node is run. 
                "datasource":"",// The name of the data source. 
                "column":[// The names of the columns. 
                    "id",
                    "value"
                ],
                "writeMode":"insert into",// The write mode. Valid values: insert into, replace into, and on duplicate key update. 
                "batchSize":1024,// The number of data records to write at a time. 
                "table":"",// The name of the table. 
                "preSql":[ 
                     "delete from XXX;" // The SQL statement that you want to execute before the synchronization node 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. 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