Data Transmission Service:Synchronize data from an RDS for MySQL instance to a Function Compute function

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB RDS for MySQL instance as the source

• A Function Compute service and function created, with Handler Type set to Event Handler — see Quickly create a function

• Read permissions on the objects to be synchronized granted to the source database account

If the source database account was not created in the RDS for MySQL console, the account must have the REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, and SELECT permissions. To create and authorize an account, see Create an account and Modify account permissions.

Billing

Supported SQL operations

Limitations

Source database requirements:

• Tables must have primary keys or UNIQUE constraints, and all fields must be unique.

• A single data entry cannot exceed 16 MB. Entries exceeding this limit cannot be written to the destination function and cause an error. Use the extract, transform, and load (ETL) feature to filter out large objects.

• When selecting tables (not an entire database) as synchronization objects, a single task supports up to 1,000 tables. If this limit is exceeded, split the tables across multiple tasks or configure a task to synchronize the entire database.

• Binary logging requirements:

  • ApsaraDB RDS for MySQL: Binary logging is enabled by default. Set the binlog_row_image parameter to full. For instructions, see Modify instance parameters.

  • Self-managed MySQL: Enable binary logging, set binlog_format to row, and set binlog_row_image to full. For a dual-primary cluster, also set log_slave_updates to ON. For instructions, see Create an account for a self-managed MySQL database and configure binary logging.

  • Binary log retention: Retain binary logs for at least 3 days on ApsaraDB RDS for MySQL (7 days recommended), and at least 7 days on self-managed MySQL. If the retention period is too short, the synchronization task may fail and data inconsistency or loss can occur. To set the retention period for ApsaraDB RDS for MySQL, see the Delete binary log files section.

• Do not perform DDL operations to change database or table schemas during full data synchronization. DDL operations during this phase fail the task.

• Data changes generated by physical backup restores or cascade operations are not recorded and are not synchronized to the destination.

  If such data is not synchronized, you can remove the affected databases and tables from the synchronization objects and then add them back, provided your business is not affected. See Modify the objects to be synchronized.

• MySQL 8.0.23 and later: Invisible columns cannot be read, causing data loss. To make a column visible, run ALTER TABLE <table_name> ALTER COLUMN <column_name> SET VISIBLE;. See Invisible Columns. Tables without primary keys automatically generate invisible primary keys; make these visible before synchronizing. See Generated Invisible Primary Keys.

Other limitations:

• Object mapping is not supported.

• Cross-region data synchronization is not supported.

• During full data synchronization, DTS uses read and write resources of the source and destination. Run full data synchronization during off-peak hours to reduce database load.

• During full data synchronization, DTS performs concurrent INSERTs to the FC function. Make sure the function's instance concurrency is not set too low. See Configure instance concurrency.

• If you select tables (not an entire database) as synchronization objects, do not use pt-online-schema-change for online DDL operations during synchronization. Use Data Management (DMS) instead. See Perform lock-free schema changes.

• Do not synchronize multiple DTS tasks to the same destination function. Use separate functions for separate tasks to keep data isolated.

• If the always-confidential (EncDB) feature is enabled on the RDS for MySQL instance, full data synchronization is not supported.

  If Transparent Data Encryption (TDE) is enabled instead of EncDB, full and incremental synchronization are both supported.

• If a DTS task fails, DTS technical support will attempt to restore it within 8 hours. During restoration, the task may be restarted and task parameters may be modified. Database parameters are not modified. Parameters that may be modified are listed in Modify the parameters of a DTS instance.

Special cases:

• Self-managed MySQL:

  • A primary/secondary switchover while the synchronization task is running causes the task to fail.

  • If no DML operations are performed on the source database for a long time, the reported synchronization latency may be inaccurate. Perform a DML operation on the source to refresh the latency. If you select an entire database as the synchronization object, create a heartbeat table that is updated every second.

  • DTS periodically executes CREATE DATABASE IF NOT EXISTS 'test' on the source to advance the binary log file position.

