This document describes how to migrate data from an AWS Aurora MySQL instance to a TencentDB for MySQL instance using Data Transmission Service (DTS). DTS supports structural migration, full data migration, and incremental data migration, allowing you to migrate data to TencentDB for MySQL without service interruption.
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 |
call mysql.rds_set_configuration('binlog retention hours', 24);
The command above sets the binlog retention period as 24 hours, and the maximum retention period can be 168 hours, namely 7 days.binlog_format
needs to be set as row
. For MySQL 5.6 and later versions, binlog_row_image
needs to be set as full
.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 target database version must be later than the source 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 of the table space 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 | The definer must be the same as of the user@host of the migration objects |
Other warnings | The max_allowed_packet of the source database is is larger than that of the target databaseThe max_allowed_packet of the target database is less than 1 GBThe character sets of the source and target databases should not consistent 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 resolve the problem and try again.
Category | Item | Description |
---|---|---|
Task | Task Name | Set a name that is easy to remember |
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 tags do not meet your requirements, please go to Manage Tags. | |
Source Database | Source Database Type | Select MySQL. |
Service Provider | Select AWS. | |
Database Version | Select Aurora MySQL 5.6 or Aurora MySQL 5.7. | |
Access Type | Select Public Network. | |
Region | Select the nearest region to the source AWS Aurora MySQL instance. This region will be set as the outbound region of DTS. | |
Host Address | The access IP of the AWS Aurora MySQL instance. You can obtain this address on the basic information page of AWS Aurora MySQL instance. | |
Port | The access port of AWS Aurora MySQL instance, which is 3306 by default. | |
Account | The database account of AWS Aurora MySQL instance. Make sure that the account has all the required permissions. | |
Password | The password of the AWS Aurora MySQL database account. | |
Target Database | 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. Make sure that this account has obtained all required permissions. | |
Password | The password of the target TencentDB database account. |
Configuration Item | Description |
---|---|
Migration Type | Choose Full + Incremental migration to implement data migration without service interruption. You can also choose Structural Migration or Full Migration as needed |
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?