Synchronizing an entire database that contains a trigger may cause data inconsistency between the source and destination databases if the trigger updates a table. This topic uses an example to describe how to configure a data synchronization task in this scenario.
A database named triggertestdata contains a trigger and the following two tables: parent and child. If a data record is inserted into the parent table, the trigger inserts the data record into the child table.
In this example, if an INSERT operation is performed on the parent table during data synchronization, the data in the source child table will be inconsistent with the data in the destination child table. To resolve this issue, you must delete the trigger in the destination database.
This section takes data synchronization from a user-created MySQL database to ApsaraDB RDS for MySQL as an example. For more examples, see Overview of data synchronization scenarios.
- Create a data migration task to migrate the schema of the source database to the destination
database. For more information, see Migrate data from a user-created MySQL database to an ApsaraDB RDS for MySQL instance. In this example, select only Schema Migration for the Migration Types parameter.
Note In the Configure Migration Types and Objects step, select only Schema Migration for the Migration Types parameter, and then select databases and tables to be migrated.
- During schema migration, DTS migrates triggers to the destination database. After
the data migration task is completed, log on to the destination database and run the
following command to delete the migrated triggers.
drop trigger <Trigger name>;
drop trigger data_check;
- Create a data synchronization task to synchronize data from the source database to
the destination database. For more information, see Synchronize data from a user-created MySQL database hosted on ECS to an ApsaraDB RDS for MySQL instance. In this example, select only Initial Full Data Synchronization for the Initial Synchronization parameter.
- When you create the data synchronization task, you must select the same objects as those you selected in Step 1.
- The schema migration has been completed. Therefore, you only need to select Initial Full Data Synchronization for the Initial Synchronization parameter.
Check data consistency
- Log on to the source database and insert a data record into the parent table.
insert into parent values(1,'testname');After a data record is inserted, the trigger inserts the data record into the source child table.
- Log on to the source and destination databases. Query the data of the source child
table and the destination child table. Check whether the data is consistent between
the two tables.
The results show that the data in the source child table is consistent with the data in the destination child table.
- Query result of the source child table
- Query result of the destination child table