• ApsaraDB RDS for MySQL:

  • A read-only ApsaraDB RDS for MySQL 5.6 instance that does not record transaction logs cannot be used as the source.

  • DTS periodically executes CREATE DATABASE IF NOT EXISTS 'test' on the source to advance the binary log file position.

Create a synchronization task

Step 3: Configure synchronization objects

In the Configure Objects step, configure the following settings:

Setting	Description
Synchronization Types	Incremental Data Synchronization is selected by default. Select Full Data Synchronization as well to synchronize existing data.
Data Format	The format for data synchronized to the FC function. Only Canal Json is supported. For field descriptions and examples, see Canal Json format.
Source Objects	Select databases or tables from the Source Objects section, then click the icon to move them to Selected Objects.
Selected Objects	Review the selected objects. To remove an object, select it and click .

Data format received by the function

FC receives data as an Object. Incremental data arrives in the Records array, where each element is an Object representing one data record.

FC receives two types of events:

• DML: data modification operations such as INSERT, UPDATE, and DELETE

• DDL: schema changes such as ALTER TABLE

ALTER TABLE `demoTable`
    ADD COLUMN `address` varchar(20) NULL AFTER `sex`;

{
    "Records": [{
        "type": "DDL",
        "serverId": "27142679",
        "es": 1690000000000,
        "sql": "/* Query from DMS-WEBSQL-0-Eid_15682857722282385K by user 14xxxxxxxx */ ALTER TABLE `demoDatabase`.`DDL` \n\tADD COLUMN `address` varchar(20) NULL AFTER `sex`",
        "database": "demoDatabase",
        "id": 63151,
        "isDdl": true,
        "table": "demoTable",
        "ts": 1690000000000
    }]
}

INSERT INTO demoTable VALUES("xiaoming", 10, "man");

{
    "Records": [{
        "data": [{
            "sex": "man",
            "name": "xiaoming",
            "age": "10"
        }],
        "pkNames": ["name"],
        "type": "INSERT",
        "serverId": "27142678",
        "es": 1690000000000,
        "sql": "",
        "database": "demoDatabase",
        "sqlType": {
            "sex": 253,
            "name": 253,
            "age": 3
        },
        "mysqlType": {
            "sex": "varchar",
            "name": "varchar",
            "age": "int"
        },
        "id": 62051,
        "isDdl": false,
        "table": "demoTable",
        "ts": 1690000000000
    }]
}

UPDATE `demoDatabase`.`demoTable` SET `age`=11 WHERE `name`='xiaoming';

{
    "Records": [{
        "data": [{
            "sex": "man",
            "name": "xiaoming",
            "age": "11"
        }],
        "pkNames": ["name"],
        "old": [{
            "sex": "man",
            "name": "xiaoming",
            "age": "10"
        }],
        "type": "UPDATE",
        "serverId": "27142679",
        "es": 1690000000000,
        "sql": "",
        "database": "demoDatabase",
        "sqlType": {
            "sex": 253,
            "name": 253,
            "age": 3
        },
        "mysqlType": {
            "sex": "varchar",
            "name": "varchar",
            "age": "int"
        },
        "id": 62373,
        "isDdl": false,
        "table": "demoTable",
        "ts": 1690000000000
    }]
}

DELETE FROM `demoDatabase`.`demoTable` WHERE `name`='xiaoming';

{
    "Records": [{
        "data": [{
            "sex": "man",
            "name": "xiaoming",
            "age": "11"
        }],
        "pkNames": ["name"],
        "type": "DELETE",
        "serverId": "27142679",
        "es": 1690000000000,
        "sql": "",
        "database": "demoDatabase",
        "sqlType": {
            "sex": 253,
            "name": 253,
            "age": 3
        },
        "mysqlType": {
            "sex": "varchar",
            "name": "varchar",
            "age": "int"
        },
        "id": 62635,
        "isDdl": false,
        "table": "demoTable",
        "ts": 1690000000000
    }]
}

What's next

• If a data entry exceeds 16 MB and causes the task to report an error, use the ETL feature to filter out large objects or narrow the synchronization scope. See Modify the ETL configuration of an existing synchronization task and Modify synchronization objects.

• Write function code to process the Canal Json events received by your function. See Code development overview.