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:
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.
__tencentdb__in the source database to record the data comparison information during the migration task.
__tencentdb__system database in the source database will not be deleted after the migration task ends.
__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.
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
During migration from MariaDB to MySQL, due to their slight differences in features, the following compatibility issues may occur:
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.
SHOW CREATE TABLEwill still display the default value
DEFAULT NULLafter the table is created.
datetimetype in the source database is
datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
SHOW CREATE TABLEwill 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.
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.
PERIOD FOR/WITHOUT OVERLAPSstatement 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.
CREATE OR REPLACE TABLEstatement 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.
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.
Basic tables, views, functions, triggers, stored procedures, and events can be migrated, while system tables such as
When views, stored procedures, and functions are migrated, DTS will check whether
user1 corresponding to
[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
[INVOKER = user1]) after the migration, and set the
DEFINER in the target database to the migration account
[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:
lower_case_tame_nameparameter (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 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.
ALTER VIEW statement is not supported and will be skipped during migration.
STATEMENTformat into the source database.
__tencentdb__during incremental migration.
CREATE OR REPLACE TABLE/PERIOD FOR/WITHOUT OVERLAPSare included, the migration task may report errors during full migration and will ignore them during incremental migration.
|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
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.
|Requirements for source database||
|Requirements for the target database||
|Other requirements||The environment variable `innodb_stats_on_metadata` must be set to `OFF`.|
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.