Migrate data from OceanBase Database in MySQL-compatible mode to a MySQL database

You can create a data migration task to seamlessly migrate the existing business data and incremental data from the source database to the target database by performing schema migration, full migration, and incremental synchronization.

Prerequisites

• You have created a cluster and a tenant in the source OceanBase database. For more information, see Create an instance and Create a tenant.

• You have created a target database instance.

  • For AWS: You have created an Aurora MySQL database instance. For more information, see Create an Aurora MySQL database instance.

  • For Huawei Cloud: You have purchased an RDS for MySQL database instance. For more information, see Purchase a database instance.

  • For Google Cloud: You have created a Cloud SQL instance. For more information, see Create an instance.

  • For Alibaba Cloud: You have created an RDS MySQL database instance or purchased a PolarDB MySQL cluster.

• You have created dedicated database users for data migration on both the source and target sides and granted them the required 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.

• Currently, data migration supports Aurora MySQL V2.x, V3.x, Cloud SQL V5.6, V5.7, V8.0, and V8.4, Huawei Cloud RDS MySQL V5.6, V5.7, V8.0, and V8.4, MySQL Database V5.6, V5.7, V8.0, and V8.4, and OceanBase Database in MySQL-compatible mode V2.x, V3.x, and V4.x.

• 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 does not support case-sensitive mode. If a database or table with the same name but different case exists at the target, the precheck will report an error.

• When the target is a database, the data migration service does not support triggers (Triggers) at the target. If triggers exist, data migration may fail.

Considerations

• 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, UTF-16, etc.) to avoid issues such as garbled text at the target caused by character set incompatibility.

• Clock desynchronization between nodes, or between the client terminal and the server, may cause inaccurate latency reporting during incremental synchronization.

  For example, if a clock is earlier than standard time, the reported latency may be negative. If a clock is later than standard time, it may cause a positive latency.

• If DDL synchronization is disabled, you must restart the data migration task if you modify a unique index at the target. Otherwise, data inconsistency may occur.

• Verify whether the migration precision of 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 field type, truncation may occur, leading to data inconsistency between the source and target.

• In a data migration task where the source is an OceanBase database and DDL synchronization is enabled, if a rename (RENAME) operation occurs on a source database table, it is recommended to restart the task to prevent data loss during incremental synchronization.

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

  • When manually creating a table schema, if it falls outside the supported scope of data migration, implicit conversion issues may arise, causing inconsistent column types between the source and target.

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

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

  If you configure Full Migration + Incremental Synchronization when creating a data migration task, the local incremental logs in the source database must be retained for at least 7 days. Otherwise, the data migration task may fail due to inability to obtain incremental logs, potentially causing data inconsistency between the source and target.

• If you perform incremental synchronization with OceanBase Database as the source and the database table objects contain UDT columns, the incremental synchronization task may fail due to unsupported UDT columns.

Supported source and target instance types

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. In the Configure Source & Target step, configure the parameters.

   1. In the Source section, configure the parameters.

      If you need to reference an existing and saved 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 area 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 the documentation in the Data Sources module.

      Parameter	Description
      Cloud Provider	Currently supports AWS, Huawei Cloud, Google Cloud, and Alibaba Cloud.
      Database Type	Select OceanBase MySQL Compatible for the source.
      Instance Type	Includes Dedicated (Transactional) and Self-managed Database. Currently, only data from a Dedicated (Transactional) instance can be migrated to a MySQL database.
      Region	Select the region where the source database is located.
      Connection Type	Includes Endpoint and Public IP. If you choose the Endpoint connection method, you must first 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 details, see the documentation in the Select Private Network Connection section. If you choose the Public IP connection method, you must first add the data source IP address displayed on the page to the allowlist of the OceanBase database instance to ensure connectivity. For details, see the documentation in the Select Public Network Connection section. Note This parameter is displayed only when the instance type is a self-managed database. After you select the regions for the source and target, the page will display the data source IP address that needs to be added to the allowlist.
      Connection Information	This parameter is displayed only when the instance type is a self-managed database. 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.
      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 For Alibaba Cloud as the cloud provider, cross-account authorized Alibaba Cloud root account instances are also supported. For details, see Alibaba Cloud Account Authorization.
      Tenant	The ID or name of the tenant in MySQL-compatible mode of OceanBase Database. You can expand the target instance on the Instances page to view the ID or name of the target tenant under that instance.
      Database Account	The username of the user for the MySQL-compatible mode tenant of OceanBase Database used for data migration.
      Password	The password of the database user.

      When the instance type is Self-managed Database, if you need to perform schema migration and incremental synchronization, configure the parameters in the Advanced Settings section.

      

      • If you need to select Schema Migration or Incremental Synchronization in the Select Type & Objects step, enable the sys Tenant Account and configure the following parameters.

        Parameter	Description
        Sys Account	The name of the sys user. This user is mainly used to read incremental logs and database object structure information from OceanBase Database. Create it under the sys tenant of the business cluster.
        Password	The password of the sys user.

      • If you need to select Incremental Synchronization in the Select Type & Objects step, enable OBLogProxy and fill in the OBLogProxy connection information.

        Note

        To enable incremental synchronization, you must enable both the sys tenant account and OBLogProxy.

        OBLogProxy connection information is an incremental log proxy service for OceanBase Database. It provides access and management capabilities for real-time incremental projects as a service, facilitating applications to access incremental logs of OceanBase Database. It also meets the need to subscribe to incremental logs when network isolation is in place. The format is OBLogProxy IP:OBLogProxy Port.

   2. In the Target section, configure the parameters.

      If you need to reference a created and saved data source, click Quick Fill on the right side of Target and select the target data source from the drop-down list. After selection, the configurations in the target area will be automatically populated. To save the current configuration as a new data source, click the Save icon in the upper-right corner of the Target area.

      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 details, see the documentation in the Data sources module.

      Parameter	Description
      Cloud Provider	Currently supports AWS, Huawei Cloud, Google Cloud, and Alibaba Cloud. You can choose the same cloud provider as the source or migrate data across cloud providers. Note The cross-cloud provider migration feature is not currently enabled by default. To use this feature, contact OceanBase Cloud Technical Support.
      Database Type	Select MySQL for the target.
      Instance Type	When the cloud provider is AWS, the instance types supported are Aurora MySQL, RDS MySQL, and Self-managed MySQL. When the cloud provider is Huawei Cloud, the instance types supported are RDS MySQL and Self-managed MySQL. When the cloud provider is Google Cloud, the instance types supported are Cloud SQL and Self-managed MySQL. When Cloud Vendor is Alibaba Cloud, the instance types supported are RDS MySQL, PolarDB MySQL, and Self-managed MySQL.
      Region	Select the region where the target database is located.
      Connection Type	Includes Endpoint and Public IP. If you choose the Endpoint connection method, you must first add the account ID displayed on the page to the allowlist of your Endpoint Service to allow connections from this account to the Endpoint Service. For details, see Select Private Network Connection. If Cloud Vendor is set to AWS and you selected Required for the Require Acceptance to Use Endpoint parameter when creating the Endpoint Service, the Data Migration Service will prompt you to enter the AWS console to perform the Accept endpoint connection request operation for the received Connections request upon first accessing the private connection. If the cloud provider is Google Cloud, please add the authorized project to Published Services. After authorization, manual reauthorization is not required during data source test connections. If you choose the Public IP connection method, you must first add the data source IP address displayed on the page to the allowlist of the MySQL database instance to ensure connectivity. For details, 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 the source and target.
      Connection Information	If Connection Type is set to Endpoint, enter the name of the Endpoint Service. If Connection Type is set to Public IP, enter the IP address and port number of the database host.
      Database Account	The username of the MySQL database user 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.

   1. In the Sync Topology section, select One-way Sync.

      Data migration supports One-way Sync and Two-way Sync. This topic describes one-way synchronization. For details on two-way synchronization, see Configure a two-way synchronization task.

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

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

      Parameter	Description
      Schema Migration	Schema migration requires defining character set mappings yourself. Data migration only replicates the source database's data (schema) to the target database without affecting the source data (schema). Schema migration from OceanBase Database in MySQL-compatible mode to a MySQL database supports automatically creating databases that do not exist at the target.
      Full Migration	After a full migration task starts, 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 includes DML Synchronization and DDL Synchronization, which you can configure as needed. For details, see Configure DML/DDL synchronization.

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

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

      

   4. 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 allows you to select one or more tables or views in a database as migration objects. Full Database Migration allows you to select an entire database as a 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 you select 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, and you can rename target objects, set row filters, view column information, and remove one or all migration objects.

        

        Note

        When you select Full Database Migration:

        • The list on the right only displays the database name; it does not support displaying specific objects.

        • If you select the Incremental Synchronization-DDL Synchronization migration type, newly added tables at the source can be synchronized to the target.

        Operation	Description
        Import Object	In the list on the right side of the selection area, click Import Object in the upper-right corner. For details, see Import migration objects.
        Rename	Data Migration supports renaming migration objects. For details, see Rename a database or table.
        Row Filter	Data Migration supports filtering rows using WHERE conditions. For details, see Filter data with SQL conditions. 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 list on the right side of the selection area, click the Remove icon next to the target object to remove it. Remove all migration objects In the list on the right side of the selection area, click Clear All in the upper-right corner. In the dialog box, click OK to remove all migration objects.

      • To select Match by Rule, see Configure a matching rule for database-to-database migration.

