Migrate data from a TiDB database to OceanBase Database Community Edition

This topic describes how to use OceanBase Migration Service (OMS) Community Edition to migrate data from a TiDB database to OceanBase Database Community Edition.

Background information

The OMS Community Edition allows you to create a data migration task to migrate data from a source TiDB database to a target OceanBase Community Edition database. You can seamlessly migrate the business data and incremental data from the source database to OceanBase Community Edition through schema migration, full migration, and incremental synchronization.

The TiDB database is a converged distributed database, which supports Hybrid Transactional and Analytical Processing (HTAP). If you use a TiDB database of a version earlier than V4.x, select Bind Kafka Data Source when you create a TiDB data source. If you use a TiDB database of V4.x or later, you can leave Bind Kafka Data Source unselected and synchronize incremental data from the TiDB database to OceanBase Community Edition by using OMS Community Edition solely.

If you select Bind Kafka Data Source when you create a TiDB data source, you also need to deploy a TiCDC cluster and a Kafka cluster to synchronize incremental data from the TiDB database to OceanBase Community Edition. If incremental synchronization is not required for the data migration task, you do not need to deploy a TiCDC cluster and a Kafka cluster.

TiCDC is a tool used to synchronize incremental data of the TiDB database. The PD cluster, which is deployed to implement high availability, is the scheduling module of the TiDB cluster and generally consists of three PD nodes. The TiKV server sends change data to the TiCDC cluster in the form of change logs. The TiCDC tool synchronizes data to the Kafka cluster after it obtains data from multiple TiCDC processes and processes the data. The Kafka cluster stores the incremental logs converted by the TiCDC tool from the TiDB database, so that OMS can obtain the corresponding data from the Kafka cluster and real-time migrate the data to OceanBase Community Edition during incremental data synchronization.

Prerequisites

You have created dedicated database users in the source TiDB database and the target OceanBase Community Edition database for the data migration task and granted corresponding privileges to the users. For more information, see Create a database user.

Limitations

• At present, TiDB Database V2.x, V3.x, V4.x, V5.x, V6.x, V7.x, and V8.x are supported.

  Notice

  TiDB Database V2.x and V3.x support only TiDB Binlog.

• If the target is a database, the OMS Community Edition does not support triggers in the target database. If triggers exist in the target database, the data migration may fail.

• When you migrate data from a TiDB database to OceanBase Community Edition, DDL synchronization is not supported.

• The OMS Community Edition does not support the migration of tables without primary keys or data containing spaces to OceanBase Community Edition.

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

