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

Prerequisites

• Data Transmission Service must have the required access permissions for cloud resources. For more information, see Authorize Data Transmission Service to access cloud resources.

• Create a database user for data migration on both the source MySQL database and the destination OceanBase database (in MySQL-compatible mode). Then, grant the required permissions to this user. For more information, see the documents in the Configure user permissions for a data source module.

• Binary logging is enabled. For more information about RDS for MySQL databases, see Manage local logs (Binlog). For more information about PolarDB for MySQL databases, see Enable binary logging.

Limits

• Operational limitations on the source database

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

• Data Transmission Service supports MySQL V5.5, V5.6, V5.7, and V8.0.

• Data Transmission Service supports only the MySQL InnoDB storage engine. Data migration is not supported for other storage engines.

  Note

  For RDS for MySQL instances, Data Transmission Service supports the InnoDB and X-Engine storage engines.

• Data Transmission Service does not support indexed fields in MySQL databases that are larger than 767 bytes (191 characters).

• Data Transmission Service does not support migrating cascading foreign keys from a source MySQL database.

• Data Transmission Service can only migrate objects whose database, table, and column names consist of ASCII characters. These names cannot contain special characters, such as line breaks, spaces, or .|"'`()=;/&\.

• If the primary key is of the FLOAT or DOUBLE data type, the precheck fails. Do not use these data types for primary keys.

• Data Transmission Service does not support triggers on the destination database. The data migration may fail if triggers exist.

• Ensure that the clocks of the source and destination databases are synchronized.

• The VPC where the RDS for MySQL instance or self-managed database resides must be in the same region as the OceanBase database instance.

Notes

• The host of the MySQL database must have sufficient outbound bandwidth. If the outbound bandwidth is insufficient, it affects the log parsing and data migration speed, which may increase synchronization latency.

• If the source and destination databases have different collations:

  • Data inconsistency may occur. For example, two data records A and A with the utf8mb4_0900_ai_ci collation in the source database may not be written correctly to a destination database with the utf8mb4_general_ci collation. This can result in data loss.

  • Data validation for tables with a VARCHAR primary key will be inconsistent.

• If node clocks are not synchronized, or if your computer's clock is not synchronized with the server clock, the reported latency for incremental or reverse incremental synchronization may be inaccurate.

  For example, if a clock is ahead of the standard time, the latency may be a negative value. If a clock is behind the standard time, the reported latency will be higher than the actual latency.

• If incremental parsing is required for the MySQL database, the server_id parameter must be set on the source MySQL server.

• If DDL synchronization is not enabled and you change a unique index on the destination, you must restart the data migration task. Otherwise, data inconsistency may occur.

• Confirm that the migration precision for column types such as DECIMAL, FLOAT, or DOUBLE meets your expectations. If the precision of the destination field type is lower than that of the source field type, data truncation may occur and lead to data inconsistency between the source and destination.

• If the source is an RDS for MySQL read-only instance, the precheck displays a risk alert about incremental log consumption.

• In a database and table aggregation scenario:

  • Use matching rules to map the source objects to the destination objects.

  • You must manually create table schemas on the destination. If you use Data Transmission Service to create them, you must skip any failed objects during the schema migration step.

• If the table schemas of the source and destination do not match, data inconsistency may occur. The following are known scenarios:

  • When you manually create a table schema, implicit conversion issues may occur because the schema uses features not supported by Data Transmission Service. This can cause inconsistent column types between the source and destination.

  • If the data length of the destination is smaller than that of the source, data truncation may occur, leading to data inconsistency between the source and destination.

• Notes on character sets:

  • If the character set of the source is utf8mb3, it is converted to utf8mb4 when migrated to the destination.

  • If the destination is an OceanBase database in MySQL-compatible mode of V4.1.0 or later, migration of the Latin1 character set is supported.

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

  If you select Full Migration and Incremental Synchronization when you create a data migration task, the source database must retain local incremental logs for at least 7 days. Otherwise, Data Transmission Service may fail to obtain incremental logs, causing the data migration task to fail or leading to data inconsistency between the source and destination.

