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.
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’@‘%’;
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 |
loose_polar_log_bin
of Alibaba Cloud PolarDB for MySQL is a global-level parameter.loose_polar_log_bin
on Configuration and Management > Parameter Configuration.ON_WITH_GTID
.ON
.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 consistentThe parameter max_allowed_packet of the target database must be at least 4 MThe 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 appearIf the max_allowed_packet of the target database is less than 1 GB, a warning will appearIf 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 |
Note:
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 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 Type | Select “MySQL”. |
Service Provider | Select “AWS”. | |
Database Version | Select PolarDB 5.6 or PolarDB 5.7. | |
Access Type | Select “Public Network”. | |
Region | The region of the source database refers to the outbound region of DTS. Please select the nearest region to Alibaba Cloud PolarDB for MySQL. | |
Host Address | The 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. | |
Port | The access port of Alibaba Cloud PolarDB for MySQL, which is 3306 by default. | |
Account | The database account of Alibaba Cloud PolarDB for MySQL. The account permissions need to meet certain requirements. | |
Password | The password of the Alibaba Cloud PolarDB for MySQL database account. | |
Target Database Settings | Target Database Type | Select “MySQL”. |
Access Type | Select “TencentDB” | |
Region | The region you select in the last step. | |
Database Instance | Select the target TencentDB instance ID. | |
Account | The account of the target TencentDB database. The account permissions need to meet certain requirements. | |
Password | The password of the target TencentDB database account. |
Configuration Item | Description |
---|---|
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. |
Was this page helpful?