• OMS only supports migrating databases, tables, and column objects with ASCII-compliant names that do not contain special characters (|"'`()=;/& and newline characters).

• The OMS Community Edition supports only the TiCDC open protocol and does not support other protocols. If you use an unsupported protocol, a JDBC-Connector error will be returned, indicating a null pointer exception.

• Make sure that enable-old-value = true is added in the configuration for synchronizing data to the Kafka cluster. Otherwise, the message format of data synchronization may be abnormal. For more information, see Description of synchronization tasks.

Considerations

• We recommend that you migrate no more than 1,000 tables at a time to avoid impact on the data migration task.

• If there are multiple foreign keys with the same name in the source, an error is returned during schema migration. You can change the foreign key constraint names and resume the task.

• If the source character set is UTF-8, we recommend that you use a compatible character set, such as UTF-8 or UTF-16, for the destination to avoid issues like garbled text due to character set incompatibility.

• During reverse incremental migration from a TiDB database to OceanBase Community Edition V3.2.x or earlier, if the source table is a multi-partition table with a global unique index and you update the values of the partitioning key, data may be lost during migration.

• Do not write data to the topic synchronized by TiCDC. Otherwise, the JDBC-Connector may fail with an error indicating a null pointer exception.

• Make sure that the migration precision for columns of the DECIMAL, FLOAT, or DOUBLE data type meets your requirements in OMS Community Edition. If the precision of the target column type is less than that of the source column type, the data may be truncated, resulting in inconsistent data between the source and target.

• If you change the target unique index, you must restart the incremental synchronization component. Otherwise, inconsistent data may be returned.

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

  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.

• Take note of the following points if you want to perform data consolidation between a database and a table:

  • We recommend that you map the schemas of the source and destination databases by using import objects or matching rules.

  • We recommend that you manually create schemas in the destination database. If you use OMS to create schemas, skip failed objects in the schema migration step.

• If the source and destination table schemas are inconsistent, inconsistent data may be returned. Some known scenarios are described as follows:

  • When users manually create table schemas in the destination database, implicit conversion may occur because the data types of columns in the destination table are not supported by OMS. As a result, the data types of the columns in the source and destination tables are inconsistent.

  • If the length of a column in the destination table is shorter than that in the source table, the data in the column may be truncated, resulting in inconsistent data between the source and destination tables.

• Take note of the following points if you want to migrate data from a TiDB database of a version earlier than V4.0:

  • TiDB Binlog does not send heartbeats. If no updates are made in the source database, the data timestamp will not be updated.

  • If no updates are made in the source database before the start of incremental synchronization, the task will fail and return the data not existed error.

  • For a table without a primary key, TiDB Binlog cannot obtain the other unique keys. The system treats all fields as the primary key. When an update operation is performed on the source table, it is deleted and inserted in the destination table.

• If you select only Incremental Synchronization when you create a data migration task, the local incremental logs in the source database must be retained for more than 48 hours.

  ​​​If you select Full Migration + Incremental Synchronization when you create a data migration task, the local incremental logs in the source database must be retained for at least seven days. Otherwise, the data migration task may fail or the data in the source and destination databases may be inconsistent because OMS cannot obtain incremental logs.

Data type mappings

Procedure

1. Create a data migration task.

   1. Log in to the OMS Community Edition console.

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

   3. On the Data Migration page, click New Task in the upper-right corner.

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

   Parameter	Description
   Task Name	We recommend that you use a name that contains Chinese characters, numbers, and letters. The name cannot contain spaces and must be 64 characters or less in length.
   Tags (Optional)	Click the field and select a tag from the drop-down list. You can also click Manage Tags to create, modify, or delete a tag. For more information, see Use tags to manage data migration tasks.
   Source	If you have created a TiDB data source, you can select one from the drop-down list. If none exist, click New Data Source in the drop-down list and configure the settings in the dialog box that appears. For more information, see Create a TiDB data source. Note: If no valid Kafka data source or topic information is bound to the source TiDB data source, incremental synchronization is not supported. If consumer authentication control is enabled for the Kafka server, add properties={"group.id":"The consumer group of your user environment. OMS Community Edition default is oms_jdbc_connector_null"} to the source field of the incremental synchronization task.
   Destination	If you have created an OceanBase Community Edition data source, you can select one from the drop-down list. If none exist, click New Data Source in the drop-down list and configure the settings in the dialog box that appears. For more information, see Create an OceanBase Community Edition data source.

3. Click Next.

4. In the dialog box that appears, click Noted.

   Note that full data migration and verification of tables without primary keys or tables with non-null unique indexes are supported, but not incremental synchronization.

5. On the Select Migration Type page, configure the parameters.

   In the Migration Type drop-down list, the values are Schema Migration, Full Migration, Incremental Synchronization, Full Verification, and Reverse Increment.

   Migration Type	Description
   Schema Migration	After you select this option, OMS Community Edition migrates data objects such as tables, indexes, constraints, comments, and views from the source database to the destination database. Temporary tables are automatically filtered.. Manual Table Schema Adjustment Required: OMS Community Edition allows you to customize the schema of tables. If you select this option, OMS Community Edition only retrieves the schema SQL statements in the schema migration phase and starts to execute the statements only after you modify them. Target Table Storage Type: This parameter is displayed only when you have selected Schema Migration and the source data is from a TiDB database of a version earlier than V4.3.0. For more information about this parameter, see default_table_store_format. The parameter determines the storage type of table objects in the destination database during schema migration or incremental synchronization. Note The Default value is adaptively determined based on the parameters of the destination. It specifies the storage type for table objects in the destination.
   Full Migration	After you select this option, OMS Community Edition migrates the existing data in the tables of the source TiDB database to the corresponding tables in the destination OceanBase Community Edition database. Before you migrate data to an OceanBase Community Edition database from a TiDB database, we recommend that you run the ANALYZE statement to collect statistics about the TiDB database.
   Incremental Synchronization	After you select this option, OMS Community Edition synchronizes the data that is added, modified, or deleted in the source database to the corresponding tables in the destination database. The Incremental Synchronization parameter DML Synchronization supports the values Insert, Delete, and Update, and you can select the required types. If you use a TiDB database of a version earlier than V4.x and have not bound a Kafka data source when you create the source TiDB data source, you cannot select this option.
   Full Verification	After full migration is completed and incremental data is synchronized to the destination database and is on par with the source data, OMS Community Edition automatically initiates a full data verification task for the source and destination tables. If you select Full Verification, we recommend that you collect statistics about the source and destination TiDB databases before the full verification starts. For more information about how to collect statistics in an OceanBase Community Edition database, see Manually collect statistics. If you select Incremental Synchronization, and have not selected all DML operations in DML Synchronization, OMS Community Edition does not support full data verification in this scenario.
   Reverse Increment	After this option is selected, OMS Community Edition can synchronize the changes in the destination database to the source database in real time after the business switch. In the following cases, you cannot select this option: Data converges in multiple tables. Multiple schemas map to one schema.

6. (Optional) Click Next. If you have selected Reverse Increment, and the OceanBase Community Edition data source at the destination does not have a configured URL, a username, or a password, the More about Data Sources dialog box appears, reminding you to configure the parameters. For more information, see Create an OceanBase Community Edition data source.

   Complete the configuration and click Test connectivity. If the connection is successful, click Save.

7. Click Next. On the Select Migration Objects page, select the migration objects and scope.

   You can select the migration objects through the Specify Objects or Match Rules option. This topic introduces the procedure for selecting migration objects through the Specify Objects option. For more information about how to configure matching rules, see Configure matching rules for migration objects.

   Notice

   • The name of a table to be migrated and the names of columns in the table must not contain Chinese characters.

   • If the name of a database or table contains double dollar signs ($$), the data migration task cannot be created.

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

   2. In the Specify Migration Scope section, select the Source Object(s) list. You can select one or more databases and tables as migration objects.

   3. Click > to add the selected objects to the Target Object(s) list.

   OMS Community Edition allows you to import objects by using a text file, rename objects, and remove one or all migration objects.

   Operation	Steps
   Import Objects	In the right-side list of the Specify Migration Scope area, click Import Objects in the upper-right corner. In the dialog box, click OK. Note: The import will overwrite previous operation selections, so proceed with caution. In the Import Migration Objects dialog box, import the objects that need to be migrated. You can perform operations such as renaming database tables by importing a CSV file. For more information, see Download and Import Migration Object Settings. Click Validate. After passing the legality validation, click OK.
   Rename	OMS Community Edition allows you to rename migration objects. For more information, see Rename a database or table.
   Remove/Remove all	OMS Community Edition allows you to remove one or all selected objects at the target. Remove a migration object Hover the pointer over the target object in the right-side list of the Specify Migration Scope section, and click Remove in the drop-down list. Remove all migration objects Click Remove All in the upper-right corner of the right-side list of the Specify Migration Scope section. In the dialog box that appears, click OK.

8. Click Next. On the Migration Type page, configure the parameters.

   If you want to view or modify the parameters of the full migration or incremental synchronization component, click Full Migration or Incremental Synchronization and then click Configuration Details in the upper-right corner of the component. For more information about the parameters, see Component parameters.

   • Full Migration

     On the Select Migration Type page, you can select Full Migration to display the following parameters.

     Parameter	Description
     Concurrency	Valid values: Stable, Normal, Fast, and Custom. The resources required for a full migration task vary with the performance of full migration. If you set the value of the Custom parameter to Custom, you can set the Read Concurrency, Write Concurrency, and JVM Memory parameters according to your business requirements.
     Handling strategy when target objects exist	Valid values: Ignore and Stop Migration: If you set the value to Ignore, OMS Community Edition writes data to a target table only when the original data and the data to be written do not conflict. In the case of a conflict, OMS Community Edition logs the conflicting data and retains the original data. Notice If you select Ignore, the IN mode is used to pull data during full verification, which cannot detect cases where the target contains data not present in the source, and the verification performance will be degraded to some extent. If you set the value to Stop Migration, OMS Community Edition returns an error if the target table contains data during full migration. You must remove the data in the target before you continue the migration. Notice If you click Restore after an error occurs, OMS Community Edition will ignore this setting and continue to migrate table data. Proceed with caution.
     Write Method	Valid values: SQL and Direct Load. For more information about Direct Load, see Overview.
     Allow Index Postponement	Specifies whether to create indexes after full data migration. Index postponement can shorten the full migration time. For more information about how to set this parameter, see the following Considerations. Notice You must select both Schema Migration and Full Migration on the Migration Type page to set this parameter.

     If you allow index postponement, we recommend that you adjust the parameters based on the hardware conditions of OceanBase Community Edition and the current business traffic.

     • If you use OceanBase Community Edition V4.x, use the CLI tool to adjust the following sys tenant parameters and business tenant parameters.

       • Adjust sys tenant parameters

         // parallel_servers_target specifies the number of parallel query queues on each server.
         // If you want to improve performance, you can set this parameter to a value greater than the number of physical CPU cores, for example, 1.5 times the number of cores. The value you set cannot exceed 64 to avoid deadlocks in the OceanBase Community Edition kernel.
         set global parallel_servers_target = 64;
         

       • Adjust business tenant parameters

         // _temporary_file_io_area_size specifies the file memory buffer size.
         alter system set _temporary_file_io_area_size = '10' tenant = 'xxx';
         // Set sys_bkgd_net_percentage to 100 to disable throttling in 4.x.
         alter system set sys_bkgd_net_percentage = 100;
         

     • If you use OceanBase Community Edition V3.x, use the CLI tool to adjust the following sys tenant parameters.

       // parallel_servers_target specifies the number of parallel query queues on each server.
       // If you want to improve performance, you can set this parameter to a value greater than the number of physical CPU cores, for example, 1.5 times the number of cores. The value you set cannot exceed 64 to avoid deadlocks in the OceanBase Community Edition kernel.
       set global parallel_servers_target = 64;
       // data_copy_concurrency specifies the maximum number of concurrent data migration replication tasks executed in the system.
       alter system set data_copy_concurrency = 200;
       

   • Incremental Synchronization

     On the Select Migration Type page, you can select Incremental Synchronization to display the following parameters.

     Parameter	Description
     Concurrency	Valid values: Stable, Normal, Fast, and Custom. The resources required for an incremental synchronization task vary with the performance of incremental synchronization. If you set the value of the Custom parameter to Custom, you can set the Read Concurrency, Write Concurrency, and JVM Memory parameters according to your business requirements.
     Time to Keep Incremental Records	This parameter is displayed only when you have not selected Bind Kafka Data Source for the newly created TiDB data source. It specifies the retention period of incremental parsing files in OMS Community Edition. The longer the retention period, the more disk space in the OMS Community Edition store component is consumed.
     Start Point of Incremental Synchronization	This parameter is not displayed if you have selected Full Migration. If you have not selected Full Migration but have selected Incremental Synchronization without selecting Bind Kafka Data Source when you create the TiDB data source, you can specify the start point of incremental synchronization for data migrated after a specific point in time. The default value is the current system time. For more information, see Set the start point of incremental synchronization.
     Kafka Group.id (Optional)	This parameter is displayed only when you have selected Bind Kafka Data Source for the newly created TiDB data source. group.id uniquely identifies a consumer group in Kafka.

   • Full Verification

     On the Select Migration Type page, you can select Full Verification to display the following parameters.

     Parameter

     Description

     Concurrency

     Valid values: Stable, Normal, Fast, and Custom. The resources required for the full verification component Full-Verification are limited by the parameter configurations, which can limit the resource consumption of the full verification phase. If you set the value of the Custom parameter to Custom, you can set the Read Concurrency, Write Concurrency, and JVM Memory parameters according to your business requirements.

9. Click Precheck to perform a precheck on the data migration task.

   In the Precheck step, OMS Community Edition checks whether the database user has the read/write permissions and whether the database connection is normal. The data migration task will not be started unless all checks pass. If the precheck fails:

   • You can troubleshoot the issues and re-run the precheck until it succeeds.

   • You can also click Skip in the operation column of the failed precheck item. A dialog box will appear, showing the impact of skipping the operation. After you confirm the skip, click OK in the dialog box.

10. Click Start Task. If you do not want to start the task immediately, you can click Save to go to the details page of the data migration task, where you can start the task later.

    OMS Community Edition allows you to modify the migration objects during a data migration task. For more information, see View and modify migration objects. After you start a data migration task, it will go through the selected migration phases in order. For more information, see View details of a data migration task in the View Migration Details section.

References

• Create a TiDB data source

• Create an OceanBase-CE data source

• Component parameters

• View and modify migration objects

• View details of a data migration task