tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Data Transfer Service
    Documentation
    Download PDF

    Migration from MySQL/MariaDB/Percona to TDSQL for MySQL

    Last updated: 2023-09-12 10:13:41
    Download PDF
      This document describes how to use the data migration feature of DTS to migrate data from MySQL/MariaDB/Percona/TDSQL-C for MySQL to TDSQL for MySQL.
      The following deployment modes of the source database are supported:
      Self-built MySQL and TencentDB for MySQL
      Self-built MariaDB and TencentDB for MariaDB
      Self-built Percona
      TDSQL-C for MySQL
      Note
      TencentDB for MariaDB supports three kernels: MariaDB, MySQL, and Percona. You don't need to distinguish the kernel when using the service. If the source database is TencentDB for MariaDB, no matter which kernel is used, you need to select MariaDB as the source database type.
      This document describes how to migrate data from MySQL to TDSQL for MySQL. The requirements and steps of data migration from MariaDB/Percona/TDSQL-C for MySQL to TDSQL for MySQL are basically the same.

      Note

      During full data migration, 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 that you migrate the data during off-peak hours.
      Migration is implemented without locks by default, during which the global lock (FTWRL) is not 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 TDSQL for MySQL instance as instructed in Creating 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.
      You need to create a __tencentdb__ database in advance in the source MySQL database.
      You need to have the permissions of the source database.
      CREATE USER 'migration account'@'%' IDENTIFIED BY 'migration password';  
      GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHOW VIEW, PROCESS ON *.* TO 'migration account'@'%';  
      // If the source database is a TencentDB for MariaDB database, you need to submit a ticket to authorize `RELOAD`; otherwise, you can authorize by referring to the sample code.
      GRANT INSERT, UPDATE, DELETE, DROP, SELECT, INDEX, ALTER, CREATE ON `__tencentdb__`.* 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 (if the target database is TencentDB for MariaDB, you need to submit a ticket to authorize RELOAD).

      Use Limits

      You can only migrate tables but not advanced objects such as views, functions, triggers, and stored procedures.
      You cannot migrate system tables and user information such as information_schema, sys, performance_schema, __tencentdb__, and mysql.
      Only data with the InnoDB database engine can be migrated. Tables with other engines will be skipped during migration by default.
      Correlated data objects must be migrated together; otherwise, migration will fail.
      During incremental migration, if the source database has binlog statements in the STATEMENT format, the migration will 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.

      Operation Restrictions

      Below are the operation restrictions which will impact the success of the task:
      Do not modify or delete user information (including username, password, and permissions) in the source and target databases and port numbers.
      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 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 that you select full + incremental data migration.

      Supported SQL Operations

      Operation Type
      Synchronizable SQL Operation
      DML
      INSERT, UPDATE, DELETE, REPLACE
      DDL
      TABLE: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE 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

      Note
      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 unmet requirements, modify them as instructed in Check Item Overview; otherwise, wait for the system check to complete and modify them as prompted.
      Type
      Environment Requirements
      Requirements for the source database
      The source and target databases can be connected.
      Requirements for instance parameters:
      table_row_format 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 max_allowed_packet parameter in the target database must be at least 4 MB.
      The connect_timeout variable in the source database must be greater than or equal to 10.
      Requirements for binlog parameters:
      The binlog_format variable in the source database must be set to ROW.
      The log_bin variable in the source database must be set to ON.
      The binlog_row_image variable in the source database must be set to FULL.
      On v5.6 or later, if the gtid_mode variable in the source database is not ON, a warning will be triggered. We recommend that 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 migration, 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
      If the target database is a distributed database, we recommend that you manually create a partitioned table and plan the shardkey in advance; otherwise, DTS will create a table in the target database based on the table style of the source database. If the source database is a standalone instance, the target database will be created as a single table.
      The target database version must be later than or equal to the source database version.
      The target database space must be at least 1.2 times the space of databases/tables to be migrated in the source database.
      The target database cannot have tables that conflict with the source database.

      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 Items
      Description
      Source Instance Type
      Select the source database type, which cannot be changed after purchase. In this scenario, select "MySQL".
      Source Instance Region
      Select the source database region. If the source database is a self-built one, select a region nearest to it.
      Target Instance Type
      Select the target database type, which cannot be changed after purchase. In this scenario, select "TDSQL for MySQL".
      Target Instance Region
      Select the target database region.
      Specification
      Select the specification of the migration link based on your business needs. For the performance and billing details of different specifications, see Billing Overview.
      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 Create.
      Note
      If the connectivity test fails, troubleshoot as prompted or as instructed in Database Connection Check and try again.
      Category
      Configuration Items
      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.
      Tag
      You can use tags to categorize resources from different dimensions. If existing tags cannot meet your needs, go to the Tag console to create more.
      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, we take a local self-built database as an example, so select Others.
      Region
      The source database region selected during purchase, which cannot be changed.
      Access Type
      Select a type based on your scenario. 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 on 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.
      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.
      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.
      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 Database.
      Database Instance
      Select the ID of the target TDSQL for MySQL instance.
      Account
      Account of the target TDSQL for MySQL database, which must have the required permissions.
      Password
      Password of the target TDSQL for MySQL database.
      4. On the Set migration options and select migration objects page, configure the migration type and objects and click Save.
      Note
      If you want to use a tool such as gh-ost and pt-osc to perform online DDL operations on a table during migration, you must select the entire database (or entire instance) where the table resides rather than only the table as the migration object; otherwise, the temporary table data generated by online DDL changes cannot be migrated to the target database.
      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, data in table B won't be synced to the target database after the renaming operation.
      
      Configuration Items
      Description
      Migration type
      Select an option as needed.
      Full migration: 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 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 data needs to be written to the source database during migration and you want to smoothly migrate the data in a non-stop manner, select this option.
      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.
      Specify object
      Select the objects to be migrated in Source Database Object and move them to the Selected Object box.
      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.
      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.
      6. Return to the data migration task list, and you can see that the task has entered the Creating status. After 1–2 minutes, the data migration task will be started.
      Select Structural migration or Full migration: Once completed, the task will stop 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 sync.
      Manually complete incremental data sync and business switchover at appropriate time.
      Check whether the migration task is in the incremental sync stage without any lag. If so, stop writing data to the source database for a few minutes.
      Manually complete incremental sync when the data gap between the target and the source databases is 0 MB 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