Create a project to synchronize data from a DBP logical table to a physical table in the MySQL tenant of OceanBase Database

OceanBase Migration Service (OMS) allows you to synchronize data from a DBP logical table to a physical table in the MySQL tenant of OceanBase Database. This feature supports database and table sharding or database sharding only. This topic describes how to create a data synchronization project to synchronize data from a DBP logical table to a physical table in the MySQL tenant of OceanBase Database.

Limits

• The logical table of DBP must have a primary key to synchronize data to a physical table in the MySQL tenant of OceanBase Database.

• The name of a table to be synchronized, as well as the names of columns in the table, must not contain Chinese characters.

Synchronizable DDL statements and limits

• Supported DDL operations for synchronization

  • Add column: ADD COLUMN. When you execute this statement to add a column, specify the default value in the statement.

  • Modify column length: MODIFY COLUMN. You can execute this statement only to increase the column length.

  Only two types of DDL statements executed on the logical table are automatically synchronized to the destination table in the incremental synchronization phase. A DDL statement executed on any physical table is automatically synchronized to other tables. This DDL statement is not synchronized to other tables if it is executed later.

  Notice

  By default, a DDL statement executed on a logical table is synchronized to all physical tables. The execution of the statement does not cause the schemas in physical tables of the logical table to be different.

  For unsupported DDL statements, you can set the ddlIgnoreUnsupport field to force execute these statements. The default value is true. For DDL statements in which etransfer failed to be parsed, this field is invalid.

  • Set the value of ddlIgnoreUnsupport to false to enable the feature of forcing execute DDL statements. In this case, DDL statements are executed and logs are printed, regardless of whether the DDL statements are supported.

  • Set the value of ddlIgnoreUnsupport to true to disable the feature of forcing execute DDL statements. In this case, unsupported DDL statements are not executed and only logs are printed.

• Limits of DDL statements

  • By default, schemas in a logical table are consistent. If they are inconsistent, DDL statements are executed only on some tables. Data synchronization failed because of inconsistent data.

  • When you execute the Alter Table Add Column statement, contain the default value in the statement. If you set ddlIgnoreUnsupport to true, statements that do not contain the default value are truncated by default.

  • Executing the Alter Table Modified statement does not work with the following case:

    A nullable column is set to non-null and the data is pulled back. Data synchronization is likely to fail because the data is null but the constraint at the destination does not allow null values.

    In this case, you can execute a DDL statement on the destination column to allow null values in this column. The following modified DDL statements are automatically synchronized.

Procedure

1. Create a data synchronization project.

   1. Log on to the OMS console.

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

   3. On the Data Synchronization page, click Create Synchronization Project in the upper-right corner.

2. On the Select Source and Destination page, configure the parameters.

   Parameter	Description
   Synchronization Project Name	We recommend that you set it to a combination of Chinese characters, digits, and letters. It must not contain any spaces and cannot exceed 64 characters in length.
   Label	Click the field and select the target tag from the drop-down list. You can click Manage Tags to create, modify, and delete tags. For more information, see Manage data synchronization projects by using tags.
   Source	If you have created a DBP data source, select it from the drop-down list. If you have not created a data source, click Create Data Source in the drop-down list and create a data source in the dialog box that appears on the right. For more information, see Create a DBP data source.
   Destination	If you have created a MySQL data source of OceanBase Database, select it from the drop-down list. If you have not created a data source, click Create Data Source in the drop-down list and create a data source in the dialog box that appears on the right. For more information, see Create a physical OceanBase data source.

3. Click Next. On the Select Synchronization Type page, specify Synchronization Type and Configuration for the current data synchronization project.

   Options for Synchronization Type and Configuration are Full Synchronization and Incremental Synchronization. Options for Incremental Synchronization are DML Synchronizationand DDL Synchronization. The DML operations for synchronization areInsert, Delete, and Update.

