tencent cloud

Feedback

Data Sync from MySQL to TencentDB for MySQL

Last updated: 2022-05-23 16:29:59

    This document describes how to use the data sync feature of DTS to sync data from MySQL to TencentDB for MySQL.

    Notes

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

    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,SELECT ON *.* TO 'migration account'@'%' IDENTIFIED BY 'migration password';
      GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO 'migration account'@'%'; // If the source database is a TencentDB database, you need to grant the `__tencentdb__` permission
      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 and views can be synced, and objects such as functions, triggers, and stored procedures are not supported.
    • When a view is exported, DTS will check whether user1 corresponding to DEFINER ([DEFINER = user1]) in the source database is the same as the sync user user2, and if not, DTS will change the SQL SECURITY attribute of user1 in the target database from DEFINER to INVOKER ([INVOKER = user1]), and set the DEFINER in the target database to the sync user user2 ([DEFINER = user2]).
    • 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, MySIAM, and TokuDB. Tables with other engines will be skipped during synching by default.
    • Correlated data objects need to be synced together; otherwise, sync will fail. Common correlations include table reference by views, view reference by views, view/table reference by stored procedures/functions/triggers, 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 source database is Alibaba Cloud ApsaraDB RDS for MySQL, then the tables to be synced on v5.6 must have a primary key, while tables on v5.7 and later are unrestricted. If the source database is Amazon RDS for MySQL, then the tables to be synced must have a primary key.

    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 Statements
    DML INSERT, UPDATE, and DELETE
    DDL CREATE DATABASE, DROP DATABASE, ALTER DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE, CREATE VIEW, DROP VIEW, CREATE INDEX, and DROP INDEX

    Environment Requirements

    TypeEnvironment Requirement
    Requirements for source database
  • The source and target databases can be connected.
    • Requirements for the database 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 above, 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 database is a slave database, the `log_slave_updates` variable must be set to `ON`.
    • Foreign key dependency:
      • Foreign key dependency can be set to only one of the following three types: `NO ACTION`, `RESTRICT`, and `CASCADE`.
      • During partial table sync, tables with foreign key dependency must be migrated.
    Requirements for the target database
  • The target database version must be above 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.
  • Other requirements The environment variable `innodb_stats_on_metadata` must be set to `OFF`.

    Directions

    1. Log in to the data sync purchase page, select the corresponding configuration items, and click Buy Now.
      ParameterDescription
      Billing ModeMonthly subscription and pay-as-you-go billing modes are supported. Currently, the data sync feature is free of charge, and you will receive notifications by email and Message Center one month before the billing officially starts.
      Source Database TypeSelect MySQL (including TencentDB for MySQL and self-built MySQL).
      Source Database RegionSelect the source database region.
      Target Database TypeSelect MySQL (including TencentDB for MySQL and self-built MySQL).
      Target Database RegionSelect the target database region.
      Sync Task SpecificationCurrently, only the Standard Edition is supported
    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 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.
      CategoryParameterDescription
      Task Configuration Task Name DTS will automatically generate a task name, which is customizable.
      Running ModeImmediate execution and scheduled execution are supported.
      Source Database Settings Source Database Type Select the TencentDB instance type selected during purchase, which cannot be changed once configured.
      Source Database Region Select the TencentDB instance region selected during purchase, which cannot be changed once configured.
      Service Provider Others (including TencentDB for MySQL and self-built MySQL), AWS, and Alibaba Cloud are supported.
      Access Type If **Other Cloud Vendors** is selected as **Service Provider**, the access type can be public network; if **Others** is selected as **Service Provider**, you need to select an access type according to the database deployment conditions.
      • Public Network: the source database can be accessed through a public IP.
      • Self-Build on CVM: the source database is deployed in a CVM instance.
      • Direct Connect: the source database can be interconnected with VPCs through Direct Connect.
      • VPN Access: the source database can be interconnected with VPCs through VPN Connection.
      • Database: the source database is a TencentDB database.
      • CCN: the source database can be interconnected with VPCs through CCN.
      • VPC: the source and target databases are both deployed in Tencent Cloud VPCs..
      For a third-party cloud database, you can select **Public Network** generally or select **VPC Access**, **Direct Connect**, or **CCN** based on your actual network conditions. For the preparations for different access types, see Overview.
      Target Database Settings Target Database TypeSelect the target database type, which cannot be changed once configured.
      Target Database RegionSelect the target database region, which cannot be changed once configured.
      Access TypeSelect the access type of the target database.
      Access type description
      In the settings of the source and target databases, you need to enter different parameters according to the access type as listed below:
      Service ProviderAccess TypeInstance IDCVM InstanceHost AddressPortAccountPassword
      OthersDatabase ×××
      Self-Build on CVM××
      Public Network××
      CCN××
      AWS Public Network××
      Alibaba Cloud Public Network××
    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 migrate 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 select Full data initialization only, you need to create the table structures in the target database in advance.
      • If you want to use a tool such as gh-ost and pt-osc to perform online DDL operations on a table during sync, you must select the entire database (or entire instance) where the table resides rather than only the table as the sync object; otherwise, the temporary table data generated by online DDL changes cannot be synced to the target database.
      • If you want to rename a table (for example, rename table A to table B) during 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.
      Database/Table mapping: hover over the right side of a selected object, and the **Edit** icon will be displayed. Click it and then enter a mapping name in the pop-up window.
      CategoryParameterDescription
      Data Initialization Option Initialization Type
      • Structure initialization: table structures in the source database will be initialized into the target database before the sync task runs.
      • Full data initialization: data in the source database will be initialized into the target database before the sync task runs. 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 database.
      Data Sync Option Conflict Resolution Method
      • Report: if a primary key conflict is found during data sync, an error will be reported, and the data sync task will be paused.
      • Ignore: if a primary key conflict is found during data sync, the primary key record in the target database will be retained.
      • Overwrite: if a primary key conflict is found during data sync, the primary key record in the source database will overwrite that in the target database.
      SQL TypeThe following operations are supported: INSERT, DELETE, UPDATE, and DDL.
      Sync Object Option Database and Table Objects of Source DatabaseSelect the objects to be synced. You can select databases, tables, and views.
      Selected ObjectIt displays the selected sync objects, and database/table mapping is supported.
    6. On the task verification page, complete the verification. After all check items are passed, click Start Task.
      If the verification failed, fix the problem as instructed in Fix for Verification Failure and initiate the verification task again.
    • Failed: it indicates that a check item failed 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.
    1. 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. You need to ensure that data sync has been completed before stopping the task.

    2. (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.

    Technical Support

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

    7x24 Phone Support