• If the MySQL database uses a dual-master or multi-master architecture, enable the log_slave_updates parameter. Otherwise, data loss may occur because the data source does not receive all binary logs.

• If the source or destination contains table objects whose names differ only in case, the migration result may not be as expected because the source or destination is case-insensitive.

Supported source and destination instance types

In the following table, OceanBase database in MySQL-compatible mode is abbreviated as OB_MySQL. Supported instance types include OceanBase cluster instances, OceanBase tenant instances, and Serverless instances.

Data type mapping

Procedure

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

   For more information, see Purchase a data migration task.

2. On the Data Transmission > Data Migration page, click Configure for your new data migration task.

   

   To reference an existing task configuration, you can click Reference Configuration. For more information, see Reference a data migration task configuration.

3. On the Select Source And Destination page, configure the parameters.

   Parameter	Description
   Migration Task Name	We recommend that you use a combination of Chinese characters, digits, and letters. The name cannot contain spaces and must be 64 characters or less in length.
   Source	If you have already created a MySQL data source, select it from the drop-down list. If you have not created one, click Create Data Source in the drop-down list to create one in the dialog box that appears on the right. For more information about the parameters, see Create a MySQL data source.
   Destination	If you have already created an OceanBase database data source that is compatible with MySQL, select it from the drop-down list. Otherwise, click Create Data Source in the drop-down list and create one in the dialog box on the right. For details about the parameters, see Create an OceanBase Data Source.
   Tags (Optional)	Click the text box and select the target tag from the drop-down list. You can also click Manage Tags to create, modify, and delete tags. For more information, see Manage data migration tasks using tags.

4. Click Next. On the Select Migration Type page, set Synchronization Topology to One-way Synchronization.

   Data Transmission Service supports One-way Synchronization and Two-way Synchronization. This topic describes the operations for a one-way synchronization task. For more information about two-way synchronization, see Configure a two-way synchronization task.

5. Select the migration types for the task.

   For a one-way synchronization topology, the following migration types are supported: Schema Migration, Full Migration, Incremental Synchronization, Full Data Verification, and Reverse Incremental.

   

   Migration Type	Description
   Schema Migration	After the schema migration task starts, Data Transmission Service migrates the definitions of data objects (such as tables, indexes, constraints, comments, and views) from the source database to the destination database and automatically filters out temporary tables. For schema migration from a MySQL database to an OceanBase database in MySQL-compatible mode, Data Transmission Service supports the automatic creation of databases that do not exist in the destination.
   Full Migration	After a full migration task starts, Data Transmission Service migrates the existing data in the source tables to the corresponding tables in the destination database. If you select Full Migration, we recommend that you use the ANALYZE statement to collect statistics for the MySQL database before you migrate data.
   Incremental Synchronization	After the incremental synchronization task starts, Data Transmission Service synchronizes the changed data (new, modified, or deleted) from the source database to the corresponding tables in the destination database. Incremental Synchronization includes DML Synchronization and DDL Synchronization. You can customize the configurations based on your requirements. For more information, see Customize DDL/DML. The limits of Incremental Synchronization are as follows: If you select DDL Synchronization, the data migration is at risk of interruption if the source database performs an incremental DDL operation that the data transmission process does not support. If the DDL operation is to add a column, we recommend that you set the attribute of the column to NULL. Otherwise, the data migration may be interrupted.
   Full Validation	After full migration is complete and the incremental data is synchronized to the destination and is almost caught up with the source, Data Transmission Service automatically initiates a full data validation task for the data tables configured for the source database and the destination tables. If you select Full Data Verification, we recommend that you collect statistics information for both the MySQL database and the OceanBase database in MySQL compatible mode before the full data verification starts. If you select Incremental Synchronization and do not select all DML operations in the DML Synchronization option, Data Transmission Service does not support full data verification in this scenario. Data Transmission Service supports full data validation only for tables with unique keys (tables with a primary key or a non-null unique key).
   Reverse Incremental Synchronization	After the reverse incremental synchronization task starts, it can synchronize the data changes generated in the destination database back to the source database in real time after a service switchover. Reverse incremental synchronization usually reuses the configuration of incremental synchronization. You can also customize the configuration as needed.

