This section offers details about using DTS to synchronize data between MySQL databases, including the supported source and destination database types, supported synchronization objects and SQL operations, database account permissions, precautions, and procedures.
Supported Source and Destination Database Types
| Source Database Type | Destination Database Type |
|---|---|
| RDS MySQL and self-managed MySQL 5.6/5.7/8.0 | RDS for MySQL |
Note:
The engine of a self-managed MySQL database can be MySQL 5.6, 5.7, or 8.0, and the engine version of the destination database must be equal to or later than that of the source database.
Supported Synchronization Objects and SQL Operations
Synchronization Objects
DTS supports table-level (specified table objects) synchronization and database-level (entire database) synchronization.
DTS supports structure synchronization of tables, indexes, stored procedures, views, functions, events, and triggers.
The names of tables that store data of views, stored procedures, and functions cannot be mapped to new names in the destination database.
During structure synchronization, DTS changes the definers of the views, functions, stored procedures, triggers, and events to the account that is used to access the destination database. Then, the original definers have only the invoker permissions on these objects.
DTS only synchronizes user databases and automatically filters out system databases. For example, DTS does not synchronize information_schema, mysql, performance_schema, and sys databases in MySQL.
DTS synchronizes triggers and events after the full data synchronization is complete.
An empty database without tables, views, functions, events, triggers, or stored procedures cannot be used as a source database.
SQL Operations Supported During Incremental Data Synchronization
DML
INSERT, UPDATE, and DELETE
DDL
For incremental data synchronization, only the following DDL statements are supported: CREATE INDEX, DROP INDEX, ALTER TABLE, TRUNCATE TABLE, and DROP TABLE. For database-level synchronization, CREATE TABLE is supported.
Note:
DTS does not support the following statement: CREATE TABLE Table name AS SELECT.
Database Accounts and Permissions
| Database | Required Permissions | Sample Statement for Granting Permissions |
|---|---|---|
| Source Database | 1. Query permissions on MySQL databases. 2. Query permissions on the databases to be synchronized. 3. Certain global permissions. RELOAD LOCK TABLES REPLICATION CLIENT REPLICATION SLAVE SHOW VIEW PROCESS 4. For database-level synchronization, the query permissions on all databases are required. | GRANT SELECT ON mysql.*TO 'Account used for synchronization'@'%'; GRANT SELECT ON Source database. TO 'Account used for synchronization'@'%'; GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, SHOW VIEW, PROCESS ON .*TO 'Account used for synchronization'@'%'; |
| Destination Database | The following 23 global permissions are required: ALTER ALTER ROUTINE CREATE CREATE ROUTINE CREATE TEMPORARY TABLES CREATE USER CREATE VIEW DELETEDROP EVENTEXECUTE INDEX INSERT LOCK TABLESPROCESS REFERENCESRE LOAD SELECT SHOW DATABASES SHOW VIEW TRIGGER UPDATE | GRANT 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 ON*.*TO 'Account used for synchronization'@'%'; |
Precautions
The binlog feature must be enabled for the source MySQL database, and the row-based format is used for binary logging.
If the storage space is sufficient, store the source database binlogs as long as possible. The recommended retention period is three days.
If the value of the expire_logs_days parameter for the source database is 0, the synchronization may fail.
If you need to perform incremental data synchronization, you must specify a server ID for your source MySQL database. If the version of the source MySQL database is equal to or earlier than 5.6, the server ID can be from 2 to 4294967296. If the version is equal to or later than 5.7, the server ID can be from 1 to 4294967296.
We recommend that you enable global transaction identifier (GTID) for the source database. If GTID is disabled for the source database, DTS does not support primary-standby switchover for high availability because DTS tasks will be interrupted during primary-standby switchover and cannot be restored due to offset discontinuity.
Make sure that the destination database is empty before starting a synchronization task. If the destination database contains data, we recommend that you clear the existing data. If the destination database and the source database contain data with the same primary key values, the existing data in the destination database will be overwritten during DTS incremental data synchronization.
The destination database and the RDS instance to which the destination database belongs must be running normally. If the RDS instance uses the primary/standby architecture, the primary/standby replication must run as expected.
The destination RDS database instance must have sufficient storage space. During full data synchronization, concurrent INSERT operations cause table fragmentation in the destination database. Therefore, the size of the used tablespace in the destination database is larger than that in the source database instance. In addition, a large number of binlogs generated during synchronization also require a large storage space.
The character set of the destination RDS database must be the same as that of the source database.
If the destination database instance uses a column of the TIMESTAMP or DATETIME data type as the shard key, the seconds hierarchy in the column is removed after synchronization.
During structure synchronization, DTS changes the definers in the views, stored procedures, and functions to invokers.
DTS does not synchronize user information. If a user needs to call views, stored procedures, or functions in the destination database after synchronization, you must grant the read and write permissions on the objects to the user.
To ensure smooth restoration after service interruption and prevent the expiration of binlogs at the breakpoint, we recommend that you set the expire_log_day parameter for the source database to an appropriate value.
You are not recommended to perform DDL operations to delete data from the source database during task startup or full data synchronization. Otherwise, the synchronization may fail.
During synchronization, do not modify or delete the usernames, passwords, permissions, or ports of the source and destination databases.
During synchronization, do not modify the table structures to be synchronized in the source database.
If you synchronize selected tables, you are not recommended to rename the tables during incremental data synchronization.
During incremental data synchronization, the source database cannot be restored.
During incremental data synchronization, DTS does not synchronize incremental data added to the source tables without primary keys. DTS provides lower synchronization performance for tables without primary keys than those with primary keys. In addition, DTS cannot ensure data consistency if incremental data is added to the source tables without primary keys during synchronization.
Procedure
1. Purchase a DTS Synchronization Instance
Go to the buy page, and select Data sync for Instance Type. Select MySQL as the destination database instance type. Then, configure the other required information, and complete your purchase.
2. Go to the Configure Page
After you purchase a DTS instance, go to the configuration page because your task awaits your configuration. To go to this page, click Configure.
3. Configure the source and destination databases.
Go to the Configure Source and Destination Databases page. Then, configure information about the source and destination databases, including the database types, IP addresses, ports, database accounts, and passwords.
After you enter all required information about the source and destination databases, you can perform a connectivity test to check whether the databases can be connected.
4. Configure objects and advanced settings.
After you configure the source and destination databases, click Test Connectivity and Proceed. On the Configure Objects and Advanced Settings page that appears, select the source database objects that you need to synchronize.
Note:
The description of this page is similar to that of the Configure Objects and Advanced Settings page. You can go to DTS > User Guide > Data Migration > Instance Management > Configuring a Data Migration Instance to view the description
.
5. Perform Precheck and Synchronization
After you configure the objects and advanced settings, click Next: Precheck. Then, the Precheck page appears. The system prechecks the following items and provides a precheck result. You can perform operations based on the result.
If the precheck is passed, click Start Sync to start the synchronization task.