Migrating from Public Network-based External MySQL to TencentDB for MySQL

Last updated: 2021-05-17 18:05:38

    This document describes how to migrate data from public network-based external MySQL to TencentDB for MySQL, using the data migration feature of Data Transmission Service (DTS). DTS supports structural migration, full data migration, and incremental data migration, as well as non-stop smooth data migration to TencentDB for MySQL.

    Prerequisites

    • You have created a TencentDB for MySQL instance. Supported MySQL versions: v5.5, v5.6, and v5.7.
    • You need to create a migration account on the target MySQL instance. Account permissions required: all the read/write permissions for objects to be migrated.
    • MySQL 5.5, 5.6, and 5.7 are the supported versions of the source external MySQL instance to be migrated.
    • You need to create a migration account on the source external MySQL instance. Account permissions required are as follows:
      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’@‘%’;  
      GRANT ALL PRIVILEGES ON `__tencentdb__`.* TO ‘migration account’@‘%’;  
      GRANT SELECT ON `mysql`.* TO ‘migration account’@‘%’;
    • Partial table migration: GRANT SELECT ON database to be migrated.* TO ‘migration account’;
    • Full instance migration: GRANT SELECT ON *.* TO ‘migration account’;

    Notes

    • When DTS performs full data migration, it will occupy certain source instance resources, which may increase the load of the source instance and the database pressure. If your database has low configurations, we recommend that you migrate data during off-peak hours.
    • Tables to be migrated from the source MySQL database can have no primary key or unique index, and will not lead to duplicate data in the target database. Locks are needed during full data migration, and write operations will be blocked for a while during the table locking process.
    • The storage capacity of TencentDB for MySQL instance must be at least 1.2 times that of the source external MySQL instance.
    • If the source external MySQL instance is not GTID-based, DTS will not support the HA (Highly Available) switchover of the source instance. Once the HA switchover occurs on the source external MySQL instance, the DTS incremental sync may be interrupted.

    Supported Migration Types

    • Structural migration: DTS allows you to migrate the structure definition of migration objects to the target instance. Currently, DTS supports the structural migration of databases, data tables, and views.
    • Full migration: DTS allows you to migrate the full data of the source MySQL instance to the target TencentDB for MySQL instance.
    • Incremental sync: DTS reads and parses the binlog information of the source external MySQL instance based on full data migration, and synchronizes the incremental data of the source external MySQL instance to the target TencentDB for MySQL. In this way, incremental data can be smoothly migrated to Tencent Cloud in a non-stop manner.

    SQL Operations Supported by Incremental Sync

    Operation Type SQL Operations Supported by Incremental Sync
    DML INSERT, UPDATE, DELETE, and REPLACE
    DDL TABLE: CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE, and RENAEM TABLE
    VIEW: CREATE VIEW, ALTER VIEW, and DROP VIEW
    INDEX: CREATE INDEX and DROP INDEX
    DATABASE: CREATE DATABASE, ALTER DATABASE, and DROP DATABASE

    Precheck

    You need to perform precheck before starting a data migration task. The main check items are described below:

    Check Items Description
    Database connectivity Check whether the source and target databases can be accessed by DTS
    Environment Check the environment variable innodb_stats_on_metadata=off
    Version Only MySQL 5.5, 5.6, and 5.7 are supported for the source and target databases, and the source database version must be earlier than or the same as the target database version
    Some instance parameters The table_row_format cannot be Fixed
    The lower_case_table_names variables of the source and target databases must be consistent
    The parameter max_allowed_packet of the target database must be at least 4 MB
    The variable connect_timeout of the source database must be greater than 10
    Source instance permission See account permissions in Prerequisites
    Target instance permission The target TencentDB for MySQL instance needs the following permissions: 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, UPDATE
    Target instance’s content conflict The target instance cannot have tables that conflict with the source instance
    Target instance capacity The capacity of the target instance must be at least 1.2 times larger than the tablespace of the source instance
    Binlog parameter The variable binlog_format of the source instance must be ROW
    The variable log_bin of the source instance must be ON
    The variable binlog_row_image of the source instance must be FULL
    If the variable gtid_mode of the source instance (including MySQL 5.6 and later versions) is not ON, the WARNING will appear. We recommend that you enable gtid_mode
    You are not allowed to set do_db and ignore_db
    If the source instance is a replica, the variable log_slave_updates must be ON
    Foreign key dependency The foreign key dependency can either be no action or restrict
    For partial table migration, tables with foreign key dependencies must be complete
    View Only a definer that is the same as the user@host of the migration objects is allowed
    Other warnings If the max_allowed_packet of the source database is larger than that of the target database, a warning will appear
    If the max_allowed_packet of the target database is less than 1 GB, a warning will appear
    If the character sets of the source and target databases are inconsistent, a warning will appear
    For full migration (incremental migration excluded), you will be warned that this is a no-lock full data migration, so data consistency cannot be guaranteed

    Directions

    1. Log in to the DTS data migration console and click Create Migration Task to enter the migration task creation page.
    2. Select the region of the target instance and click Free Trial. Currently, DTS data migration services are free of charge.
    3. Complete task configuration, source database settings, and target database settings on the “Set source and target databases” page. After the connectivity test for the source and target databases is passed, click Create.
      说明:

      If the connectivity test fails, follow the prompts to troubleshoot, resolve the problem, and try again.

      Setting TypeConfiguration ItemDescription
      Task Configuration Task Name Set a task name that is easy to identify
      Running Mode “Immediate execution” and “Scheduled execution” supported. For immediate execution, the task is started immediately after the task is verified; for scheduled execution, you need to set the task execution time so that the task can be started upon the execution time.
      Tag The tag is used to manage resources by category from different dimensions. If the existing tag does not meet your requirements, please manage tags in the console.
      Source Database Settings Source Database TypeSelect “MySQL”.
      Service ProviderSelect “Others”.
      Access TypeSelect “Public Network”.
      RegionThe region of the source database refers to the outbound region of DTS. Please select the region nearest to the external MySQL instance.
      Host AddressThe IP address or domain name to access the source MySQL database.
      PortThe access port of the source MySQL database.
      AccountThe account of the source MySQL database, which needs to have required permissions.
      PasswordThe password of the source MySQL database account.
      Target Database Settings Target Database TypeSelect “MySQL”.
      Access TypeSelect “TencentDB”
      RegionThe region you select in the previous step.
      Database InstanceSelect the target TencentDB instance ID.
      AccountThe account of the target TencentDB database, which needs to have required permissions.
      PasswordThe password of the target TencentDB database account.
    4. On the “Set migration options and select migration objects” page, set the migration type and objects, and click Save.
      Configuration ItemDescription
      Migration Type Select “Structural migration” if you only need structural migration.
      Select “Full migration” if you only need full data migration.
      Select “Full + Incremental migration” if you need non-stop smooth data migration.
      Migration Object Select “Entire instance” if you need to migrate the entire instance, but system databases such as `information_schema`, `mysql`, `performance_schema`, and `sys` won’t be migrated
      Select “specified object” if you need to migrate specified tables.
      Specified Object Select the object to be migrated in the “Source Database Object” box, and drag it to the “Selected Object” box.
    5. Verify the migration task on the “Verify task” page. After the task is verified, click Start.
      • If the task is verified, start the migration task according to the running mode you select.
      • If task verification fails, you can view the verification items and troubleshoot. Verify the task again after the problem is solved.
    6. Return to the data migration task list, and the task will be in the “Creating” status. After running for 1-2 minutes, the data migration task will be started.
      • Cancel the task: if you want to cancel the migration task during the migration process, click Cancel. Canceling the task does not mean data in the target instance will be cleared. Restarting the task may lead to verification failure. To restart the migration task, you may need to manually clear the databases or data tables that may conflict in the target database.
      • Complete the task: during the incremental data sync process, you can click Complete when you confirm that the data in the source and target databases is consistent, which means the data gap between the source and target databases is 0 MB and the time lag between them is 0 second. After that, data sync will not be performed during the migration task, which means data migration has been completed.
      • View the task: you can click the task ID to enter the task details page, and then view migration task details and the migration object list.