6. Click Next. On the Select Migration Objects page, select the objects for the data migration task.

   You can select migration objects using two methods: Specify Objects and Matching Rules. This topic describes how to select migration objects using the Specify Objects method. For more information about configuring matching rules, see Configure and modify matching rules.

   Important

   • The names of the tables to be migrated and the columns in these tables cannot contain Chinese characters.

   • If a database name or table name contains the "$$" characters, the creation of the data migration task may fail.

   • If you select DDL Synchronization in the Select Migration Type step, we recommend using matching rules to select migration objects. This ensures that all new objects that match the rules are synchronized. If you select migration objects individually, new or renamed objects will not be synchronized.

   

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

   2. In the Select Migration Scope area, in the Source Objects list, select the objects to migrate. You can select tables and views from one or more databases to migrate.

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

   Data Transmission Service lets you import objects from text files. It also lets you rename destination objects, set row filters, view column information, and remove a single object or all objects.

   Note

   When you select migration objects using the Matching Rules method, the matching rule syntax overrides the renaming capability, and the Actions section is limited to setting filter conditions. For more information, see Configure and modify matching rules.

   Operation	Description
   Import Object	In the list on the right of the selection area, click Import Object in the upper-right corner. In the dialog box that appears, click OK. Important The import operation overwrites previous selections. Proceed with caution. In the Import Migration Objects dialog box, import the objects to be migrated. You can import a CSV file to rename database tables, set row filtering conditions, and perform other operations. For more information, see Download and import migration object configurations. Click Check Validity. After you import the migration objects, check their validity first. Column field mapping is not currently supported. After the check is passed, click OK.
   Rename	Data Transmission Service lets you rename migration objects. For more information, see Rename database and table objects.
   Settings	Data Transmission Service supports row filtering using WHERE conditions. For more information, see Filter data with SQL conditions. You can also view the column information of migration objects in the View Columns area.
   Remove/Remove All	Data Transmission Service lets you remove one or more objects that have been temporarily selected for the destination during data mapping. Remove a single migration object In the list on the right of the selection area, hover over the target object and click the displayed Remove button to remove the migration object. Remove all migration objects In the list on the right of the selection area, click Remove All in the upper-right corner. In the dialog box that appears, click OK to remove all migration objects.

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

   • Full Migration

     On the Select Migration Type page, the following parameters appear only when you select One-way Synchronization > Full Migration.

     

     Parameter	Description
     Read Concurrency	This parameter specifies the number of concurrent threads for reading data from the source during full migration. The maximum value is 512. A high degree of concurrency may put excessive pressure on the source and affect your services.
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the destination during full migration. The maximum value is 512. A high degree of concurrency may put excessive pressure on the destination and affect your services.
     Full Migration Rate Limit	You can decide whether to enable the full migration rate limit as needed. If you enable it, set RPS (the maximum number of data rows that can be migrated to the destination per second during full migration) and BPS (the maximum volume of data that can be migrated to the destination per second during full migration). Note The RPS and BPS settings here only serve as a rate-limiting capability. The actual performance of full migration is affected by factors such as the source, destination, and instance specifications.
     Processing Policy for Existing Records in Destination Table Objects	The processing policy includes Ignore and Stop Migration: Select Ignore: If a destination table already contains data and a conflict occurs between the data being written and the existing data, Data Transmission Service logs the conflicting data and keeps the existing data unchanged. Important If you select Ignore, full data verification uses the IN pattern to pull data. This process cannot verify scenarios where data exists in the destination but not in the source, and verification performance is degraded to a certain degree. If you select the default value Stop Migration, the full migration fails with an error if the destination table contains data. You must process the data in the destination table before you can restart the migration. Important If an error occurs and you click Resume, Data Transmission Service will ignore this configuration option and continue to migrate table data. Proceed with caution.
     Is deferred indexing supported?	You can set whether to allow index creation after full data migration is complete. The postponed index creation feature can shorten the time required for full migration. For notes on selecting postponed index creation, see the description below the table. Important On the Select Migration Type page, this parameter is displayed only if you select both Schema Migration and Full Migration. Only non-unique indexes support postponed creation. When creating an index, if the destination OceanBase database encounters the following errors, Data Transmission Service will ignore them and assume the index was created successfully, without attempting to create it again. OceanBase database in MySQL-compatible mode reports a Duplicate key name error. OceanBase database in Oracle-compatible mode reports a name is already used by an existing object error. If the destination is an OceanBase database and you select Allow, configure the following settings: Concurrency for a single index DDL statement: A higher degree of parallelism consumes more resources and results in a faster migration speed. Maximum number of concurrent index DDL statements: The maximum number of postponed index DDL statements that the system can call at the same time.

     If you allow postponed index creation, we recommend that you adjust the following business tenant parameters using a command-line tool according to the hardware of the OceanBase database and the current service traffic.

     // File memory buffer limit
     alter system set _temporary_file_io_area_size = '10' tenant = 'xxx'; 
     // V4.x Disable throttling
     alter system set sys_bkgd_net_percentage = 100;  

   • Incremental Synchronization

     The following parameters appear only when you select One-way Synchronization > Incremental Synchronization on the Select Migration Type page.

     

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

   • Reverse Incremental

     On the Select Migration Type page, the parameters for this area are displayed only if you select One-way Synchronization > Reverse Incremental. The configuration parameters for Reverse Incremental default to Reuse Incremental Synchronization Configuration.

     

     You can also choose not to reuse the incremental synchronization configuration and configure it manually.

     Parameter	Description
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the source during reverse incremental synchronization. The maximum value is 512. A high degree of concurrency may put excessive pressure on the source and affect your services.
     Reverse Incremental Rate Limit	You can decide whether to enable the reverse incremental rate limit as needed. If you enable it, set RPS (the maximum number of data rows that can be synchronized to the source per second during reverse incremental synchronization) and BPS (the maximum volume of data that can be synchronized to the source per second during reverse incremental synchronization). Note The RPS and BPS settings here only serve as a rate-limiting capability. The actual performance of reverse incremental synchronization is affected by factors such as the source, destination, and instance specifications.
     Incremental Synchronization Start Offset	If you select Full Migration, this parameter is not displayed. If you do not select Full Migration but select Incremental Synchronization, the setting defaults to the forward switch (if any) and cannot be modified.

   • Advanced Options

     The parameters in this area are displayed only if the destination OceanBase database is in MySQL compatibility mode V4.3.0 or later and you select Schema Migration or Incremental Synchronization > DDL Synchronization on the Select Migration Type page.

     

     The available storage classes for destination table objects are Default, Row Store, Column Store, and Mixed Row and Column Store. This configuration determines the storage class for destination table objects created during schema migration or incremental synchronization. For more information, see default_table_store_format.

     Note

     The Default option automatically adjusts other settings based on the destination's parameters to ensure that table objects from a schema migration and new tables created by incremental DDL are written to the structure defined by the configured storage class.

8. Click Precheck to precheck the data migration task.

   In the Precheck step, Data Transmission Service checks whether items meet the requirements, such as the read and write permissions of the database user and the network connectivity of the database. You can start the data migration task only after all check items pass. If the precheck fails:

   • You can identify and troubleshoot the problem, and then run the precheck again until it succeeds.

   • You can also click Skip in the Actions column for a failed precheck item. A dialog box appears that describes the impact of skipping this operation. To proceed, click OK.

9. After the precheck passes, click Start Task.

   If you do not want to start the task immediately, click Save. You can then start the task manually from the Data Migration Task List page or using batch operations. For more information about batch operations, see Batch Operations on Data Migration Tasks.

   Data Transmission Service lets you modify migration objects and their row filtering conditions while a data migration task is running. For more information, see View and modify migration objects and their filter conditions. After the data migration task starts, it performs the migration steps sequentially based on the selected migration types. For more information, see View migration details.

References

• Create a MySQL data source

• Create an OceanBase data source

• Configure and modify matching rules

• View the details of a data migration task