PrevNext
search by keyword

Recent Pages

Documentation
Data Transmission Service
    Documentation
    Download PDF

    Migrating from Alibaba Cloud PolarDB for MySQL to TencentDB for MySQL

    Last updated: 2021-04-23 11:20:35
    Download PDF

      This document describes how to migrate data from Alibaba Cloud PolarDB for 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">

      Prerequisites

      • You have created a TencentDB for MySQL instance. Supported versions: MySQL 5.6 and MySQL 5.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.
      • The source Alibaba Cloud PolarDB for MySQL instance to be migrated can be accessed via public network. The public availability of Alibaba Cloud PolarDB for MySQL should be enabled. Supported versions: MySQL 5.6 and MySQL 5.7.
      • . You need to create a migration account on the source Alibaba Cloud PolarDB for 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 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.
      • Only tables with primary keys on the source MySQL instance can be migrated. During the full data migration process, no locks are needed and write operations are not blocked, so your business will be less impacted. We recommend that you add primary keys to the data tables of the source MySQL instance before data migration.
      • In case of DDL operations during the lock-free full data migration process, the migration task may fail. Therefore, please avoid DDL operations during this process.
      • The storage capacity of TencentDB for MySQL instance must be at least 1.2 times larger than that of Alibaba Cloud PolarDB for MySQL instance.

      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 Alibaba Cloud PolarDB for MySQL instance based on full data migration, and synchronizes the incremental data of the Alibaba Cloud PolarDB for 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

      Preparation

      • The binlog feature of Alibaba Cloud PolarDB for MySQL is disabled by default. Enabling this feature may lead to connection interruption because you need to restart all the nodes in the cluster. Please make business arrangements before the operation.
      • The parameter loose_polar_log_bin of Alibaba Cloud PolarDB for MySQL is a global-level parameter.
      • Log in to the Alibaba Cloud PolarDB for MySQL console and find the target parameter loose_polar_log_bin on Configuration and Management > Parameter Configuration.
        • For a PolarDB cluster featuring the MySQL 5.6 database engine, modify the current value to ON_WITH_GTID.
        • For a PolarDB cluster featuring the MySQL 5.7 database engine, modify the current value to ON.

      Precheck

      You need to perform precheck before starting a data migration task. The main check content and points are shown below:

      Check Content Check Point
      Database connectivity check The source and target databases can access the Internet
      Environment check Check the environment variable innodb_stats_on_metadata=off
      Version check Only MySQL 5.6 and MySQL 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
      Partial instance parameter check 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 M
      The variable connect_timeout of the source database must be greater than 10
      Source instance permission check See account permissions in Prerequisites
      Target instance permission check 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 check The target instance cannot have tables that conflict with the source instance
      Target instance capacity check The capacity of the target instance must be at least 1.2 times larger than the tablespace of the source instance
      Binlog parameter check 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 secondary database, the variable log_slave_updates must be ON
      Foreign key dependency check The foreign key dependency can either be no action or restrict
      For partial table migration, tables with foreign key dependencies must be complete
      View check Only a definer that is the same as the user@host of the migration objects is allowed
      Other warning check 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 lock-free full data migration, so data consistency cannot be guaranteed
      Checking table without primary key Tables to be migrated cannot contain tables without primary key

      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 Buy at 0 USD. 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.

        Note：

        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 name that has business meaning for easy task identification
        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 go to the console Manage Tags.
        Source Database Settings Source Database TypeSelect “MySQL”.
        Service ProviderSelect “AWS”.
        Database VersionSelect PolarDB 5.6 or PolarDB 5.7.
        Access TypeSelect “Public Network”.
        RegionThe region of the source database refers to the outbound region of DTS. Please select the nearest region to Alibaba Cloud PolarDB for MySQL.
        Host AddressThe IP address to access Alibaba Cloud PolarDB for MySQL. You can obtain this address on the basic information page of Alibaba Cloud PolarDB for MySQL.
        PortThe access port of Alibaba Cloud PolarDB for MySQL, which is 3306 by default.
        AccountThe database account of Alibaba Cloud PolarDB for MySQL. The account permissions need to meet certain requirements.
        PasswordThe password of the Alibaba Cloud PolarDB for MySQL database account.
        Target Database Settings Target Database TypeSelect “MySQL”.
        Access TypeSelect “TencentDB”
        RegionThe region you select in the last step.
        Database InstanceSelect the target TencentDB instance ID.
        AccountThe account of the target TencentDB database. The account permissions need to meet certain requirements.
        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.
      1. 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.
      • Structural + full migration task: the task will automatically end when it is completed. If you don’t need to cancel the task, do not manually end it. Otherwise, the migrated data will be lost.
      • Full + incremental migration task: After the full migration task is completed, it will automatically enter the stage of incremental data sync which will not automatically end. You need to click Complete to end incremental data sync.
        • Please manually complete incremental data sync and business switchover at appropriate time.
        • Observe whether the migration task is in the incremental sync stage and is in the “no delay” status. 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 delay between them is 0 second.
      1. (Optional) You can also click the task ID to enter the task details page, and then view migration task details and the migration object list.
      tencent
      Copyright © 2013-2021 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy