Migrate data from a TiDB database to the MySQL-compatible mode of OceanBase Database

You can create a data migration task to migrate the existing business data and incremental data from the source database to the target database in MySQL-compatible mode of OceanBase Database through schema migration, full migration, and incremental synchronization.

Background information

TiDB Database supports hybrid transactional and analytical processing (HTAP) and is a converged distributed database product. You need to deploy a TiCDC cluster and a Kafka cluster to synchronize incremental data from TiDB Database to the MySQL-compatible mode of OceanBase Database.

TiCDC is an incremental data synchronization tool for TiDB Database. It uses a PD cluster (the scheduling module of a TiDB cluster, typically consisting of three PD nodes) to ensure high availability. A TiKV server is a TiKV node in a TiDB cluster that proactively sends change data to the TiCDC cluster in the form of change logs. The TiCDC tool uses multiple TiCDC processes to obtain data from TiKV nodes, process it, and then synchronize it to the Kafka cluster. The Kafka cluster stores the incremental log information of TiDB Database converted by the TiCDC tool. During incremental data synchronization, data migration obtains the corresponding data from the Kafka cluster and migrates it to the MySQL-compatible mode of OceanBase Database in real time. If you do not bind a Kafka data source when creating a new TiDB data source, incremental synchronization cannot be performed.

Prerequisites

• You have created a source database instance.

• You have created a cluster and tenant for the target OceanBase Database. For more information, see Create an instance and Create a tenant.

• You have created dedicated database users for data migration on both the source and target sides and granted them the relevant permissions. For more information, see User privileges.

Limitations

• Only users with the project roles of Project Owner, Project Admin, or Data Services Admin can create a data migration task.

• Limitations on the source database

  Do not perform DDL operations that modify database or table schemas during schema migration or full migration. Otherwise, the data migration task may be interrupted.

• Data migration currently supports TiDB Database V4.x, V5.4, V6.x, and V7.x, and OceanBase Database MySQL-compatible mode V2.x, V3.x, and V4.x.

• When migrating data from TiDB Database to the MySQL-compatible mode of OceanBase Database, DDL synchronization is not supported.

