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

Prerequisites

• DTS has been granted the required cloud resource access permissions. For more information, see Grant permissions for data migration.

• A database user with the required permissions has been created for the data migration task in both the source OceanBase database (MySQL-compatible mode) and the destination MySQL database. For more information, see the topics in the Configure user permissions for data sources module.

Limits

• Operations on the source database

  Do not perform DDL operations that change the database or table schema during the schema migration and full migration phases. This can interrupt the data migration task.

• DTS supports MySQL versions 5.5, 5.6, 5.7, and 8.0.

• DTS does not support primary keys (PKs) or unique keys (UKs) that contain both FLOAT and BINARY data types.

• DTS does not support indexed fields larger than 767 bytes (191 characters) in MySQL 5.6.

• DTS does not support triggers on the destination database. If triggers exist, the data migration task may fail.

• DTS supports migrating only objects whose database, table, and column names contain ASCII characters. Special characters, such as line breaks, spaces, and .|"'`()=;/&\, are not supported.

• DTS does not support using a secondary OceanBase database as the source.

Precautions

• If the source character set is UTF-8, use a compatible character set for the destination, such as UTF-8 or UTF-16. This prevents issues such as garbled characters due to incompatible character sets.

• If the clocks between nodes are not synchronized, or if the clock on your computer is not synchronized with the server clock, the latency for incremental synchronization 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, it may cause delays.

• If the explicit_defaults_for_timestamp variable is configured differently on the source and destination, there is a risk of data inconsistency for the TIMESTAMP data type.

• 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 less than that of the source, data truncation may occur. This can cause data inconsistency between the source and destination.

• OceanBase databases in MySQL-compatible mode support a TIMESTAMP range from 0000-00-00 00:00:00.000000 to 9999-12-31 23:59:59.999999. MySQL databases support a TIMESTAMP range from 1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999. When a TIMESTAMP value from the source is outside the supported range of the destination, DTS inserts the value as is. However, DTS logs the data in the error.log file or an exception table.

• In a data migration task with an OceanBase database as the source and DDL synchronization enabled, if a database or table is renamed (RENAME) on the source, you must restart the task. This prevents data loss during incremental synchronization.

• For database and table aggregation scenarios:

  • You must use matching rules to map the relationship between the source and destination objects.

  • You must create the table schemas on the destination database yourself. If you use DTS to create the table schemas, you must skip any failed objects during the schema migration step.

• If the table schemas of the source and destination are not identical, data inconsistency may occur. The following are known scenarios where this can occur:

  • If you manually create table schemas, implicit conversion issues may arise if the schemas are outside the supported scope of DTS. This can lead to inconsistent column types between the source and destination databases.

  • If the data length of a column in the destination database is smaller than that in the source database, data truncation may occur, which can cause data inconsistency.

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

  If you select both Full Migration and Incremental Synchronization, the local incremental logs of the source database must be retained for at least 7 days. Otherwise, DTS may fail to retrieve the incremental logs, which can cause the task to fail or lead to data inconsistency.

