tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
Data Transfer Service
    Documentation
    Download PDF

    Migration from MariaDB/Percona to TencentDB for MySQL

    Last updated: 2024-07-08 19:41:52
    Download PDF
      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 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 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, 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, 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 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. 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.
      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 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 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 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

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

      Migration from MariaDB and Percona to TencentDB for MySQL follows the same steps of migration from MySQL to TencentDB for 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.

      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