Configure tasks for source databases with triggers

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 from the source database to the destination database.

   1. Navigate to the migration task list page for the destination region using one of the following methods.

      From the DTS console

      1. Log on to the Data Transmission Service (DTS) console.

      2. In the navigation pane on the left, click Data Migration.

      3. In the upper-left corner of the page, select the region where the migration instance is located.

      From the DMS console

      Note

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

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

      2. In the top menu bar, choose Data + AI > Data Transmission (DTS) > Data Migration.

      3. To the right of Data Migration Tasks, select the region where the migration instance is located.

   2. Click Create Task to navigate to the task configuration page.

   3. Configure the Source Database and Destination Database settings, and then click Test Connectivity and Proceed at the bottom of the page.

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

   4. For Migration Types, select Schema Migration, and then select the database or schema to migrate.

   5. Complete the remaining configuration steps based on your business requirements.

2. Schema migration also migrates the triggers. After the task completes, log on to the destination database and delete these triggers by running the following command:

   drop trigger <trigger_name>;

   Example:

   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 Source Database and Destination Database settings, and then click Test Connectivity and Proceed at the bottom of the page.

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

      Note

      Use the same source and destination databases that you configured in Step 1.

   3. For Synchronization Types, manually select Full Data Synchronization.

      Note

      • Ensure the synchronization objects are the same as the migration objects you configured in Step 1.

      • Synchronization Types is selected by default under Incremental Data Synchronization. Do not select Schema Synchronization.

   4. Complete the remaining configuration steps based on your business requirements.

Test data consistency

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

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

   The trigger in the source database then inserts the same row into the source child table.

2. Log on to the source and destination databases. Query the child table in both databases to verify data consistency.

   • Query results in the source database

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

   • Query results in the destination database

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

   The results show that the data is consistent.

Next steps

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

   For more information, see Terminate a DTS instance.

2. (Optional) View the statement for creating the trigger.

   1. Find the migration task that you created in Step 1.

   2. Click the ID of the task.

   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 manually add the trigger.