tencent cloud

Feedback

Creating TDSQL for MySQL Data Subscription

Last updated: 2024-07-08 16:52:02
    This document describes how to create a data subscription task in DTS for TDSQL for MySQL.

    Prerequisite

    You have prepared a TencentDB instance to be subscribed to, and the database version meets the requirements. For more information, see Databases Supported by Data Subscription.
    You have enabled the binlog in the source database.
    You have created a subscription account in the source database and granted it the following permissions: REPLICATION CLIENT, REPLICATION SLAVE, PROCESS, and SELECT for all objects. Below is the specific authorization syntax:
    create user 'migration account' IDENTIFIED BY 'account password';
    grant SELECT, REPLICATION CLIENT,REPLICATION SLAVE,PROCESS on *.* to 'migration account'@'%';
    flush privileges;

    Restrictions

    Currently, the subscribed message content is retained for 1 day by default. Once expired, the data will be cleared. Therefore, you need to consume the data promptly.
    The region where the data is consumed should be the same as that of the subscribed instance.
    Geometry data types are not supported currently.
    If the data subscription source is TDSQL for MySQL, it is not supported to directly run the authorization statement. Instead, you need to go to the TDSQL console, click the target instance ID, and enter the account management page to authorize. The permissions required by the subscription account are those listed in the above authorization statement. To perform __tencentdb__ authorization to the subscription account, select Object-Level Privilege in the Modify Permissions pop-up window and then select all permissions.
    If the data subscription source is TDSQL for MySQL, two-level partitioned tables cannot be subscribed to.
    If a two-level partitioned table is created in the source database before the subscription task is started, the verification task will fail.
    If a two-level partitioned table is created in the source database when the subscription task is running, the data subscribed to in the two-level partitioned table will be the data in the child table (if the selected subscription object is the entire database or entire instance, a two-level partitioned table created in the source database after the subscription task is started will also be subscribed to; in this way, data in the two-level partitioned table will also be included in the subscription). As the underlying layer of the two-level partitioned table is implemented by child tables, we recommend that you not create two-level partitioned tables during the subscription task execution; otherwise, differences in the subscribed data as shown below will occur. For example, if the source database table "test_a" is a two-level partitioned table, then the table name of the DML that DTS subscribes to this table is "test_a_tdsql_subp0/test_a_tdsql_subp1".
    During the process of subscribing to a task, if you perform operations such as modifying the subscription object, the task will be restarted, which may cause duplicated data consumption on the Kafka client.
    DTS transfers data based on the smallest data unit. Every time the incremental data is marked with a checkpoint, it will be considered a data unit. If the transfer of one data unit is completed before the task restart, it will not cause data duplication; if one data unit is still being transfered during the task restart, the data unit needs to be pulled again after the restart to ensure data integrity, which will lead to data duplication.
    If you are more concerned about duplicate data, you can implement deduplication logic when consuming data.

    Note

    For a subscription task whose database for subscription is TDSQL for MySQL, the DDL operations of each shard will be subscribed to and be published to Kafka. Therefore, DDL operations of a sharded table will lead to duplicate DDL statements. For example, if instance A has 3 shards and you subscribe to a sharded table A, you will subscribe to 3 DDL statements of table A.
    The header of each message in Kafka carries the shard information in key/value form. Here, the key is ShardId, and the value is the SQL passthrough ID. You can identify the shard where the message comes from based on the SQL passthrough ID. You can check the SQL passthrough ID in the TDSQL console: click Instance List > Manage in the Operation column, and select the Shard Management tab to view *SQL Passthrough ID**.
    

    Subscribable SQL Operations

    Operation Type
    Supported SQL Operations
    DML
    INSERT, UPDATE, DELETE
    DDL
    CREATE DATABASE、DROP DATABASE、CREATE TABLE、ALTER TABLE、DROP TABLE、RENAME TABLE

    Directions

    1. Log in to the DTS console, select Data Subscription on the left sidebar, and click Create Subscription.
    2. On the Create Subscription page, select the corresponding configuration and click Buy Now.
    Billing Mode: Monthly subscription and pay-as-you-go billing are supported.
    Region: The region must be the same as that of the database instance to be subscribed to.
    Database: Select your actual database type.
    Version: Select Kafka Edition. You can directly consume data on a Kafka client.
    Subscribed Instance Name: Edit the name of the current data subscription instance.
    3. After successful purchase, return to the data subscription list. You need to click Configure Subscription in the Operation column to configure the newly purchased subscription before you can use it.
    4. On the Subscription Configuration page, select the appropriate configuration items and click Next.
    Database Instance: Select a database instance. You can select a disaster recovery read-only instance. However, it is recommended to select the source instance, so that the pressure of the subscription service on the source database is minimized.
    Database Account: add the account and password of the instance to be subscribed to. The account must have the permissions required by the subscription task, including REPLICATION CLIENT, REPLICATION SLAVE, PROCESS, and SELECT of all objects.
    Number of Kafka Partitions: Set the number of Kafka partitions. Increasing the number can improve the speed of data write and consumption. A single partition can guarantee the order of messages, while multiple partitions cannot. If you have strict requirements for the order of messages during consumption, set this value to 1.
    5. On the Subscription Type and Object page, select a subscription type and click Save. Subscription Type: Options include Data Update, Structure Update, and Full.
    Data Update: data updates of the selected objects are subscribed to, including INSERT, UPDATE, and DELETE operations.
    Structure Update: creation, modification, and deletion of the structures of all objects in the instance are subscribed to.
    Full: data and structure updates of all objects in the instance are subscribed to.
    6. On the Pre-verification page, a pre-verification task will run for 2–3 minutes. After the pre-verification is passed, click Start to complete data subscription task configuration.
    Note
    If the verification fails, fix the problem as instructed in Check Item Overview and initiate the verification again.
    7. After you click Start, the subscription task will be initialized, which will take 3–4 minutes. After successful initialization, the task will enter the Running status.
    8. Add a consumer group. Data subscription (Kafka Edition) allows you to create multiple consumer groups for multi-point consumption. For more information, see Adding Consumer Group. The consumption in data subscription (Kafka Edition) depends on the consumer groups of Kafka; therefore, you must create a consumer group first before data can be consumed.
    9. After the subscription instance enters the Running status, you can start consuming data. For consumption in Kafka, you need to verify the password. For specific examples, see Data Consumption Demo. We provide demo code in multiple programming languages and descriptions of main consumption processes and key data structures.
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support