6. Click Next. In the Migration Options step, configure the parameters.

   • Full Migration

     The following parameters are displayed only if you have selected One-way Sync > Full Migration in the Select Type & Objects step.

     Parameter	Description
     Read Concurrency Configuration	This parameter specifies the number of concurrent reads from the source during the full migration phase. The maximum limit is 512. A high concurrency value may cause excessive load on the source and impact business operations.
     Write concurrency configuration	This parameter specifies the number of concurrent data writes to the target during the full migration phase. The maximum value is 512. A high concurrency level may overwhelm the target and impact your business.
     Whether to limit the full migration rate	You can decide whether to enable rate limiting for full migration 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). Note The RPS and BPS values specified here are only for rate limiting. The actual achievable performance during full migration is subject to factors such as the source, target, and instance specifications.
     Handling strategy when records exist in the target table object	The handling strategies include Stop Migration and Ignore: If you select Stop Migration, a full migration error will be reported if the target table object contains data. You must handle the data in the target before continuing the migration. Note If you click Resume after an error occurs, data migration will ignore this configuration option and continue migrating table data. Proceed with caution. If you select Ignore, when the target table object contains data, if the original data conflicts with the data to be written, data migration will log the conflicting data and retain the original data unchanged during write operations.
     Whether to allow index creation later	The feature to create indexes later can shorten the time required for a full migration. You can set whether to allow indexes to be created after the full data migration is complete. For considerations on selecting index creation later, see the description below the table. Note This option is available only if you have selected both Schema Migration and Full Migration on the Select Migration Type page. Only non-unique key indexes support creation later.

     If index creation later 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.

     // File memory buffer limit
     ALTER SYSTEM SET _temporary_file_io_area_size = '10' tenant = 'xxx'; 
     // Disable throttling 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 One-way Sync > Incremental Synchronization in the Select Type & Objects step.

     Parameter	Description
     Write Concurrent 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 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 flow rate that can be synchronized to the target per second during incremental synchronization). Note The RPS and BPS set here are only for rate limiting. The actual achievable performance during incremental synchronization is subject to factors such as the source, target, and instance specifications.
     Incremental Synchronization Start Timestamp	If Full Migration was selected as the migration type, this parameter is not displayed. If Full Migration was not selected but Incremental Synchronization was selected, specify a point in time after which data should be migrated. The default is the current system time. For details, see Set an incremental synchronization timestamp.

7. Click Next. The system performs a precheck on the data migration task.

   In the Pre-check phase, data migration checks whether the read/write permissions of the database user and the network connection of the database meet the requirements. The data migration task can only be started after all check items pass. If the precheck reports an error:

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

   • You can also click Skip in the Actions column for the failed precheck item. A dialog box appears, explaining the specific impact of skipping this operation. After confirming you can skip it, 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 a successful purchase, you can start the data migration task. For purchase 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 details page of the data migration task. 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 according to the selected migration type. For details, see the content in the "View migration details" section of View the details of a data migration task.