Data Transmission Service:Configure a data synchronization or migration task for a source database that contains a trigger

Data preparation

CREATE TABLE `parent` (
  `user_vs_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`user_vs_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8

CREATE TABLE `child` (
  `sys_child_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_vs_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`sys_child_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8

CREATE TRIGGER data_check
AFTER INSERT ON parent
FOR EACH ROW
insert into child(user_vs_id, name) values(new.user_vs_id, new.name) ;

Procedure

1. Create a data migration task to migrate the schema of the source database to the destination database.

   1. Go to the Data Migration page.

      1. Log on to the Data Management (DMS) console.

      2. In the top navigation bar, move the pointer over DTS.

      3. Choose DTS (DTS) > Data Migration.

      Note

      • The actual operations may vary based on the mode and layout of the DMS console. For more information, see Simple mode and Customize the layout and style of the DMS console.

      • You can also go to the Data Migration page of the new DTS console.

   2. From the drop-down list on the right side of Data Migration Tasks, select the region in which your data migration instance resides.

      Note

      If you use the new DTS console, you must select the region in which the data migration instance resides in the upper-left corner.

   3. Click Create Task to go to the task configuration page.

   4. Configure the parameters in the Source Database and Destination Database sections, and click Test Connectivity and Proceed.

      For more information, see Migrate data from a self-managed MySQL database to an ApsaraDB RDS for MySQL instance.

   5. Set the Migration Types parameter to Schema Migration, and select a database or a schema.

      

   6. Configure the other parameters based on your business requirements.

2. During schema migration, DTS also migrates triggers to the destination database. After the migration task completes, you need to log on to the destination database and run the following command to delete the migrated triggers:

   drop trigger <Trigger name>;

   Sample command:

   drop trigger data_check;

3. Create a data synchronization task to synchronize data from the source database to the destination database.

   1. In the left-side navigation pane, click Data Synchronization.

   2. Configure the parameters in the Source Database and Destination Database sections, and click Test Connectivity and Proceed.

      For more information, see Synchronize data from a self-managed MySQL database to an ApsaraDB RDS for MySQL instance.

      Note

      The configurations of the source and destination databases must be the same as those in Step 1.

   3. Select Full Data Synchronization for the Synchronization Types parameter.

      Note

      • The object to be synchronized must be the same as the object migrated in Step 1.

      • By default, Incremental Data Synchronization is selected for the Synchronization Types parameter. Do not select Schema Synchronization.

      

   4. Configure the other parameters based on your business requirements.

Test data consistency

1. Log on to the source database and insert a data entry into the parent table.

   insert into parent values(1,'testname');

   After a data entry is inserted, the trigger inserts the data entry into the source child table.

2. 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.

   • Query results of the source child table

     +--------------+------------+----------+
     | sys_child_id | user_vs_id | name     |
     +--------------+------------+----------+
     |         2001 |          1 | testname |
     +--------------+------------+----------+

   • Query results of the destination child table

     +--------------+------------+----------+
     | sys_child_id | user_vs_id | name     |
     +--------------+------------+----------+
     |         2001 |          1 | testname |
     +--------------+------------+----------+

   The results show that the data in the source child table is consistent with the data in the destination child table.

What to do next

1. After data synchronization is complete, terminate the data synchronization task created in Step 3.

   For more information, see Terminate a DTS instance.

2. Optional. View the statements for creating the trigger.

   1. Find the data migration task created in Step 1.

   2. Click the ID of the task that you want to manage.

   3. On the Task Management page, click Schema Migration2.

   4. On the Task Details tab, click View Statements.

3. Log on to the destination database and add a trigger.