• The data migration service only supports migrating objects whose database, table, and column names are in ASCII and do not contain special characters (including .|"'`()=;/& and line breaks).

• The data migration service only supports the TiCDC Open Protocol; other protocols are not supported. If you use an unsupported protocol, the JDBC-Connector may throw an exception indicating a null pointer error.

• The data migration service does not support triggers (Triggers) on the target side. If triggers exist, data migration may fail.

• If you choose TiCDC as the incremental synchronization method, refer to the TiDB official documentation for usage limitations. Otherwise, data inconsistency may occur.

Considerations

• If foreign keys with the same name exist at the source, schema migration will report an error. You can modify the foreign key constraint names and then resume the task.

• If the source character set is UTF-8, it is recommended to use a character set compatible with the source at the target (for example, UTF-8 or UTF-16) to avoid issues such as garbled characters at the target due to character set incompatibility.

• Do not write data to a topic used for synchronization by TiCDC. Otherwise, the JDBC-Connector may throw an exception indicating a null pointer error.

• If you need to modify a unique index at the target, you must restart the data migration task. Otherwise, data inconsistency may occur.

• In database/table aggregation scenarios:

  • It is recommended that you use matching rules to map the relationship between the source and target.

  • We recommend that you create the table structure on the target side yourself. If you use the data migration service to create it, skip the failed objects in the schema migration step.

• If the table structures at the source and target are not identical, data inconsistency may occur. Known scenarios include:

  • When manually creating a table structure, if it exceeds the supported scope of data migration, implicit conversion issues may arise, leading to inconsistent column types between the source and target.

  • If the data length at the target is shorter than at the source, data truncation may occur, causing inconsistency between the source and target data.

• If table objects differing only in case exist at the source or target, the data migration result may not meet expectations due to case-insensitivity at the source or target.

• Clock desynchronization between nodes, or between the client and the server, can lead to inaccurate latency reporting during incremental synchronization.

  For example, if a clock is ahead of standard time, the reported latency may be negative. If a clock is behind, it may cause a positive latency.

• Verify whether the data migration service's precision for migrating column types such as DECIMAL, FLOAT, and DATETIME meets your expectations. If the precision of the target field type is lower than that of the source, truncation may occur, leading to data inconsistency between the source and target.

• If you configure only Incremental Synchronization when creating a data migration task, the service requires local incremental logs in the source database to be retained for more than 48 hours.

  If you configure both Full Migration and Incremental Synchronization when creating the task, the service requires local incremental logs in the source database to be retained for at least 7 days. Otherwise, the data migration task may fail or cause data inconsistency between the source and target due to inability to obtain incremental logs.

Supported source and target instance types

In the following table, OceanBase MySQL Compatible supports the following instance types: Dedicated (Transactional) and Dedicated (Analytical).

Data type mappings

Procedure

1. Create a data migration task.

   

   1. Log in to the OceanBase Cloud console.

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

   3. On the Data Migration page, click the Data Migration tab.

   4. On the Data Migration tab, click Create Task in the upper-right corner.

2. In the Edit Task Name text box, enter a custom name for the migration task.

   It is recommended to use a combination of Chinese characters, numbers, and letters. The name must not contain spaces and cannot exceed 64 characters in length.

3. On the Configure Source & Target page, configure the parameters.

   1. In the Source section, configure the parameters.

      If you need to reference an existing data source, you can click Quick Fill next to Source and select the target data source from the drop-down list. After selection, the configurations for the source section will be automatically populated. If you want to save the current configuration as a new data source, click the Save icon in the upper-right corner of the Source section.

      You can also click Manage Data Source in the Quick Fill drop-down list to go to the Data Source page to view and manage data sources. This page provides unified management for different types of data sources. For more information, see Data sources.

      Parameter	Description
      Cloud Provider	Currently supports Alibaba Cloud, AWS, and Google Cloud.
      Database Type	Select TiDB for the source.
      Instance Type	Currently only supports Self-managed TiDB.
      Region	Select the region where the source database is located.
      Connection Type	Includes Endpoint and Public IP. For Alibaba Cloud, AWS, and Google Cloud, you can also select Peering Connection. If you select Endpoint, add the account ID displayed on the page to the allowlist of your Endpoint service to allow connections from this account's endpoint to the Endpoint service. For more information, see Select Private Network Connection. When AWS is selected for Cloud Vendor and Require Enable is selected for the Require Accept to Use Endpoint parameter when creating the Endpoint service, the data migration service prompts you to open the AWS console and perform the Accept endpoint connection request operation for the incoming request the first time it accesses the private connection. When the cloud provider is set to Google Cloud, add the authorized project to Published Services. After authorization, no further manual authorization is required when you test the data source connection. If you select Public IP, add the data source IP address displayed on the page to the allowlist of the TiDB database instance to ensure connectivity. For more information, see Select Public Network Connection. Note The data source IP address to be added to the allowlist is displayed on the page only after you have selected the regions for both the source and target. If you select Peering Connection, choose an available peering connection from the drop-down list. Add the IP address displayed on the page to the TiDB instance's inbound security group rules or allowlist to ensure connectivity. For more information, see AWS peering connection, Alibaba Cloud peering connection, and Google Cloud peering connection.
      Peering Connection ID	This parameter is displayed only when Connection Type is set to Peering Connection. Select an available peering connection from the drop-down list. The list displays only connections that match the current cloud provider and region and are in the Connected state. It also displays the corresponding VPC ID and CIDR block. If no connection is available, click Create to open a new tab and create a data service peering connection.
      Connection Information	When Connection Type is set to Endpoint, enter the name of the Endpoint service. When Connection Type is set to Public IP, enter the IP address and port number of the database host. When Connection Type is set to Peering Connection, enter the private IP address or hostname and port number of the database host.
      Database Account	The username of the TiDB database user used for data migration.
      Password	The password of the database user.

      If you need to obtain incremental data from TiDB via Kafka, please fill in the Incremental Sync Settings section to enable the Binlog and TiCDC data sources. If you do not configure this section, you will not be able to select Incremental Synchronization on the Select Type & Objects page.

      Parameter	Description
      Kafka Data Source	The Kafka data source is used to store information about incremental logs converted by the Binlog and TiCDC tools for consumption by data migration. Notice Kafka data sources from the same cloud provider as TiDB are supported.
      Topic	Select the topic of the Kafka data source to bind from the drop-down list.
      TiDB Data Format	When migrating data from a TiDB database to the MySQL-compatible mode of OceanBase Database, you can choose data in TiCDC or TiDB Binlog format. Select from the drop-down list.

   2. In the Target section, configure the parameters.

      If you need to reference an existing data source, you can click Quick Fill next to Target and select the target data source from the drop-down list. After selection, the configurations for the target section will be automatically populated. If you want to save the current configuration as a new data source, click the Target icon in the upper-right corner of the Save section.

      You can also click Manage Data Source in the Quick Fill drop-down list to go to the Data Source page, where you can view and manage data sources. This page provides unified management for different types of data sources. For more information, see the documentation in the Data sources module.

      Parameter	Description
      Cloud Provider	Currently supports Alibaba Cloud, AWS, and Google Cloud. You can choose the same cloud provider as the source or perform cross-cloud data migration. Note The cross-cloud data migration feature is not currently available by default. To use this feature, contact OceanBase Cloud technical support.
      Database Type	Select OceanBase MySQL Compatible as the target database type.
      Instance Type	Currently supports Cluster Instance (Transactional) and Cluster Instance (Analytical).
      Region	Select the region where the target database is located.
      Instance	The ID or name of the instance where the tenant in MySQL-compatible mode of OceanBase Database resides. You can view the ID or name of the target instance on the Instances page. Note When the cloud provider is Alibaba Cloud, you can also select an Alibaba Cloud root account instance with cross-account authorization. For more information, see Alibaba Cloud Account Authorization.
      Tenant	The ID or name of the tenant in MySQL-compatible mode of OceanBase Database. You can view the ID or name of the target tenant under the target instance by expanding the target instance on the Instances page. Note This parameter is not displayed when the instance type is Cluster Instance (Analytical).
      Database Account	The username of a tenant in MySQL-compatible mode of OceanBase Database used for data migration.
      Password	The password of the database user.

4. Click Test and Continue.

5. On the Select Type & Objects page, configure the parameters.

   Note

   Currently, when migrating data from a TiDB database to the MySQL-compatible mode of OceanBase Database, only unidirectional synchronization is supported.

   1. In the Migration Type section, select the migration type for the current data migration task.

      The Migration Type includes Schema Migration, Full Migration, and Incremental Synchronization.

      Parameter	Description
      Schema Migration	Schema migration requires you to define the character set mapping relationship yourself. Data migration only copies the data (schema) from the source database to the target database without affecting the source data (schema).
      Full Migration	After a full migration task starts, the data migration service migrates the existing data from the source database tables to the corresponding tables in the target database.
      Incremental Synchronization	After an incremental synchronization task starts, data migration synchronizes the changed data (newly added, modified, or deleted) from the source database to the corresponding tables in the target database. Incremental Synchronization supports only DML Synchronization, which you can configure as needed. For more information, see Configure DML/DDL synchronization.

   2. In the Select Migration Objects section, configure the method for selecting migration objects.

      You can select migration objects using either Specify Objects or Match by Rule.

      

   3. In the Select Migration Scope section, select the objects to migrate.

      • When you select Specify Objects, data migration supports Table-level and Database-level. Database table migration supports selecting one or more tables or views from a database as migration objects, while full database migration supports selecting an entire database as the migration object. If you select database table migration for a database, that database cannot be used for full database migration. If you select full database migration for a database, that database cannot be used for database table migration.

        After selecting Table-level or Database-level, select the objects you want to migrate on the left and click > to add them to the list on the right.

        Data migration supports importing objects via text files, and you can rename target objects, set row filters, view column information, and remove single or all migration objects.

        

        Note

        When you select Whole database migration, only the database name is displayed in the list on the right; specific objects are not supported.

        Action	Description
        Import objects	In the right-side list of the selection area, click Import Object in the upper-right corner. For more information, see Import migration objects.
        Rename	Data migration supports renaming migration objects. For more information, see Rename a database or table.
        Row filter	Data migration supports filtering rows using WHERE conditions. For more information, see Filter data by SQL condition. You can also view the column information of a migration object in the View Column section.
        Remove/Clear All	Data migration supports removing one or more temporarily selected objects to the target during data mapping. Remove a single migration object In the right-side list of the selection area, click the Remove icon next to the target object to remove it. Remove all migration objects In the right-side list of the selection area, click Clear All in the upper-right corner. In the dialog box, click OK to remove all migration objects.

      • Select Match by Rule. For more information, see Configure database-to-database matching rules.

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

   *Full migration

    The following parameters are displayed only if you have selected **<UI-TERM key="oms-cloud.components.SyncTopology.constant.OneWaySynchronization">One-way Sync</UI-TERM>** > **<UI-TERM key="oms-cloud.MigrationParamsConfig.MigrationParamsDetail.constants.FullMigration">Full Migration</UI-TERM>** in the **<UI-TERM key="oms-cloud.DataVerify.New.CreateTaskSteps.SelectTypeAndObject">Select Type & Objects</UI-TERM>** step.
   
    |Parameter|Description|
    |--------------------|--------------------|
    |Read Concurrency Configuration| This parameter specifies the number of concurrent reads from the source during full migration. The maximum value is 512. A high concurrency value may cause excessive load on the source and affect business operations.|
    |Write Concurrency Configuration| This parameter specifies the number of concurrent writes to the target during full migration. The maximum value is 512. A high concurrency value may cause excessive load on the target and affect business operations.|
    |Whether to Limit Full Migration Rate| You can decide whether to enable the full migration rate limit based on your actual needs. If enabled, set the source read RPS (the maximum number of rows that can be read from the source per second during full migration), source read BPS (the maximum traffic that can be read from the source per second during full migration), target write RPS (the maximum number of rows that can be written to the target per second during full migration), and target write BPS (the maximum traffic that can be written to the target per second during full migration).<main id="notice" type='explain'><h4>Note</h4><p>The RPS and BPS values set here are only for throttling. The actual achievable performance during full migration is subject to factors such as the source, target, and instance specifications.</p></main>|
    |Processing Strategy When Target Table Has Records| Valid values: <b><UI-TERM key="oms-cloud.components.MigrationParamsConfig.MigrationParamsDetail.NotAllowed.ignore">Stop Migration</UI-TERM></b> and <b><UI-TERM key="oms-cloud.pages.Migration.New.components.MigrationTypesObjects.Ignore">Ignore</UI-TERM></b>:<ul><li>If you select <b><UI-TERM key="oms-cloud.pages.Migration.New.components.MigrationTypesObjects.stopjob">Stop Migration</UI-TERM></b>, an error is returned when full migration attempts to migrate data because the target table contains records. In this case, you must handle the data in the target table before you can continue the migration.<main id="notice" type='notice'><h4>Notice</h4><p>If you click Resume after an error occurs, data migration ignores this configuration option and continues migrating table data. Proceed with caution.</p></main></li><li>If you select <b><UI-TERM key="oms-cloud.components.PreCheck.PreCheckTable.Ignore">Ignore</UI-TERM></b>, when the target table contains records, if the original data conflicts with the data to be written, data migration logs the conflicting data and retains the original data unchanged during write operations.</li> </ul>|
    |Whether to Allow Post-indexing| The post-indexing feature can shorten the duration of full migration. You can specify whether to allow indexes to be created after full data migration is completed. For considerations on selecting post-indexing, see the description below the table.<main id="notice" type='notice'><h4>Notice</h4><ul><li><p>This option is available only if you have selected both <b>Schema Migration</b> and <b>Full Migration</b> on the <b>Select Migration Type</b> page.</p></li><li>Only non-unique key indexes support post-creation.</li></ul></main>|
   
    If post-indexing is allowed, we recommend that you adjust the following business tenant parameters using the command-line client tool based on the hardware conditions of your OceanBase database and the current business traffic.
   
    ```SQL
    // File memory buffer size
    ALTER SYSTEM SET _temporary_file_io_area_size = '10' tenant = 'xxx';
    // Disable rate limiting for OceanBase Database V4.x
    ALTER SYSTEM SET sys_bkgd_net_percentage = 100;
    ```
   

   *Incremental synchronization

    The following parameters are displayed only if you have selected **<UI-TERM key="oms-cloud.components.SyncTopology.constant.OneWaySynchronization">One-way Sync</UI-TERM>** > **<UI-TERM key="oms-cloud.components.IncrTransferForm.IncrementalSynchronization">Incremental Synchronization</UI-TERM>** in the **<UI-TERM key="oms-cloud.New.CreateTaskComponents.useGetTaskStepLinkedList.SelectTypeAndObject">Select Type & Objects</UI-TERM>** step.
   
    |Parameter|Description|
    |---------------------|---------------------|
    |Write Concurrency Configuration| This parameter specifies the number of concurrent writes to the target during incremental synchronization. The maximum value is 512. A high concurrency value may cause excessive load on the target and affect business operations.|
    |Whether to Limit Incremental Migration Rate| You can decide whether to enable the incremental synchronization rate limit based on your actual needs. If enabled, set the target write RPS (the maximum number of data rows that can be synchronized to the target per second during incremental synchronization) and target write BPS (the maximum traffic that can be synchronized to the target per second during incremental synchronization).<main id="notice" type='explain'><h4>Note</h4><p>The RPS and BPS values set here are only for throttling. The actual achievable performance during incremental synchronization is subject to factors such as the source, target, and instance specifications.</p></main>|
    |Incremental Synchronization Start Timestamp| <ul><li>If you selected <b><UI-TERM key="oms-cloud.New.components.MigrationObjectForm.FullMigration">Full Migration</UI-TERM></b> as the migration type, this parameter is not displayed.</li><li>If you did not select <b><UI-TERM key="oms-cloud.Migration.constants.FullMigration">Full Migration</UI-TERM></b> but selected <b><UI-TERM key="oms-cloud.src.constants.migration.IncrementalSynchronization">Incremental Synchronization</UI-TERM></b> as the migration type, specify a point in time after which data should be migrated. The default is the current system time. For more information, see [Set an incremental synchronization timestamp](../300.migrate-data/400.functions/400.incremental-synchronization-timestamp.md).</li></ul>|
   

   *Advanced options

    The parameters in this section are displayed only if the target OceanBase Database is in MySQL-compatible mode V4.3.0 or later, and you have selected **<UI-TERM key="oms-cloud.MigrationParamsConfig.MigrationParamsDetail.constants.StructureMigration">Schema Migration</UI-TERM>** in the **<UI-TERM key="oms-cloud.DataVerify.New.CreateTaskSteps.SelectTypeAndObject">Select Type & Objects</UI-TERM>** step.
   
    ![migration74-en](https://obbusiness-private.oss-cn-shanghai.aliyuncs.com/doc/img/oms/oceanbase-cloud/migration74-en.png)
   
    The storage types for table objects at the target include **<UI-TERM key="obcloud-console.alerts.rule.page.Default">Default</UI-TERM>**, **<UI-TERM key="oas.src.constants.sql.LineDeposit">Row Storage</UI-TERM>**, **<UI-TERM key="oms-cloud.Migration.constants.ColumnStorage">Columnar Storage</UI-TERM>**, and **<UI-TERM key="oms-cloud.Migration.constants.RowAndRowMixed">Hybrid Row-Column Storage</UI-TERM>**. This configuration determines the storage type for table objects during schema migration or incremental synchronization.
   
    <main id="notice" type='explain'>
    <h4>Note</h4>
    <p>The <b>Default</b> option adapts to other options based on the target parameter configuration. For schema migration, table objects are written according to the set storage type.</p>
    </main>
   

7. Click Pre-check to have the system perform a precheck on the data migration task.

   During the Pre-check phase, data migration checks whether the database user's read/write permissions, network connectivity, and other requirements are met. The data migration task can only be started after all check items pass. If an error is reported in the precheck:

   • You can troubleshoot and resolve the issue, then re-execute the precheck until it succeeds.

   • You can also click Skip in the Actions column of the failed precheck item. A dialog box will pop up explaining the specific impact of skipping this operation. After confirming you can skip, click OK in the dialog box.

8. After the precheck succeeds, click Purchase to go to the Purchase Data Migration Instance page for purchase.

   After successful purchase, you can start the data migration task. For details, see Purchase a data migration instance. If you do not need to purchase a data migration instance immediately, click Save to go to the data migration task details page, where you can manually purchase it later as needed.

   You can click Configure Validation Task in the upper-right corner of the data migration details page to compare data differences between the source and target databases. For details, see "Create a data verification task".

   The data migration service supports modifying migration objects during the execution of a data migration task. For details, see View and modify migration objects. After a data migration task is started, it is executed sequentially based on the selected migration type. For details, see the "View migration details" section in View the details of a data migration task.