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.
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’@‘%’;
GRANT SELECT ON database to be migrated.* TO ‘migration account’;
GRANT SELECT ON *.* TO ‘migration account’;
|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
You need to perform precheck before starting a data migration task. The main check items are described below:
|Database connectivity||Check whether the source and target databases can be accessed by DTS|
|Environment||Check the environment variable
|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
|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
If the variable
You are not allowed to set
If the source instance is a replica, the variable
|Foreign key dependency||The foreign key dependency can either be
For partial table migration, tables with foreign key dependencies must be complete
|View||Only a definer that is the same as the
|Other warnings||If the
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
Log in to the DTS data migration console and click Create Migration Task to enter the migration task creation page.
Select the region of the target instance and click Free Trial. Currently, DTS data migration services are free of charge.
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 Type||Configuration Item||Description|
|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 Type||Select “MySQL”.|
|Service Provider||Select “Others”.|
|Access Type||Select “Public Network”.|
|Region||The region of the source database refers to the outbound region of DTS. Please select the region nearest to the external MySQL instance.|
|Host Address||The IP address or domain name to access the source MySQL database.|
|Port||The access port of the source MySQL database.|
|Account||The account of the source MySQL database, which needs to have required permissions.|
|Password||The password of the source MySQL database account.|
|Target Database Settings||Target Database Type||Select “MySQL”.|
|Access Type||Select “TencentDB”|
|Region||The region you select in the previous step.|
|Database Instance||Select the target TencentDB instance ID.|
|Account||The account of the target TencentDB database, which needs to have required permissions.|
|Password||The password of the target TencentDB database account.|
On the “Set migration options and select migration objects” page, set the migration type and objects, and click Save.
|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.|
Verify the migration task on the “Verify task” page. After the task is verified, click Start.
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.