tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Data Transfer Service
    Documentation
    Download PDF

    Migration from MySQL to TencentDB for MySQL

    Last updated: 2023-12-26 15:16:29
    Download PDF
      This document describes how to use the data migration feature of DTS to migrate data from MySQL to TencentDB for MySQL.
      The following deployment modes of the source database are supported:
      Self-built MySQL.
      TencentDB for MySQL.
      MySQL in other clouds: Alibaba Cloud ApsaraDB RDS, Alibaba Cloud ApsaraDB for PolarDB, Amazon RDS, and Amazon Aurora.

      Notes

      When DTS performs full data migration, it will occupy some resources in the source instance, which may increase the load of the source instance and the database pressure. If your database has low configurations, we recommend that you migrate the data during off-peak hours.
      Migration 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.
      When you create a data consistency check task, DTS will use the account that executes the migration task to write the system database __tencentdb__ in the source database to record the data comparison information during the migration task.
      To ensure that subsequent data problems can be located, the __tencentdb__ system database in the source database will not be deleted after the migration 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

      You have created a TencentDB for MySQL instance as instructed in Creating MySQL Instance.
      The source and target databases must meet the requirements for the migration feature and version as instructed in Databases Supported by Data Migration.
      You have completed all the preparations as instructed in Overview.
      The source database must have the following permissions:
      Migration of the entire instance:
      CREATE USER 'migration account'@'%' IDENTIFIED BY 'migration password';  
      GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,REPLICATION SLAVE,SHOW DATABASES,SHOW VIEW,PROCESS ON *.* TO 'migration account'@'%'; 
      // If the source database is an Alibaba Cloud database, you don't need to grant the `SHOW DATABASES` permission; otherwise, you need to do so. For more information on authorizing an Alibaba Cloud database, visit https://help.aliyun.com/document_detail/96101.html.
      // If you select to migrate triggers and events, you need grant both the `TRIGGER` and `EVENT` permissions.
      GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO 'migration account'@'%'; 
      GRANT SELECT ON *.* TO 'migration account';
      Migration of specified objects:
      CREATE USER 'migration account'@'%' IDENTIFIED BY 'migration password';  
      GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,REPLICATION SLAVE,SHOW DATABASES,SHOW VIEW,PROCESS ON *.* TO 'migration account'@'%';  
      // If the source database is an Alibaba Cloud database, you don't need to grant the `SHOW DATABASES` permission; otherwise, you need to do so. For more information on authorizing an Alibaba Cloud database, visit https://help.aliyun.com/document_detail/96101.html.
      // If you select to migrate triggers and events, you need grant both the `TRIGGER` and `EVENT` permissions.
      GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO 'migration account'@'%'; 
      GRANT SELECT ON `mysql`.* TO 'migration account'@'%';
      GRANT SELECT ON database to be migrated.* TO 'migration account';
      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

      Basic tables, views, functions, triggers, procedures, and events can be migrated, while system tables such as information_schema, sys, performance_schema, __cdb_recycle_bin__, __recycle_bin__, __tencentdb__, and mysql cannot.
      When views, procedures, and functions are migrated, DTS will check whether user1 corresponding to DEFINER ([DEFINER = user1]) in the source database is the same as the migration 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 migration, and set the DEFINER in the target database to the migration account user2 ([DEFINER = migration 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 instance, DTS doesn't support HA switch for it. If it is switched, DTS incremental migration may be interrupted.
      Only data with the following three database engines can be migrated: InnoDB, MyISAM, and TokuDB. Tables with other engines will be skipped during migration by default. If there is compressed TokuDB engine data in the source database, the target database must support the compression mode before migration can be performed; otherwise, the task will report an error.
      Correlated data objects must be migrated together; otherwise, migration will fail. Common correlations include table referenced by views, view referenced by views, and tables correlated through primary/foreign keys.
      During incremental migration, if the source database has distributed transactions or generates binlog statements in the STATEMENT format, the migration will fail.
      In migration scenarios without locks, after the migration task enters the "source database export" step, DDL operations are not supported.
      If the binlog of the source database has a GTID hole, it may compromise the performance of the migration task and cause the task to fail.
      Scenarios that contain both DML and DDL statements in the same transaction are not supported and will trigger errors during task execution.
      Geometry data types are not supported and will trigger errors during task execution.
      The ALTER VIEW statement is not supported and will be skipped during migration.

      Operation restrictions

      During migration, do not perform the following operations; otherwise, the migration 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 run DDL operations of changing the database/table structure during database/table structure migration or full migration.
      Do not delete the system table __tencentdb__ during incremental migration.
      If you only perform full data migration, do not write new data into the source instance during migration; otherwise, the data in the source and target databases will be inconsistent. In scenarios with data writes, to ensure the data consistency in real time, we recommend you select full + incremental data migration.
      If the source database is Alibaba Cloud ApsaraDB for RDS or PolarDB, it will add an additional primary key column to tables without a primary key or non-null unique key in the binlog. The added primary key column is invisible in the table structure and thus may not be recognized by DTS. Therefore, we recommend you not migrate tables without a primary key.

      Supported SQL operations

      Operation Type
      Supported SQL Operation
      DML
      INSERT, UPDATE, DELETE, REPLACE
      DDL
      TABLE: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, RENAME TABLE
      VIEW: CREATE VIEW, DROP VIEW
      INDEX: CREATE INDEX, DROP INDEX
      DATABASE: CREATE DATABASE, ALTER DATABASE, DROP DATABASE
      CREATE TABLE table name AS SELECT is not supported.

      Environment requirements

      Notes:
      The system will automatically check the following environment requirements before starting a migration task and report an error if a requirement is not met. If you can identify the failed check item, fix it as instructed in Check Item Overview; otherwise, wait for the system verification to complete and fix the problem according to the error message.
      Type
      Environment Requirement
      Requirements for the source database
      The source and target databases can be connected.
      The server where the source database resides must have enough outbound bandwidth; otherwise, the migration speed will be affected.
      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.
      We recommend you enable skip-name-resolve to reduce the possibility of connection timeout.
      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.
      You cannot set filter conditions with 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 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 if it is suspended or interrupted for longer than the time set for binlog retention.
      Foreign key dependency:
      Foreign key dependency can be set to only one of the following two types: NO ACTION and RESTRICT.
      During partial table migration, tables with foreign key dependency must be migrated.
      The migration precision of DTS for data in FLOAT type is 38 digits, and for data in DOUBLE type is 308 digits. You should check whether this meets your requirements.
      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 size of the target database space must be at least 1.2 times that of the databases/tables to be migrated in the source database. Full data migration will execute INSERT operations concurrently, causing some tables in the target database to generate data fragments. Therefore, after full migration is completed, the size of the tables in the target database may be larger than that in the source database.
      The target database cannot have migration 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 DTS console, select Data Migration on the left sidebar, and click Create Migration Task to enter the Create Migration Task page.
      2. On the Create Migration Task page, select the types, regions, and specifications of the source and target instances and click Buy Now.
      Configuration Item
      Description
      Creation Mode
      Select Create task.
      Billing Mode
      Only the pay-as-you-go billing mode is supported. After the purchase is completed, fees will be charged only in the incremental migration stage but not task configuration and full migration stages. However, due to Tencent Cloud's unified requirements for pay-as-you-go billing, the fees for one hour's usage will be frozen after the purchase. For detailed billing rules, see Billing Overview.
      Source Instance Type
      Select the source database type, which cannot be changed after purchase. Here, select MySQL.
      Source Instance Region
      This refers to the source region of the DTS service. If the source database is TencentDB, select its region here. If the source database is self-built or in another cloud, select the region closest to it, so that DTS can choose the optimal migration path to reduce the migration time.
      Target Instance Type
      Select the target database type, which cannot be changed after purchase. Here, select MySQL.
      Target Instance Region
      Select the target database region.
      Specification
      Select the specification of the migration link based on your business conditions. For the performance and billing details of different specifications, see Billing Overview.
      Quantity
      You can purchase up to 10 migration tasks at a time.
      3. On the Set source and target databases page, configure the task, source database, and target database settings. After the source and target databases pass the connectivity test, click Save.
      Notes:
      If the connectivity test fails, troubleshoot as prompted or as instructed in Database Connection Check and try again.
      
      As there are many cross-scenarios of source database deployment modes and access types, the migration steps for different scenarios are similar. The following only provides configuration examples for typical scenarios. For other scenarios, configure by referring to the examples.
      Example 1: Migrating a local self-built database to TencentDB through Direct Connect/VPN
      Category
      Configuration Item
      Description
      Task Configuration
      Task Name
      Set a task name that is easy to identify.
      Running Mode
      Immediate execution: The task will be started immediately after the task verification is passed.
      Scheduled execution: You need to configure a task execution time and the task will be started automatically then.
      Automatic Retry
      After this option is set, if the migration task is temporarily interrupted due to a network error, DTS will automatically retry and resume the task within the set time range.
      The supported time range is 5–720 minutes.
      Source Database Settings
      Source Database Type
      The source database type 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.
      Region
      The region selected during purchase, which cannot be changed.
      Access Type
      Select a type based on your scenario. In this scenario, select Direct Connect or VPN Access, and you need to configure VPN-IDC interconnection as instructed in Direct Connect or VPN Access: Configuring VPN-IDC Interconnection. For the preparations for different access types, see Overview.
      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 Connections.
      Database: The source database is a TencentDB instance.
      CCN: The source database can be interconnected with VPCs through CCN.
      VPC-based Direct Connect Gateway/VPN Gateway
      Only VPC-based Direct Connect gateway is supported. You need to confirm the network type associated with the gateway. For VPN Gateway, select a VPN Gateway instance.
      VPC
      Select a VPC and subnet associated with the VPC-based Direct Connect Gateway or VPN Gateway.
      Host Address
      IP address or domain name for accessing the source MySQL database.
      Port
      Port for accessing the source MySQL database.
      Account
      Account of the source MySQL database, which must have the required permissions.
      Password
      Password of the source MySQL database account.
      Connection Method
      Secure Sockets Layer (SSL) refers to the secure connection between DTS and the database through SSL that encrypts the transfer link.
      Enabling SSL may slow down the connection response. In general, the Tencent Cloud private network is secure, so there is no need to enable this option. If public network or Direct Connect are used for data transfer, and your business requires a higher level of data security, enable it; however, you should enable SSL encryption in the source database first in this case.
      Target Database Settings
      Target Database Type
      The target database type selected during purchase, which cannot be changed.
      Region
      The target database region selected during purchase, which cannot be changed.
      Access Type
      Select a type based on your scenario. In this scenario, select Database.
      Database Instance
      Select the instance ID of the target database.
      Account
      Account of the target database, which must have the required permissions.
      Password
      Password of the target database.
      Connection Method
      Secure Sockets Layer (SSL) refers to the secure connection between DTS and the database through SSL that encrypts the transfer link.
      Enabling SSL may slow down the connection response. In general, the Tencent Cloud private network is secure, so there is no need to enable this option. If public network or Direct Connect are used for data transfer, and your business requires a higher level of data security, enable it; however, you should enable SSL encryption in the source database first in this case.
      Example 2: Migrate between two TencentDB instances
      Category
      Configuration Item
      Description
      Task Configuration
      Task Name
      Set a task name that is easy to identify.
      Running Mode
      Immediate execution: The task will be started immediately after the task verification is passed.
      Scheduled execution: You need to configure a task execution time and the task will be started automatically then.
      Automatic Retry
      After this option is set, if the migration task is temporarily interrupted due to a network error, DTS will automatically retry and resume the task within the set time range.
      The supported time range is 5–720 minutes.
      Source Database Settings
      Source Database Type
      The source database type 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.
      Region
      The source database region selected during data migration task purchase, which cannot be changed.
      Access Type
      Select a type based on your scenario. In this scenario, select Database. For the preparations for different access types, see Overview.
      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 Connections.
      Database: The source database is a TencentDB instance.
      CCN: The source database can be interconnected with VPCs through CCN.
      Cross-/Intra-Account
      Intra-account: The source and target database instances belong to the same Tencent Cloud root account.
      Cross-account: The source and target database instances belong to different Tencent Cloud root accounts. The following uses migration under the same account as an example. For detailed directions on cross-account migration, see Cross-Account TencentDB Instance Migration.
      Database Instance
      Source MySQL instance ID.
      Account
      Account of the source MySQL database, which must have the required permissions.
      Password
      Password of the source MySQL database account.
      Connection Method
      Secure Sockets Layer (SSL) refers to the secure connection between DTS and the database through SSL that encrypts the transfer link.
      Enabling SSL may slow down the connection response. In general, the Tencent Cloud private network is secure, so there is no need to enable this option. If public network or Direct Connect are used for data transfer, and your business requires a higher level of data security, enable it; however, you should enable SSL encryption in the source database first in this case.
      Target Database Settings
      Target Database Type
      The target database type selected during purchase, which cannot be changed.
      Region
      The target database region selected during purchase, which cannot be changed.
      Access Type
      Select a type based on your scenario. In this scenario, select Database.
      Database Instance
      Select the instance ID of the target database.
      Account
      Account of the target database, which must have the required permissions.
      Password
      Password of the target database.
      Connection Method
      Secure Sockets Layer (SSL) refers to the secure connection between DTS and the database through SSL that encrypts the transfer link.
      Enabling SSL may slow down the connection response. In general, the Tencent Cloud private network is secure, so there is no need to enable this option. If public network or Direct Connect are used for data transfer, and your business requires a higher level of data security, enable it; however, you should enable SSL encryption in the source database first in this case.
      Example 3: Migrating an Alibaba Cloud ApsaraDB for RDS instance to a TencentDB instance over the public network
      Category
      Configuration Item
      Description
      Task Configuration
      Task Name
      Set a task name that is easy to identify.
      Running Mode
      Immediate execution: The task will be started immediately after the task verification is passed.
      Scheduled execution: You need to configure a task execution time and the task will be started automatically then.
      Automatic Retry
      After this option is set, if the migration task is temporarily interrupted due to a network error, DTS will automatically retry and resume the task within the set time range.
      The supported time range is 5–720 minutes.
      Source Database Settings
      Source Database Type
      The source database type 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 Alibaba Cloud.
      Region
      The source database region selected during purchase, which cannot be changed.
      Access Type
      For a third-party cloud database, you can select Public Network generally or select VPN Access, Direct Connect, or CCN based on your actual network conditions.
      In this scenario, select Public Network. For the preparations for different access types, see Overview
      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 Connections.
      Database: The source database is a TencentDB instance.
      CCN: The source database can be interconnected with VPCs through CCN.
      Host Address
      IP address or domain name for accessing the source MySQL database.
      Port
      Port for accessing the source MySQL database.
      Account
      Account of the source MySQL database, which must have the required permissions.
      Password
      Password of the source MySQL database account.
      Connection Method
      Secure Sockets Layer (SSL) refers to the secure connection between DTS and the database through SSL that encrypts the transfer link.
      Enabling SSL may slow down the connection response. In general, the Tencent Cloud private network is secure, so there is no need to enable this option. If public network or Direct Connect are used for data transfer, and your business requires a higher level of data security, enable it; however, you should enable SSL encryption in the source database first in this case.
      Target Database Settings
      Target Database Type
      The target database type selected during purchase, which cannot be changed.
      Region
      The target database region selected during purchase, which cannot be changed.
      Access Type
      Select a type based on your scenario. In this scenario, select Database.
      Database Instance
      Select the instance ID of the target database.
      Account
      Account of the target database, which must have the required permissions.
      Password
      Password of the target database.
      Connection Method
      Secure Sockets Layer (SSL) refers to the secure connection between DTS and the database through SSL that encrypts the transfer link.
      Enabling SSL may slow down the connection response. In general, the Tencent Cloud private network is secure, so there is no need to enable this option. If public network or Direct Connect are used for data transfer, and your business requires a higher level of data security, enable it; however, you should enable SSL encryption in the source database first in this case.
      4. On the Set migration options and select migration objects page, configure the migration type and objects and click Save.
      Notes:
      If you want to rename a table (for example, rename table A table B) during migration, you must select the entire database (or entire instance) where table A resides rather than only table A as the migration object; otherwise, the system will report an error.
      
      Configuration Item
      Description
      Migration Type
      Select an option based on your scenario.
      Structural migration: Structured data such as databases and tables in the database will be migrated.
      Full migration: The database/table structure and data of the entire database will be migrated. The migrated data will only be existing content of the source database when the task is initiated but not include the incremental data written to the source database after the task is initiated.
      Full + Incremental migration: The database/table structure and data of the entire database will be migrated. The migrated data will include the existing content of the source database when the task is initiated as well as the incremental data written to the source database after the task is initiated. If there are data writes to the source database during migration, and you want to smoothly migrate the data in a non-stop manner, select this option.
      Data Consistency Check
      If Full + Incremental migration is selected, you can perform data consistency check to carefully compare the data in the source and target databases after migration.
      If Full check is selected, when the migration task enters the "incremental sync" step, the time lag between them is 0 seconds, and the data gap between the target and the source databases is 0 MB, DTS will automatically trigger a data consistency check task.
      If Full check is not selected, you can manually trigger a check task when the task enters the "incremental sync" step. For more information, see Creating Data Consistency Check Task.
      Migration Object
      Entire instance: Migrate the entire database instance excluding the system databases such as information_schema, mysql, performance_schema, and sys.
      Specified objects: Migrate specified objects.
      Advanced Migration Object
      Procedures, functions, triggers, and events can be migrated.
      The migration of advanced objects is a one-time operation: only advanced objects already in the source database before the task start can be migrated, while those added to the source database after the task start will not be synced to the target database.
      Procedures and functions will be migrated during source database export. If there are no incremental migration tasks, triggers and events will be migrated when the task stops; otherwise, they will be migrated after you click Done, in which case the transition will take a slightly longer time.
      For more information, see Migrating Advanced Object.
      Migrate Account
      Select this feature if you want to migrate the account information of the source database.
      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 you not rename tables when migrating advanced objects; otherwise, the migration may fail.
      Sync Online DDL Temp Table
      If you perform an online DDL operation on tables in the source database with the gh-ost or pt-osc tool, DTS can migrate the temp tables generated by online DDL changes to the target database.
      If you select gh-ost, DTS will migrate the temp tables (_table name_ghc, _table name_gho, and _table name_del) generated by the gh-ost tool to the target database.
      If you select pt-osc, DTS will migrate the temp tables (_table name_new and _table name_old) generated by the pt-osc tool to the target database.
      For more information, see Migrating Online DDL Temp Table.
      5. On the task verification page, verify the task. After the verification is passed, click Start Task.
      If the verification fails, fix the problem as instructed in Check Item Overview and initiate the verification again. You can skip certain check items by blocking them after the verification fails. Then, you need to initiate the verification again to continue the task.
      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.
      If you select Migrate Account, the verification task will check the account information of the source database and migrate accounts meeting the requirements. DTS won't migrate ineligible accounts or will migrate them with fewer permissions. For more information, see Account Migration.
      
      6. Return to the data migration task list, and you can see that the task has entered the Preparing status. After 1–2 minutes, the data migration task will be started.
      Select Structural migration or Full migration: Once completed, the task will be stopped automatically.
      Select Full + Incremental migration: After full migration is completed, the migration task will automatically enter the incremental data sync stage, which will not stop automatically. You need to click Complete to manually stop the incremental data migration.
      Manually complete incremental data migration and business switchover at appropriate time.
      Check whether the migration task is in the incremental migration stage without any lag. If so, stop writing data to the source database for a few minutes.
      Manually complete incremental migration when the data gap between the target and the source databases is 0 KB and the time lag between them is 0 seconds.
      
      7. (Optional) If you want to view, delete, or perform other operations on a task, click the task and select the target operation in the Operation column. For more information, see Viewing Task.
      8. After the migration task status becomes Task successful, you can formally cut over the business. For more information, see Cutover Description.
      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