Synchronize data between OceanBase databases in different compatible modes

Prerequisites

• The data transmission service has the privilege to access cloud resources. For more information, see Grant privileges to roles for data transmission.

• You have created a dedicated database user for data synchronization in OceanBase Database and granted required privileges to the user. For more information, see Configure user privileges for a data source.

Limits

Data Transmission Service supports only objects with database, table, and column names that use ASCII characters. These names cannot contain special characters, such as line breaks, spaces, or any of the following: `.|"'`()=;/&\`.

Considerations

• If the clocks between nodes or between the client and the server are out of synchronization, the latency may be inaccurate during incremental synchronization.

  For example, if the clock is earlier than the standard time, the latency can be negative. If the clock is later than the standard time, the latency can be positive.

• If you select only Incremental Synchronization when you create the data synchronization task, the data transmission service requires that the local incremental logs in the source database be retained for at least 48 hours.

  If you select Full Synchronization and Incremental Synchronization when you create the data synchronization task, the data transmission service requires that the local incremental logs in the source database be retained for at least seven days. Otherwise, the data synchronization task may fail or the data at the source and target may be inconsistent because the data transmission service cannot obtain incremental logs.

• If the source or target contains table objects that differ only in letter cases, the data synchronization results may not be as expected due to case insensitivity at the source or target.

• Case sensitivity

  • When you synchronize data from an OceanBase database in MySQL compatible mode to an OceanBase database in Oracle compatible mode, the target table name and column names in the table are converted into uppercase letters.

  • When you synchronize data from an OceanBase database in Oracle compatible mode to an OceanBase database in MySQL compatible mode, the target table name and column names in the table are converted into lowercase letters.

• When you synchronize data from an OceanBase database in MySQL compatible mode to an OceanBase database in Oracle compatible mode, data loss may occur if a column with a UNIQUE constraint allows NULL values. Specifically, if a column with multiple NULL values exists in the source database, only the first NULL value of the column is successfully synchronized and subsequent NULL values are discarded due to the UNIQUE constraint violation.

Supported source and target instance types

Data type mappings

Procedure

1. Log on to the OceanBase Management Console and purchase a data sync task.

   For more information, see Purchase a data sync task.

2. In the Data Transmission Service console, navigate to the Data Synchronization page, find the new sync task, and click Configure.

   

   If you want to reuse the configurations of an existing task, click Reuse Configuration. For more information, see Reuse and clear the configurations of a data sync task.

3. On the Select Source and Target page, configure the parameters.

   Parameter	Description
   Synchronization Task 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.
   Source	If you have created an OceanBase data source, you can select an OceanBase data source in MySQL or Oracle compatible mode from the drop-down list as needed. Otherwise, click New Data Source in the drop-down list and create one in the dialog box that appears on the right. For more information about the parameters, see Create an OceanBase data source.
   Target	If you have created an OceanBase data source: Select an OceanBase data source in Oracle compatible mode from the drop-down list if the source is an OceanBase data source in MySQL compatible mode. Select an OceanBase data source in MySQL compatible mode from the drop-down list if the source is a OceanBase data source in Oracle compatible mode. Otherwise, click New Data Source in the drop-down list and create one in the dialog box that appears on the right.
   Tag (Optional)	Select a target tag from the drop-down list. You can also click Manage Tags to create, modify, and delete tags. For more information, see Use tags to manage data synchronization tasks.

4. Click Next. On the Select Synchronization Types page, select the synchronization types.

   

   The synchronization types include Structure Synchronization, Full Synchronization, and Incremental Synchronization. Incremental Synchronization includes DML Synchronization (including INSERT, DELETE, and UPDATE) and DDL Synchronization. You can select the types as needed. For more information, see Customize DDL/DML and Scope of DDL synchronization.

5. Click Next. On the Select Sync Objects page, select the objects to sync.

   You can select sync objects using Specify Objects or Matching Rules. This topic describes how to select objects using Specify Objects. For more information about how to configure matching rules, see Configure and modify matching rules.

   Important

   • The names of the tables and columns to be synchronized cannot contain Chinese characters.

   • If a database or table name contains "$$", the data sync task may fail to be created.

   • If you selected DDL Synchronization in the Select Sync Type step, use matching rules to select the objects to synchronize. This ensures that all new objects that match the rules are included in the synchronization. If you specify objects directly instead of using rules, new or renamed objects will not be synchronized.

   

   1. In the Select Sync Objects section, select Specify Objects.

   2. In the Select Sync Scope section, select the objects to sync from the Source Objects list. You can select tables from one or more databases as sync objects.

   3. Click > to add them to the Destination Objects list.

   Data Transmission Service lets you import objects from a text file. You can also rename destination objects, set row filters, and remove a single object or all objects.

   Note

   When you use Matching Rules to select objects to sync, the matching rule syntax handles object renaming. You can only set filter conditions, select shard columns, and select columns to sync. For more information, see Configure and modify matching rules.

   Operation	Description
   Import Objects	In the list on the right, click Import Objects in the upper-right corner. In the dialog box, click OK. Important Importing will overwrite previous selections. Proceed with caution. In the Import Sync Objects dialog box, import the objects to synchronize. You can import a CSV file to rename database tables, set row filter conditions, and perform other operations. For more information, see Download and import sync object configurations. Click Check Validity. After the check is passed, click OK.
   Rename	Data Transmission Service lets you rename sync objects. For more information, see Rename database and table objects.
   Settings	Data Transmission Service supports row filtering with a WHERE clause. For more information, see Filter data with SQL conditions. You can also view the column information of sync objects in the View Columns section.
   Remove/Remove All	Data Transmission Service lets you remove one or more objects that have been temporarily selected for the destination during data mapping. Remove a single sync object In the list on the right, hover your mouse over the target object and click the Remove icon that appears. This removes the sync object. Remove all sync objects In the list on the right, click Remove All in the upper-right corner. In the dialog box, click OK to remove all sync objects.

6. Click Next. On the Synchronization Options page, configure the parameters.

   • Full synchronization

     The following parameters are displayed only if you select Full Synchronization on the Choose Sync Type page.

     

     Parameter	Description
     Read Concurrency	This parameter specifies the number of concurrent threads for reading data from the source during full synchronization. The maximum value is 512. A high concurrency can increase the load on the source database and affect your business.
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the destination during full synchronization. The maximum value is 512. A high concurrency can increase the load on the destination database and affect your business.
     Full Sync Rate Limit	You can enable the full synchronization rate limit as needed. If you enable it, set the RPS and BPS. RPS is the maximum number of data rows that can be synchronized to the destination per second during full synchronization. BPS is the maximum volume of data that can be synchronized to the destination per second during full synchronization. Note The RPS and BPS settings only serve as a rate-limiting capability. The actual performance of full synchronization is affected by factors such as the source, the destination, and the instance specifications.
     Policy for Existing Records in Destination Table	Select Ignore: If data exists in the destination table and a conflict occurs between the existing data and the data being written, Data Transmission Service logs the conflict and keeps the existing data. Important If you select Ignore, full validation pulls data in IN mode. This mode cannot validate scenarios where extra data exists in the destination, and validation performance is degraded to some extent. Select the default value Stop Migration: If data exists in the destination table, an error is reported for the full migration. You must handle the data in the destination before you can continue the migration. Important If an error occurs and you click Resume, Data Transmission Service ignores this setting and continues to migrate table data. Proceed with caution.

   • Incremental synchronization

     The following parameters are displayed only if you select Incremental Synchronization on the Choose Synchronization Types page.

     

     Parameter	Description
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the destination during incremental synchronization. The maximum value is 512. A high concurrency may put excessive pressure on the destination and affect your business.
     Incremental Synchronization Rate Limit	You can decide whether to enable the incremental synchronization rate limit as needed. If you enable it, set RPS (the maximum number of data rows that can be synchronized to the destination per second during incremental synchronization) and BPS (the maximum data volume that can be synchronized to the destination per second during incremental synchronization). Note The RPS and BPS values set here are only for rate limiting. The actual performance of incremental synchronization is affected by factors such as the source, destination, and instance specifications.
     Incremental Synchronization Start Offset	If you select Full Synchronization when you select the synchronization type, you cannot change this parameter. If you do not select Full Synchronization but select Incremental Synchronization when you select the synchronization type, specify a point in time after which you want to synchronize data. The default value is the current system time. For more information, see Set the start offset for incremental synchronization.

   • Advanced parameters

     

     Parameter	Description
     Target Table Storage Type	This section is displayed only if the target is OceanBase Database V4.3.0 or later and you have selected Schema Synchronization or selected DDL synchronization for Incremental Synchronization on the Select Synchronization Type page. This parameter specifies the storage type for target table objects during schema synchronization or incremental synchronization. The storage types supported for target table objects are Default, Row storage, Column storage, and Hybrid columnar storage. For more information, see default_table_store_format. This parameter specifies the storage type for target table objects during schema synchronization or incremental synchronization. The storage types supported for target table objects are Default, Row storage, Column storage, and Hybrid columnar storage. For more information, see default_table_store_format. Note The value Default means that other parameters are automatically set based on the parameter configurations of the target. Table objects in schema synchronization or new table objects in incremental DDL synchronization are written to the corresponding schemas based on the specified storage type.
     Add Hidden Columns for Tables Without Non-null Unique Keys	This parameter is displayed only you have selected Schema Synchronization or selected DDL synchronization for Incremental Synchronization on the Select Synchronization Type page when you created a task that synchronizes data between OceanBase databases of different compatible modes. For more information, see Hidden column mechanism of the data transmission service.

7. Click Precheck.

   During the precheck, the data transmission service checks the read and write privileges of the database users and the network connectivity of the databases. A data synchronization task can be started only after it passes all check items. If an error is returned during the precheck, you can perform the following operations:

   • Identify and troubleshoot the problem and then perform the precheck again.

   • Click Skip in the Actions column of the failed precheck item. In the dialog box that prompts the consequences of the operation, click OK.

8. After the precheck passes, click Start Task.

   If you do not need to start the task right away, click Save. You can then start the task manually on the Sync Task List page or include it in a batch operation. For more information about batch operations, see Perform batch operations on data sync tasks.

   DTS lets you modify the synchronization objects while a data sync task is running. For more information, see View and modify synchronization objects and their filter conditions. After the data sync task starts, it runs based on the selected synchronization types. For more information, see View synchronization details.

If a data sync task fails, typically due to network connection issues or a slow process startup, you can click Resume on the task list or the product page.

References

• Create an OceanBase data source

• Perform batch operations on data sync tasks

• View the details of a data sync task