4. Click Next. In the Select Synchronization Scope section of the Select Synchronization Objects page, select the target object in the left list and click >.

   When you synchronize data from a DBP logical table to a physical table of a MySQL tenant of OceanBase Database, OMS allows you to import objects from text and perform the following operations on the objects in the destination database: rename the objects, set sharding columns, and remove the objects.

   Operation	Steps
   Import Objects	In the list on the right, click Import Objects in the upper-right corner. In the dialog box that appears, click OK. Notice This operation will overwrite previous selections. Proceed with caution. In the Import Synchronization Objects dialog box, import the objects to be synchronized. You can import CSV files to rename databases/tables and set row filtering conditions. For more information, see Download and import the settings of synchronization objects. Click Validate. After the validation succeeds, click OK.
   Rename	In the list on the right, move the pointer over the object that you want to change. Click Rename. Enter the name in the field and click OK.
   Settings	In the list on the right, move the pointer over the object that you want to change. Click Settings. In the Settings dialog box, click Shard Columns and select the target sharding columns in the drop-down list. You can select multiple fields as sharding columns. This parameter is optional. Unless otherwise specified, select the primary keys as sharding columns. If the primary keys are not load-balanced, select fields with unique identifiers and whose load is balanced as sharding columns. Ensure that the selected sharding columns are correct. An incorrect sharding column will cause data synchronization to fail. Sharding columns can be used for the following purposes: Load balancing: Threads used for sending messages can be recognized based on the sharding columns if the destination table supports concurrent writes. Orderliness: OMS ensures that messages are received in order if the values of the sharding columns are the same. The orderliness specifies the sequence of executing DML statements for a column. Click OK.
   Remove/Remove All	OMS allows you to remove one or more objects from the destination database during data mapping. Remove a single synchronization object In the list on the right of the selection section, hover the pointer over the target object, and click Remove. The synchronization object is removed. Remove all synchronization objects In the list on the right of the selection section, click Remove All in the upper-right corner. In the dialog box that appears, click OK to remove all synchronization objects.

5. Click Next. On the Synchronization Options page, specify the following parameters.

   Parameter	Description
   Incremental Synchronization Start Timestamp	If you have selected Full Synchronization when you set the synchronization type and configuration, the value here is the project start time by default and cannot be modified. If you do not select Full Synchronization when you set the synchronization type and configuration, specify a point in time after which the data is to be synchronized. The default value is the current system time. You can select a point in time or enter a timestamp. Notice You can select the current time or a point in time earlier than the current time. This parameter is closely related to the retention period of archived logs. Generally, you can start data synchronization from the current timestamp.
   Enable Intra-Transaction Sequence	Specifies whether to maintain order within a transaction. If you enable this feature, OMS marks each transaction sent to downstream devices.

6. Click Precheck.

   During precheck, OMS checks whether all the physical tables in the logical table are accessible based on the account and password entered in DBP and whether the user has the SELECT privilege. In addition, OMS checks whether the obconfig_url parameter of OceanBase Database is correctly configured. You can run the show parameters like "obconfig_url" command under the sys tenant to obtain the value of obconfig_url for pulling logs in subsequent incremental synchronization.

   If an error is returned:

   • You can troubleshoot the error and run the precheck again.

   • You can also click Skip in the Actions column of the precheck item that returns the error. Then, a dialog box appears, indicating the impact that may be caused if you choose to skip this check item. If you want to continue, click OK in the dialog box.

7. Click Start Project. If you do not need to start the project now, click Save to go to the details page of the data synchronization project. You can start the project later as needed.

   OMS allows you to modify synchronization objects when a data synchronization project is running. For more information, see View and modify synchronization objects. After a data synchronization project is started, the synchronization objects will be executed based on the selected synchronization type. For more information, see the "View synchronization details" section in the View details of a data synchronization project topic.

   If data access fails due to a network failure or the slow startup of processes, go to the project list or the project details page and click Restore.