tencent cloud

Feedback

Migration from MariaDB/Percona to TencentDB for MySQL

Last updated: 2022-09-21 17:24:22

    This document describes how to use the data migration feature of DTS to migrate data from MariaDB or Percona to TencentDB for MySQL.

    The following deployment modes of the source database are supported:

    • Self-built MariaDB and TencentDB for MariaDB.
    • Self-built Percona.
    Note:

    TencentDB for MariaDB supports three kernels: MariaDB, Percona, and MySQL. 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.

    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.
    • 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 a TencentDB for MariaDB database, you need to submit a ticket to authorize `RELOAD`; otherwise, you can authorize by referring to the sample code
        // 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, RELOAD, 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
        // 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';
        
    • If the source database is MariaDB 10.5 or 10.6, you also need the SLAVE MONITOR permission to run show slave status.

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

    Compatibility description for heterogeneous migration

    During migration from MariaDB to MySQL, due to their slight differences in features, the following compatibility issues may occur:

    1. Due to MariaDB's functional characteristics, some SQL statements are different from the returned result of SHOW CREATE TABLE, which may cause differences of the synced DDL statements in the target database.

      • Even if no default value is specified for the blob type in MariaDB, SHOW CREATE TABLE will still display the default value DEFAULT NULL after the table is created.
      • If the DDL statement of datetime type in the source database is datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP, SHOW CREATE TABLE will display datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp() after the table is created, and the DDL parsed by the target MySQL will be datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP.
    2. Some statements only supported by MariaDB (such as CREATE OR REPLACE TABLE/PERIOD FOR/WITHOUT OVERLAPS) may cause the migration task to report errors during full migration and will be ignored during incremental migration.

      • If the PERIOD FOR/WITHOUT OVERLAPS statement is executed before the migration task is started or during full migration (in the source database export and data import step), the migration task will fail; if it is executed during incremental sync, the target database will ignore it, and data cannot be synced to the target database.
      • As DDL operations that change the database or table structure cannot be performed during full migration, if the CREATE OR REPLACE TABLE statement is executed during full migration, the migration task will fail; if it is executed during incremental sync, the target database will ignore it, and data cannot be synced to the target database.
    3. MariaDB allows default values for blob/text data, but MySQL does not. If there are SQL statements of these types, the migration task will report errors.

    Application restrictions

    • Basic tables, views, functions, triggers, stored 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, stored 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 sync 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.

    • Correlated data objects must be migrated together; otherwise, migration will fail. Common correlations include table reference by views, view reference by views, and associative tables based on 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.

    • If the source database is TencentDB for MariaDB, the following limits apply:

      • The DTS migration task requires that the values of the lower_case_tame_name parameter (table name case sensitivity) of the source and target databases be the same. If the source database is TencentDB for MariaDB, as it allows modifying this parameter only during instance creation, you need to determine the case sensitivity rule when creating the source database and modify this parameter of the target database if the values are different during verification.
      • If the source database is TencentDB for MariaDB 10.4, the Access Type does not support the Database option, and you need to select Public Network or other types.
    • 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 limits

    • 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 MariaDB's unique statements such as CREATE OR REPLACE TABLE/PERIOD FOR/WITHOUT OVERLAPS are included, the migration task may report errors during full migration and will ignore them 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.

    Supported SQL Operations

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

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

    TypeEnvironment Requirement
    Requirements for 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 the 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 MariaDB 10.2 or later and Percona 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.
    • 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.
    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.
  • Other requirements The environment variable `innodb_stats_on_metadata` must be set to `OFF`.

    Directions

    Migration from MariaDB and Percona to TencentDB for MySQL follows the same steps of migration from MySQL to TencentDB MySQL. For detailed configurations, see Migration from MySQL to TencentDB for MySQL.

    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