Synchronize data from an IDB logical table to a physical table in a MySQL tenant of OceanBase Database

OceanBase Migration Service (OMS) allows you to synchronize data from an IDB logical table to the MySQL tenant of OceanBase Database. This feature supports database and table sharding or database sharding only. This topic describes how to synchronize data from an IDB logical table to the MySQL tenant of OceanBase Database.

Limits

• The IDB must have a primary key logical table so that it can be synchronized to 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.

• Data source identifiers, user accounts, and tags must be globally unique in OMS.

• OMS supports only the synchronization of objects whose database name or table name is an ASCII string without special characters. The special characters are . | \ " ' ` ( ) = ; / & \n

Usage notes

• If the clocks are not synchronized between the nodes or between the client and a server, the latency of incremental synchronization may be negative.

• We recommend that you select no more than 15,000 database objects for a project.

  If a table contains LOB fields or contains more than 500 columns, we recommend that you create a dedicated project for the table and set the JVM parameters of related components as needed. For example, set the limitator.select.batch.max parameter for the full verification component, the sourceBatchSize parameter for the full import component, and the sourceBatchSize parameter for the incremental synchronization component.

  Execute the following statement to query tables that contain LOB fields: SELECT DISTINCT(TABLE_NAME) FROM ALL_TAB_COLUMNS WHERE DATA_TYPE IN ('BLOB', 'CLOB', 'NCLOB') AND OWNER = XXX;.

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 of this field 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 MODIFY COLUMN 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 MODIFY 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, specify the following parameters.

   Parameter	Description
   Synchronization Project Name	We recommend that you set it to a combination of digits and letters. It must not contain any spaces and cannot exceed 64 characters in length.
   Labels	Click the field and select a 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 an IDB data source, select it from the drop-down list. Otherwise, click Add Data Source in the drop-down list to create one in the dialog box on the right side. For more information, see Create an IDB data source.
   Destination	If you have created a MySQL data source of OceanBase Database, select it from the drop-down list. Otherwise, click Add Data Source in the drop-down list to create one in the dialog box on the right side. For more information, see Create a physical data source of OceanBase Database. Notice The destination must not be a MySQL tenant of OceanBase Database V4.0.0.

3. Click Next. On the Select Synchronization Type page, select the synchronization type for the current data synchronization project.

   Options for Synchronization Type are Full Synchronization and Incremental Synchronization. Options for Incremental Synchronization are DML Synchronization and DDL Synchronization. The supported DML operations are Insert, Delete, and Update. Select the options based on your business needs. For more information, see DML filtering and Synchronize DDL operations.

   Notice

   If you want to perform Full Synchronization, set OBProxy URL, Full Synchronization User, and Full Synchronization Password when you add an IDB data source. Otherwise, ignore this option.

4. Click Next. On the Select Synchronization Objects page, select a synchronization scope.

   1. Enter the global unique ID (GUID) in the GUID field.

   2. Select the target object in the Source Objects list and click >.

      After you select the target object, you can rename the target object, set sharding columns, and remove the target object.

      Actions	Steps
      Rename	OMS allows you to rename databases or tables for synchronization objects. For more information, see Rename a database table.
      Parameter	In the list on the right, move the pointer over the target object. 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 to avoid potential performance issues. 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	During data mapping, OMS allows you to remove one or more selected objects to be migrated to the destination. Remove a single synchronization object In the list on the right of the selection section, move 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.
   Advanced Options	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 entered account and password 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 during the precheck:

   • You can identify and troubleshoot the issue and then perform the precheck again.

   • You can click Skip in the Actions column of the precheck item with the error. A dialog box will be displayed, prompting the impact caused if you skip this error. 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 the synchronization objects when the 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.