• If the source or destination database is case-insensitive and contains table objects whose names differ only in case, the migration may not produce the expected results.

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 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 using a combination of Chinese characters, digits, and letters. The name cannot contain spaces and must be no more than 64 characters long.
   Source	If you have already created an OceanBase database (MySQL-compatible mode) data source, select it from the drop-down list. If not, click Create Data Source in the drop-down list to create one in the dialog box that appears. For more information about the parameters, see Create an OceanBase data source.
   Destination	If you have already created a MySQL data source, select it from the drop-down list. If not, click Create Data Source in the drop-down list to create one in the dialog box that appears. For more information about the parameters, see Create a MySQL data source.
   Tags (Optional)	Click the text box and select the destination tag from the drop-down list. You can also click Manage Tags to create, modify, or 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 a one-way synchronization topology, the supported migration types are Schema Migration, Full Migration, Incremental Synchronization, Full Data Verification, and Reverse Incremental Synchronization.

   

   Migration Type	Description
   Schema Migration	After the schema migration task starts, DTS migrates the definitions of data objects (such as tables, indexes, constraints, comments, and views) from the source database to the destination database. Temporary tables are automatically filtered out.
   Full Migration	After the full migration task starts, DTS migrates the existing data from the source database tables to the corresponding tables in the destination database. If you select Full Migration, collect statistics for the OceanBase database (MySQL-compatible mode) before migrating data.
   Incremental Synchronization	After the incremental synchronization task starts, DTS synchronizes data changes (additions, modifications, or deletions) from the source database to the corresponding tables in the destination database. Incremental Synchronization includes DML Synchronization and DDL Synchronization, which you can configure as needed. For more information, see Customize DDL and DML synchronization. The limits for Incremental Synchronization are as follows: If you select DDL Synchronization, the data migration task may be interrupted if an unsupported DDL operation occurs on the source database. If the DDL operation is to add a new column, set the column's attribute to NULL. Otherwise, the data migration task may be interrupted.
   Full Data Verification	After full migration is complete and incremental data is almost fully synchronized to the destination, DTS automatically starts a full data verification task. This task verifies the data in the configured source database tables against the destination tables. If you select Full Data Verification, collect statistics for the OceanBase database (MySQL-compatible mode) before the verification starts. Also, use the ANALYZE statement to collect statistics for the MySQL database. If you select Incremental Synchronization but do not select all DML options under DML Synchronization, DTS does not support full data verification for this scenario.
   Reverse Incremental Synchronization	After the reverse incremental synchronization task starts, it can synchronize data changes from the destination database back to the source database in real time after a service switchover. Reverse incremental synchronization typically 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

     These parameters are displayed only if you select One-way Synchronization > Full Migration on the Select Migration Types page.

     

     Parameter	Description
     Read Concurrency	This parameter specifies the number of concurrent threads for reading data from the source during full migration. The maximum is 512. A high concurrency can put excessive pressure on the source and affect your business.
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the destination during full migration. The maximum is 512. A high concurrency can put excessive pressure on the destination and affect your business.
     Full Migration Rate Limit	You can enable the full migration rate limit as needed. If enabled, set the RPS (maximum number of rows migrated to the destination per second) and BPS (maximum volume of data migrated to the destination per second). Note The RPS and BPS settings here are for rate limiting only. The actual performance of full migration depends on factors such as the source, destination, and instance specifications.
     Policy for Existing Records in Destination Table	The policies include Ignore and Stop Migration: Select Ignore: If the destination table contains data and a conflict occurs between the original data and the data being written, DTS logs the conflicting data and keeps the original data unchanged. Important If you select Ignore, full data verification will pull data in IN mode. This mode cannot verify scenarios where the destination has data that the source does not, and verification performance will be degraded to some extent. Select the default value Stop Migration: If the destination table contains data, full migration will report an error and stop. Handle the data in the destination table before continuing the migration. Important If you click Resume after an error, DTS will ignore this setting and continue migrating the table data. Proceed with caution.
     Allow Postponed Index Creation	You can choose whether to create indexes after the full data migration is complete. Postponing index creation can reduce the time required for full migration. For precautions when choosing this option, see the notes below this table. Important This parameter is displayed only if you select both Schema Migration and Full Migration on the Select Migration Types page. Only non-unique indexes support postponed creation.

     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

     These parameters are displayed only if the source OceanBase database is version 4.3.0 or later and you select Reverse Incremental Synchronization > DDL Synchronization on the Select Migration Types page.

     

     The storage class for source table objects includes Default, Row Store, Column Store, and Hybrid Row-Column Store. This setting determines the storage class of source table objects that are created during reverse incremental synchronization. For more information, see default_table_store_format.

     Note

     The Default option automatically adapts to other options based on the destination parameter settings. During reverse incremental synchronization, new table objects from incremental DDL operations are created with the corresponding structure based on the specified 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.