Can Online DDL Tools Be Used for Real-time Synchronization?

Scenarios

DRS supports migration or synchronization tasks with MySQL serving as the source. In the incremental phase, third-party online DDL tools (such as PT-OSC and GH-OST) are used to execute DDL operations in the source database. An Online DDL tool creates a temporary table and uses the temporary table to perform DDL operations. In this case, there are the following scenarios for DRS migration or synchronization:

• For database- and instance-level migration or synchronization tasks, DRS automatically synchronizes DDL operations because the temporary table used by Online DDL is in the synchronization objects. No special processing is required.
• For table-level migration or synchronization tasks, if the temporary table used by third-party Online DDL has been added to the migration or synchronization objects when you create a DRS task, DRS will automatically synchronize DDL operations. No special processing is required.
• For table-level migration or synchronization tasks, if you select only the table data when creating a DRS task, DRS will not synchronize DDL operations because the temporary table used by Online DDL is not included in the selected objects. You can manually execute DDL operations in the destination database by referring to Constraints and Procedure to prevent DRS task failures caused by table structure inconsistency between the source and destination databases due to online DDL operations on the source database.

Constraints

• This solution is an alternative solution in scenarios where DRS database- and instance-level migration or synchronization cannot be used. You are advised to preferentially use the database- and instance-level migration or synchronization solution.
• The operation sequence of different DDL statements in the source and destination databases is different. Strictly follow the sequence in Procedure to prevent DRS task failures.
• The DDL statements executed in the source database and destination database must have the same semantics, including but not limited to the object name, column type, and length.

Procedure

1. Check the DRS task status. Ensure that the task is in the Incremental state and the incremental latency is within 10 seconds.
2. Confirm the DDL operations to be performed. Different operations are performed in different sequences in the source and destination databases.
   • Adding columns: Perform the operation in the destination database and then in the source database.
   • Deleting columns: Perform the operation in the source database and then in the destination database.
   • Adding, modifying, or deleting default values of columns: These operations are irrelevant to the operation sequence.
   • Changing column types: Perform the operation in the destination database and then in the source database.
   • Changing character sets: Perform the operation in the destination database and then in the source database.
   • Changing column names: Perform the operation in the source database, wait until the DRS task fails because the column is not found, and then perform the operation in the destination database to resume the DRS task.
   • Adding partitions: Perform the operation in the destination database and then in the source database.
   • Deleting partitions: Perform the operation in the source database and then in the destination database.
   • Adding indexes: This operation is irrelevant to the operation sequence.
   • Deleting indexes: This operation is irrelevant to the operation sequence.
   • Adding constraints (such as primary keys, unique keys, and checks): Perform the operation in the source database and then in the destination database.
   • Deleting constraints (such as primary keys, unique keys, and checks): Perform the operation in the destination database and then in the source database.
   • Increasing field lengths: Perform the operation in the destination database and then in the source database.
   • Reducing field lengths: Perform the operation in the source database and then in the destination database.
   

   If a DDL contains multiple operations, all operations except those irrelevant to the operation sequence (for example, changing the default value) must be performed in the required sequence. Otherwise, split it into multiple DDL operations. If you change default values when adding a column, perform the operation in the destination database and then in the source database.

   Table 1 Summary

   DDL Operation	Operation Sequence
   Adding columns, changing column types, changing character sets, adding partitions, deleting constraints, and increasing field lengths	Perform the corresponding operations in the destination database and then in the source database.
   Deleting columns, deleting partitions, adding constraints, and reducing field lengths	Perform the corresponding operations in the source database and then in the destination database.
   Adding, modifying, and deleting default values of columns, adding indexes, and deleting indexes	These operations are irrelevant to the operation sequence.
   Changing column names	Perform the operation in the source database, wait until the DRS task fails because the column is not found, and then perform the operation in the destination database to resume the DRS task.

3. After the DDL operations are complete in 2, check whether the DRS task is normal.