Migrate data from a MySQL database to the MySQL-compatible mode of OceanBase 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 in MySQL-compatible mode of OceanBase Database through schema migration, full migration, and incremental synchronization.

Prerequisites

• You have created a source 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 a cluster and tenant in 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 necessary permissions. For more information, see User privileges.

• Binlog has been enabled. For more information, see Access MySQL binary logs.

Limitations

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

• 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, V8.4, Huawei Cloud RDS MySQL V5.6, V5.7, V8.0, V8.4, MySQL databases V5.6, V5.7, V8.0, 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 ASCII-compliant and do not contain special characters (including .|"'`()=;/& and line breaks).

• If the primary key is of the FLOAT or DOUBLE type, the precheck will fail. It is strongly recommended that you do not use this type as the primary key.

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

• The data migration service does not support index fields in MySQL databases that exceed 16,000 bytes (4,000 characters).

• Ensure the clocks of the source and target databases are synchronized.

Considerations

• 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.

• Tables to be migrated must have a primary key or a unique constraint, and the fields must be unique. Otherwise, data inconsistency may occur in the target database.

• The host of the MySQL database must have sufficient outbound bandwidth. Insufficient outbound bandwidth affects log parsing and data migration speed, potentially increasing synchronization latency.

• The data migration service does not support changing the type of custom columns. You must handle database objects with special field type requirements yourself.

• The data migration service supports three types of objects: tables, indexes, and views.

• If no business data changes occur at the source, the data migration service cannot obtain logs to advance data synchronization, causing the task delay to increase continuously. This delay is not an issue with the task itself; you can resolve excessive latency by writing data to the source.

• 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 standard time, it may cause a positive latency.

• If the collation of the source and target databases is different, data verification for tables with VARCHAR as the primary key will be inconsistent.

• Without enabling DDL synchronization, if you modify a unique index on the target side, you must restart the data migration task; otherwise, data inconsistency may occur.

• Please verify that the migration precision of the data migration service for 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.

• When creating a new data migration task, if you configure only Incremental Synchronization, the data migration requires the 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 data migration requires the local incremental logs in the source database to be retained for at least 7 days. Otherwise, the data migration task may fail due to inability to obtain incremental logs, potentially leading to data inconsistency between the source and target.

Supported source and target instance types

In the following table, OceanBase MySQL Compatible supports the 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 cannot contain spaces and must not exceed 64 characters in length.

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

   1. In the Source section, configure the parameters.

      To reference an existing data source, 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, where you can view and manage data sources. This page provides unified management for different types of data sources. For more information, see the Data sources topic.

      Parameter	Description
      Cloud Provider	Currently supports AWS, Huawei Cloud, Google Cloud, and Alibaba Cloud.
      Database Type	Select MySQL for the source.
      Instance Type	If AWS is selected as the cloud provider, the instance types supported are Aurora MySQL, RDS MySQL, and Self-managed MySQL. If Huawei Cloud is selected as the cloud provider, the instance types supported are RDS MySQL and Self-managed MySQL. If Google Cloud is selected as the cloud provider, the instance types supported are Cloud SQL and Self-managed MySQL. If Cloud Vendor is set to Alibaba Cloud, the instance types supported are RDS MySQL, PolarDB MySQL, and Self-managed MySQL.
      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 Select Private Connection topic. If Cloud Vendor is set to AWS, and you selected Require to enable for the Require to accept endpoint connection requests 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 Google Cloud is selected as the cloud provider, please add the authorized project to Published Services. After authorization, manual authorization is no longer 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 the Select Public Connection topic. Note The data source IP address to be added to the allowlist is displayed 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 for the MySQL database used for data migration.
      Password	The password for the database user.

   2. In the Target section, configure the parameters.

      To reference an existing data source, 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 Save icon in the upper-right corner of the Target 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 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 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	When the target is OceanBase MySQL Compatible, Dedicated (Transactional) and Dedicated (Analytical) are supported. Dedicated (Transactional) stands for Online Transaction Processing. Dedicated (Analytical) stands for Online Analytical Processing.
      Region	Select the region where the target database will be 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 Select Private Network Connection module. 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 Select Public Network Connection module. 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 data source IP address to be added to the allowlist is displayed on the page.
      Connection Information	This parameter is displayed only when the instance type is a self-managed database. If you select Connection Type as Endpoint, enter the name of the Endpoint Service. If you select Connection Type as 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 When the cloud provider is Alibaba Cloud, you can also select an Alibaba Cloud root account instance for cross-account authorization. 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 in the MySQL-compatible mode tenant of OceanBase Database used for data migration.
      Password	The password of the database user.

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

      

      • 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 structures 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.

        Notice

        Incremental synchronization is supported only when both the sys Tenant Account and OBLogProxy are enabled.

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

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 about two-way synchronization, see Configure a two-way synchronization task.

      Notice

      Two-way synchronization is not supported when the target is an OceanBase analytical instance.

   2. 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. Data migration only copies the data (schema) from the source database to the target database without affecting the source data (schema). Schema migration from a MySQL database to a MySQL-compatible mode of OceanBase Database supports automatically creating databases that do not exist on the target side.
      Full Migration	After a full migration task starts, the data migration service migrates the existing data in 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) in the source database to the corresponding tables in the target database. Incremental Synchronization includes DML Synchronization and DDL Synchronization. You can configure them according to your needs. 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 the Specify Objects or Match by Rule method.

      

   4. In the Select Migration Scope section, select the objects to be migrated.

      • When you select Specify Objects, data migration supports Table-level and Database-level. Database table migration supports selecting one or more tables or views in a database as migration objects. Full database migration supports selecting an entire database as a migration object. If you select database table migration for a database, that database does not support full database migration. If you select full database migration for a database, that database does not support 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 single 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 Objects	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 migration objects 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 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 select 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 full migration. The maximum value is 512. A high concurrency may cause excessive load on the source and affect business continuity.
     Write concurrency configuration	This parameter specifies the number of concurrent data writes to the target during full migration. The maximum value is 512. A high concurrency may cause excessive load on the target and affect business continuity.
     Full migration rate limit	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). Note The RPS and BPS settings here are only for throttling. The actual achievable performance during full migration is subject to factors such as the source, target, and instance specifications.
     Processing strategy when target table objects have records	The processing strategies include Stop Migration and Ignore: If you select Stop Migration, an error will be reported during full migration if the target table objects contain data. In this case, you must handle the data in the target before you can continue the migration. Notice 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 objects contain 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 deferred indexing	The deferred indexing feature can shorten the time required for full migration. You can set whether to allow indexes to be created after full data migration is completed. For considerations on selecting deferred indexing, see the description below the table. Notice 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 deferred creation.

     If deferred 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.

     // File memory buffer size 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 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.
     Incremental migration rate limit	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). Note The RPS and BPS settings here are only for throttling. The actual achievable performance during incremental synchronization is subject to factors such as the source, target, and instance specifications.
     Incremental synchronization start timestamp	If you selected Full Migration as the migration type, this parameter is not displayed. If you did not select Full Migration but selected Incremental Synchronization as the migration type, 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.
     Scheduled Binlog advancement	If you enable this feature, you need to configure the frequency. The supported frequency range is 1 to 60 seconds. After configuration, data migration will periodically execute the CREATE DATABASE IF NOT EXISTS test command in the MySQL source database at the configured frequency during incremental synchronization to advance the Binlog timestamp.
     Adapt to Online DDL Tools	After enabling this feature, if the database uses an Online DDL tool to perform lock-free schema changes, data migration will filter temporary table objects to improve the stability of the data migration task. For details, see Introduction to Online DDL tools.

   • Advanced options

     These parameters are displayed only if the target OceanBase Database is in MySQL-compatible mode V4.3.0 or later, and you have selected Schema Migration or Incremental Synchronization > DDL Synchronization in the Select Type & Objects step.

     

     The target table object storage types include Default, Row Storage, Columnar Storage, and Hybrid Row-Column Storage. This configuration determines the storage type for target table objects during schema migration or incremental synchronization.

     Note

     The Default option adapts other options based on the target parameters. It writes the corresponding schema for table objects migrated during schema migration or newly added table objects during incremental DDL according to the set storage type.

7. Click Next to have the system pre-check the data migration task.

   In the Pre-check step, data migration checks whether the database user's read/write permissions and network connectivity meet the requirements. The data migration task can only be started after all checks pass. If the pre-check reports an error:

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

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

8. After the pre-check passes, 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 data migration task details page, where you can perform manual purchase 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 will be executed sequentially according to the selected migration type. For details, see the content in the "View migration details" section of View details of a data migration task.