tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Data Transfer Service
    Documentation
    Download PDF

    Sync from TencentDB for MySQL to Self-Built or Third-Party Cloud MySQL

    Last updated: 2023-11-16 17:39:05
    Download PDF
      This document describes how to use DTS to sync data from a TencentDB for MySQL instance to a self-built or third-party cloud MySQL database.
      This document describes how to perform one-way sync. To create a complex topology structure, such as two-way sync or many-to-one sync, see Creating Two-Way Sync Data Structure, Creating Many-to-One Sync Data Structure, or Creating Multi-Site Active-Active IDC Architecture.

      Note

      During full data sync, DTS consumes certain source database resources, which may increase the load and pressure of the source database. If your database configuration is low, we recommend you sync the data during off-peak hours.
      To avoid duplicate data, make sure that the tables to be synced have a primary key or non-null unique key.
      Sync is implemented without locks by default, during which no global lock (the FTWRL lock) is added to the source database, and only tables without a primary key are locked.
      During data sync, DTS will use the account that executes the sync task to write the system database __tencentdb__ in the source database to record the data comparison information during the sync task.
      To ensure that subsequent data problems can be located, the __tencentdb__ system database in the source database will not be deleted after the sync task ends.
      The __tencentdb__ system database uses a single-threaded connection wait mechanism and occupies a very small space, about 0.01%–0.1% of the storage space of the source database; for example, if the source database is 50 GB, __tencentdb__ will be about 5–50 MB. Therefore, it has almost no impact on the performance of the source database and will not preempt resources.

      Prerequisites

      The source and target databases must meet the requirements for the sync feature and version as instructed in Databases Supported by Data Sync.
      Permissions required of the source database:
      GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,REPLICATION SLAVE,SHOW VIEW,PROCESS,SELECT ON *.* TO 'account'@'%' IDENTIFIED BY 'password';
      GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO 'account'@'%'; 
      FLUSH PRIVILEGES;
      Permissions required of the target database: ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER, and UPDATE.

      Application Restrictions

      Only basic tables, views, procedures, and functions can be synced.
      When views, procedures, and functions are synced, DTS will check whether user1 corresponding to DEFINER ([DEFINER = user1]) in the source database is the same as the sync account user2, and if not, DTS will change the SQL SECURITY attribute of user1 in the target database from DEFINER to INVOKER ([INVOKER = user1]) after the sync, and set the DEFINER in the target database to the sync account user2 ([DEFINER = sync account user2]). If the view definition in the source database is too complex, the task may fail.
      If the source MySQL database is a non-GTID database, DTS doesn't support HA switch for it. If it is switched, DTS incremental sync may be interrupted.
      Only data with the following three database engines can be synced: InnoDB, MyISAM, and TokuDB. Tables with other engines will be skipped during sync by default.
      Correlated data objects must be synced together; otherwise, sync will fail. Common correlations include table referenced by views, view referenced by views, and tables correlated through primary/foreign keys.
      During incremental sync, if the source database has distributed transactions or generates binlog statements in the STATEMENT format, sync will fail.
      If the binlog of the source database has a GTID hole, it may compromise the performance of the sync task and cause the task to fail.
      Scenarios that contain both DML and DDL statements in the same transaction are not supported. They will trigger errors during task execution.
      Geometry data types are not supported. They will trigger errors during task execution.
      The ALTER VIEW statement is not supported and will be skipped during sync.

      Operation Restrictions

      During the sync, do not perform the following operations; otherwise, the sync task will fail:
      Do not modify or delete user information (including username, password, and permissions) in the source and target databases and port numbers.
      Do not run distributed transactions in the source database.
      Do not write binlog data in the STATEMENT format into the source database.
      Do not clear binlogs in the source database.
      Do not delete the system table __tencentdb__ during incremental sync.

      Synchronizable SQL Operations

      Operation Type
      SQL Statement
      DML
      INSERT, UPDATE, DELETE
      DDL
      CREATE DATABASE, DROP DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE, CREATE VIEW, DROP VIEW, CREATE INDEX, DROP INDEX
      Note
      DDL operations involving partitions cannot be synced.

      Environment Requirements

      Type
      Environment Requirements
      Requirements for the source database
      The source and target databases can be connected.
      Requirements for instance parameters:
      The server_id parameter in the source database must be set manually and cannot be 0.
      row_format for the source databases/tables cannot be set to FIXED.
      The values of the lower_case_table_names variable in both the source and target databases must be the same.
      The connect_timeout variable in the source database must be greater than or equal to 10.
      Requirements for binlog parameters:
      The log_bin variable in the source database must be set to ON.
      The binlog_format variable in the source database must be set to ROW.
      The binlog_row_image variable in the source database must be set to FULL.
      On MySQL 5.6 or later, if the gtid_mode variable is not ON, an alarm will be triggered. We recommend you enable gtid_mode.
      It is not allowed to set do_db and ignore_db.
      If the source instance is a replica database, the log_slave_updates variable must be set to ON.
      We recommend that you retain the binlog of the source database for at least three days; otherwise, the task cannot be resumed from the checkpoint and will fail.
      Foreign key dependency:
      Foreign key dependency can be set to only one of the following two types: NO ACTION and RESTRICT.
      During partial table sync, all tables with foreign key dependency must be migrated.
      The environment variable innodb_stats_on_metadata must be set to OFF.
      Requirements for the target database
      The target database version must be later than or equal to the source database version.
      The target database must have sufficient storage space. If you select Full data initialization as the initialization type, the target database space must be at least 1.2 times the space of databases/tables to be synced in the source database.
      The target database cannot have sync objects such as tables and views with the same name as those in the source database.
      The max_allowed_packet parameter of the target database must be set to 4 MB or above.
      

      Directions

      1. Log in to the data sync task purchase page, select appropriate configuration items, and click Buy Now.
      Parameter
      Description
      Billing Mode
      Monthly subscription and pay-as-you-go billing are supported.
      Source Instance Type
      Select MySQL, which cannot be changed after purchase.
      Source Instance Region
      Select the source database region, which cannot be changed after purchase.
      Target Instance Type
      Select MySQL, which cannot be changed after purchase.
      Target Instance Region
      Select the target database region, which cannot be changed after purchase.
      Specification
      Select a specification based on your business needs. The higher the specification, the higher the performance. For more information, see Billing Overview.
      2. After successful purchase, return to the data sync list, and you can see the newly created data sync task. You need to configure it before you can use it.
      3. In the data sync task list, click Configure in the Operation column to enter the sync task configuration page.
      
      4. On the sync task configuration page, configure the source and target databases and their accounts and passwords, test the connectivity, and click Next.
      Setting Items
      Parameter
      Description
      Task Configuration
      Task Name
      DTS will automatically generate a task name, which is customizable.
      Running Mode
      Immediate execution and scheduled execution are supported.
      Source Instance Settings
      Source Instance Type
      The source database type selected during purchase, which cannot be changed.
      Source Instance Region
      The source database region selected during purchase, which cannot be changed.
      Service Provider
      For a self-built database (such as a CVM-based one) or TencentDB database, select Others. For a third-party cloud database, select the corresponding service provider. In this scenario, select Others.
      Access Type
      Select Database.
      Cross-/Intra-Account
      In this scenario, select Intra-account.
      Instance ID
      Source database instance ID.
      Account
      Account of the source database, which must have the required permissions.
      Password
      Password of the source database account.
      Target Instance Settings
      Target Instance Type
      The target database type selected during purchase, which cannot be changed.
      Target Instance Region
      The target database region selected during purchase, which cannot be changed.
      Access Type
      For a self-built or third-party cloud database, you can select Public Network generally or select VPN Access, Direct Connect, or CCN based on your actual network conditions. For the preparations for different access types, see Preparations Overview.
      Host Address
      IP address or domain name of the target database.
      Port
      Target database port.
      Account
      Account of the target database, which must have the required permissions.
      Password
      Password of the target database account.
      5. On the Set sync options and objects page, set the data initialization, data sync, and sync object options and click Save and Go Next.
      Note
      If you only select Full data initialization for Initialization Type, the system will assume by default that you have created the table structures in the target database and will neither sync table structures nor check whether the source and target databases have tables with the same name. Therefore, if you select Precheck and report error for If Target Already Exists, the precheck and error reporting feature won't take effect.
      If you want to rename a table (for example, rename table A "table B") during the sync, you must select the entire database (or entire instance) where table A resides rather than only table A as the sync object; otherwise, the system will report an error.
      
      Setting Items
      Parameter
      Description
      Data Initialization Option
      Initialization Type
      Structure initialization: Table structures in the source instance will be initialized into the target instance before the sync task runs.
      Full data initialization: Data in the source instance will be initialized into the target instance before the sync task runs. If you select Full data initialization, you need to create the table structure in the target database in advance.
      Both options are selected by default, and you can deselect them as needed.
      If Target Already Exists
      Precheck and report error: If a table with the same name exists in both the source and target databases, an error will be reported, and the task will stop.
      Ignore and execute: Full and incremental data will be directly added to tables in the target instance.
      Data Sync Option
      Conflict Resolution Method
      Report: If a primary key conflict of tables is found during data sync, the system will report an error and pause the data sync task.
      Ignore: If a primary key conflict is found during data sync, the system will keep the primary key record in the target database.
      Overwrite: If a primary key conflict is found during data sync, the system will use the primary key record in the source database to overwrite that in the target database.
      SQL Type
      Supported operations include INSERT, UPDATE, DELETE, and DDL. If you select Custom DDL, you can select different DDL statement sync policies as needed. For more information, see Setting SQL Filter Policy.
      Sync Object Option
      Database and Table Objects of Source Instance
      Select the objects to be synced. You can select basic databases, tables, views, procedures, and functions. The sync of advanced objects is a one-time operation: only advanced objects already in the source database before the task start can be synced, while those added to the source database after the task start will not be synced to the target database. For more information, see Syncing Advanced Object.
      Selected Object
      Database/table mapping (renaming) is supported. Hover over a database or table name, click the displayed Edit icon, and enter a new name in the pop-up window.
      We recommend that you do not rename tables when syncing advanced objects; otherwise, the sync may fail.
      Sync of online DDL temp tables (by using gh-ost, pt-online-schema-change tools) is supported. Click the edit button next to the table, and select the temp table name in the pop-up window. For more information, see Syncing Online DDL Temp Table.
      6. On the task verification page, complete the verification. After all check items are passed, click Start Task. If the verification fails, fix the problem as instructed in Check Item Overview and initiate the verification again.
      Failed: It indicates that a check item fails and the task is blocked. You need to fix the problem and run the verification task again.
      Alarm: It indicates that a check item doesn't completely meet the requirements, and the task can be continued, but the business will be affected. You need to assess whether to ignore the alarm or fix the problem and continue the task based on the alarm message.
      
      7. Return to the data sync task list, and you can see that the task has entered the Running status.
      Note
      You can click More > Stop in the Operation column to stop a sync task. Before doing so, ensure that data sync has been completed.
      
      8. (Optional) you can click a task name to enter the task details page and view the task initialization status and monitoring data.
      Contact Us

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

      Contact Us
      Technical Support

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

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy