Best practices for migrating data from AWS RDS to OceanBase Cloud

This topic describes best practices for migrating data from an AWS RDS database to OceanBase Cloud. It covers pre-migration preparation and assessment, migration, and data validation.

Background information

OceanBase Migration Service (OMS) supports data exchange between homogeneous or heterogeneous data sources and OceanBase Database. It can migrate existing data online and synchronize incremental data in real time. OMS supports schema migration, full migration, and incremental synchronization to migrate existing business data and incremental data from an AWS RDS database to OceanBase Cloud. This topic uses AWS RDS for MySQL as an example to describe how to migrate data by using OMS.

The migration process is as follows:

1. Configure the network.

2. Prepare the databases at both ends.

   1. Create a data source for the OceanBase Cloud database.

   2. Add an AWS RDS data source.

3. Perform a compatibility assessment.

4. Migrate data by using schema migration, full migration, and incremental synchronization.

5. Verify the data.

Prerequisites

• You have the permissions of a project owner, project administrator, or data service administrator and can create migration-related tasks. For information about authorization, see Overview of organization and project management.

• You have created an OceanBase Database cluster and tenant at the target. For details, see Create an instance and Create a tenant.

• You have created dedicated database users for data migration at both the source and target and granted them the relevant permissions. For details, see Data migration user permissions and Create an Aurora MySQL database instance.

Network configuration

Before starting real-time migration, establish network connectivity between AWS and OceanBase Cloud to allow data access. This topic uses a private connection to an RDS MySQL instance as an example. For information about other AWS database types, see Configure an AWS endpoint.

Prerequisites for configuring private network connections

You have created a database instance of the required type and obtained its connection address in your VPC.

• You have created an Amazon MSK cluster and associated a key. For details, see Set up SASL/SCRAM authentication for an Amazon MSK cluster.

• If security groups are enabled for resources such as the load balancer or virtual server associated with the endpoint service, add the private IP address range of the OceanBase Cloud data source to the corresponding security group. For details, see Private IP address ranges.

Obtain the IP address of an RDS MySQL instance

1. Log in to the Databases page of the Amazon RDS console.

2. In the instance list, click the name of the target RDS MySQL database instance, copy Endpoint in the Connections and Security section, and confirm the VPC information.

3. Log in to an EC2 instance in the same VPC and run the following command:

   // Replace the domain name example.example-region.rds.amazonaws.com with the endpoint name you copied in the previous step.
   nslookup <example.example-region.rds.amazonaws.com>   
   

   The following output is returned:

   // ...
   Name: example.example-region.rds.amazonaws.com
   Address: xxx.xxx.xxx.1
   //... 
   

4. The returned IP address is the current primary private IPv4 address of the RDS instance. This address will be used in the Create Target Group step.

Create a target group

1. Log in to the Target Groups page of the AWS console.

2. Click Create Target Group in the upper-right corner of the page.

3. On the Create Target Group page, configure the parameters and click Next.

   Parameter	Description
   Target Type	Select IP Address.
   Target Group Name	Customize the target group name according to the naming conventions.
   Protocol	Select TCP.
   Port	Enter the corresponding port number. For a Kafka instance, enter the corresponding NGINX port.
   IP Address Type	Select IPv4.
   VPC	Select the VPC that hosts the load balancer.

4. On the Register Target page, configure the parameters in the IP Address section and click Include as pending below.

   Parameter	Description
   Network	From the Network drop-down list, select the VPC where the MySQL database instance is located.
   Specify IP and Define Port	In the Enter the IPv4 address of the VPC subnet text box, enter the IP address of the database instance.
   Port	Enter the corresponding port number. For a Kafka instance, enter the corresponding NGINX port.

5. In the View Targets section, confirm that the IP addresses to be included in the group are correctly configured, and then click Next.

6. On the Review and Create page, check the configuration information. If everything is correct, click Create Target Group.

Create a network load balancer

For instructions on creating a Network Load Balancer, see Create a network load balancer.

1. Log in to the Load Balancers page of the AWS console.

2. In the upper-right corner of the page, click Create Load Balancer > Network Load Balancer.

3. On the Create Network Load Balancer page, configure the parameters.

   • Basic settings

     Parameter	Description
     Mode	Select Internal.
     IP Address Type	Select IPv4.

   • Network mapping

     Parameter	Description
     VPC	Select the VPC where the MySQL data source is located from the drop-down list.
     Availability Zone and Subnet	Typically, select all availability zones and subnets. Alternatively, select at least one availability zone and one subnet based on your requirements.

   • Security group

     Parameter	Description
     Security Group	You can associate a security group with the network load balancer. We recommend that you do not select a security group here. For details, see Security groups for network load balancers.

     Note

     • If you associate a security group with the network load balancer, allow access to the backend target port.
     • When creating a network load balancer for a Kafka instance, do not select a security group.

   • The parameters in the Listeners and Routing section are configured as follows.

     Parameter	Description
     Protocol	The listener protocol. Select TCP.
     Port	Select the corresponding port number.
     Default Action	Select the target group created in the Create Target Group step from the drop-down list.

4. After the network load balancer is created, return to the Load Balancers page and select it.

5. On the Load Balancers page, click Actions in the upper-right corner and choose Edit Load Balancer Attributes.

6. On the Edit Load Balancer Attributes page, in the Load Balancer Target Selection Strategy section, select Enable Cross-Zone Load Balancing and click Save Changes.

Create an endpoint service

1. Log in to the Endpoint Services page of the AWS console.

2. In the upper-right corner of the page, click Create Endpoint Service.

3. On the Create Endpoint Service page, configure the parameters.

   Parameter	Description
   Name	Customize the name of the endpoint service.
   Load Balancer Type	Select Network.
   Available Load Balancer	Select the load balancer created in the Create Network Load Balancer step. Note If it is not displayed, wait until its status changes to Active, and then refresh the page.

4. In the Other Settings section, configure the parameters.

   Parameter	Description
   Accept requests to use the endpoint (Optional)	Specifies whether connection requests from service consumers require manual approval. If you select Accept, when the data migration service first accesses the private connection, you are prompted to go to the AWS console and accept the endpoint connection request. If you do not select Accept, no prompt is displayed when the data migration service first accesses the private connection.
   Enable Private DNS Name	Do not enable your own domain name here.
   Supported IP Address Type	Select IPv4.

5. Click Create to complete the creation of the endpoint service.

6. Authorize accounts that need to access the endpoint service.

   1. On the Endpoint Service page, select the created endpoint service.

   2. In the upper-right corner of the page, click Actions > Allow Principal.

   3. Obtain the account that requires authorization for the data source or data migration task.

      On the Create Data Source or Create Data Migration Task page in the OceanBase Cloud console, set Connection Type to Endpoint. Then, copy the account that the page indicates must be authorized. The account is in the format arn:aws:iam::aws-account-id:<type>/<id>. For details about creating a data source, see Create a data source. For details about creating a task, see the documentation for the applicable migration type in Data migration.

   4. In the Add Principal section of the Allow Principal page, enter the account to authorize and click Allow Principal.

      After authorization is completed, the created endpoint service becomes visible to the authorized account.

7. After creating the endpoint service, copy its Service Name and enter it in the AWS > Endpoint > Connection Information field on the Create Data Source or Create Data Migration Task page.

   To copy the service name, follow these steps:

   1. Log in to the Endpoint Services page of the AWS console.

   2. Select the target endpoint service.

   3. In the Details section at the bottom of the page, click the copy icon next to Service Name and save the service name for use when you add a data source.

Prepare the source and target databases

Add an OceanBase Cloud data source

1. Log in to the OceanBase Cloud console.

2. In the left-side navigation pane, choose Data Services > Data Source.

3. On the Data Sources page, click Create Data Source > OceanBase MySQL Compatible.

4. On the Create Data Source page, select the OceanBase Cloud instance that you created, enter the username and password, and click Test Connection.

5. After the connection test succeeds, click Save, and record the data source name for use as the target in subsequent operations.

Add an AWS RDS data source

1. Log in to the OceanBase Cloud console.

2. In the left-side navigation pane, choose Data Services > Data Source.

3. On the Data Sources page, click Create Data Source > MySQL.

4. On the Create Data Source page, enter the region, instance type, connection type, endpoint service name, port, and account password based on your existing AWS RDS database information, then click Test Connection.

5. After the connection test succeeds, click Save, and record the data source name for use as the source in subsequent operations.

Compatibility assessment

1. Create an online assessment task.

   1. Log in to the OceanBase Cloud console.

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

   3. On the Data Migration page, click Assess Compatibility.

   4. On the Compatibility Assessment tab, click Create > Online Assessment.

2. On the assessment page, configure the following parameters.

   1. Customize the Task Name, which must not exceed 30 characters in length.

   2. In the source area, click Reference Data Source and select an added AWS RDS data source from the drop-down list. The source database information is automatically populated.

   3. In the target area, click Reference Data Source and select a created OceanBase Cloud database from the drop-down list. The target database information is automatically populated.

   4. In the assessment type area, select the type to be assessed. Currently supported types are Object Assessment and Profile Assessment.

   5. Click Next.

3. On the Advanced Configurations page, set the assessment scope.

   Operation	Description
   Choose Objects for Assessment	In the left Source List, select the target schema, click >, and add it to the right list.
   Select Object Type	In the Select Object Type area, select the target object type.

4. Click Start Assessment to initiate the online assessment task.

5. After the assessment is complete, you can view the results on the Assess Compatibility tab.

Migrate data from AWS RDS for MySQL to OceanBase Cloud

After the compatibility assessment is complete, you can start the data migration.

Limitations

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

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

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

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

Considerations

• Clock skew between nodes or between the client and server can cause inaccurate latency reporting during incremental synchronization. For example, if a clock is behind the standard time, the reported latency may be negative. If a clock is ahead of the standard time, the reported latency may increase.

• If DDL synchronization is disabled, you must restart the data migration task after modifying a unique index on the target side. Otherwise, data inconsistency may occur.

• Verify that the migration precision 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.

• If the collation of the source and target databases is different:

  • Data synchronization may become inconsistent. For example, the two values 'A' and 'A ' in a source database that uses the utf8mb4_0900_ai_ci collation may not both be written to a target database that uses the utf8mb4_general_ci collation, resulting in data loss.

  • Data validation for tables with VARCHAR as the primary key may become inconsistent.

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

Data type mappings

Procedure

Configure data migration

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

2. On the Data Migration tab, click Create Task.

3. In the Edit Task Name text box, enter a custom migration task name. We recommend that you use a combination of Chinese characters, numbers, and letters. The name cannot contain spaces and cannot exceed 64 characters.

4. On the Configure Source and Target page, configure the following information.

   1. In the source area, click Quick Fill next to Source. Select a target AWS RDS data source from the drop-down list to automatically populate the source database information.

   2. In the target area, click Quick Fill next to Target. Select a target OceanBase Cloud data source from the drop-down list to automatically populate the target database information.

5. Click Test and Continue.

   Note

   • If the connection test fails when the endpoint is accessed for the first time, follow the instructions in the dialog box.
   • If you selected Accept for Accept requests to use the endpoint (Optional) when creating the endpoint service, you are prompted to go to the AWS console and accept the endpoint connection request when data migration accesses the private connection for the first time.

6. On the Select Type and Objects page, configure the following parameters.

   1. In the Synchronization Topology area, select One-way Sync.

   2. In the Migration Type area, select the migration type for the current data migration task. Options include Schema Migration, Full Migration, and Incremental Synchronization.

      Parameter	Description
      Schema Migration	Schema migration requires defining character set mappings yourself. 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 the 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 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 (inserts, modifications, or deletions) from the source database to the corresponding tables in the target database. Incremental Synchronization includes DML Synchronization and DDL Synchronization. You can customize the configuration 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 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 Database Object and Entire Database. Database Object lets you select tables and views from one or more databases as migration objects. Entire Database lets you select an entire database as a migration object. You cannot use both methods for the same database.

        After selecting Database Object or Entire Database, select the objects you want to migrate from the left and click > to add them to the list on the right.

        Data migration supports importing objects via text, and allows you to rename target objects, set row filters, view column information, and remove individual or all migration objects.

        Note

        When you select Entire Database:

        • The list on the right displays only database names, not individual objects.
        • If you selected Incremental Synchronization > DDL Synchronization, new tables created at the source can be synchronized to the target.

        Actions	Description
        Import Object	In the right-side list of the selected area, click Import Object in the upper-right corner. For more information, see Import migration objects.
        Rename	Data migration supports renaming migration objects. For more information, see Rename a database or table.
        Row Filtering	Data migration supports filtering rows based on WHERE conditions. For more information, see Filter data by SQL condition. You can also view the column information of migration objects in the View Column section.
        Remove/Clear All	During object mapping, you can remove one or all objects that have been temporarily selected for migration to the target. Remove one migration object In the list on the right side of the selection area, click the Remove icon next to the object. 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.

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

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

   • Full migration

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

     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. Excessive concurrency may increase the load on the source and affect your workloads.
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the target during full migration. The maximum value is 512. Excessive concurrency may increase the load on the target and affect your workloads.
     Full Migration Rate Limit	You can decide whether to enable the full migration rate limit based on your actual needs. If you enable it, set the RPS (the maximum number of data rows that can be migrated to the target per second during full migration) and BPS (the maximum amount of data that can be migrated to the target per second during full migration). Note The RPS and BPS set 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.
     Processing Strategy When Target Table Has Records	The processing strategies include Stop Migration and Ignore: If you select Stop Migration, an error is reported during full migration if the target table has data. You must handle the data in the target table 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 has data, if the original data conflicts with the data to be written, data migration logs the conflicting data and retains the original data unchanged during data writing.
     Post-Indexing	The post-indexing feature can shorten the time consumed by full migration. You can specify whether to allow indexes to be created after full data migration is completed. For considerations on selecting post-indexing, see the description below the table. 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 post-creation.

     If post-indexing is allowed, we recommend that you adjust the following business tenant parameters using the command-line client tool based on the hardware conditions of 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 on the Select Type & Objects page.

     Parameter	Description
     Write Concurrency	This parameter specifies the number of concurrent threads for writing data to the target during incremental synchronization. The maximum value is 512. Excessive concurrency may increase the load on the target and affect your workloads.
     Incremental Migration Rate Limit	You can decide whether to enable the incremental synchronization rate limit based on your actual needs. If you enable it, set the RPS (the maximum number of data rows that can be synchronized to the target per second during incremental synchronization) and BPS (the maximum amount of data 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 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 more information, see Set an incremental synchronization point.
     Scheduled Binlog Advance Point	If you enable this feature, you need to configure the frequency. The supported frequency range is 1 to 60 seconds. After configuration, during the incremental synchronization phase, data migration will periodically execute the CREATE DATABASE IF NOT EXISTS test command in the MySQL source database at the configured frequency to advance the Binlog point.
     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 out temporary table objects to improve the stability of the data migration task. For more information, see Introduction to Online DDL tools. Note Currently, adapting to Online DDL tools is only supported when the source is a MySQL database and Alibaba Cloud DMS, gh-ost, or pt-osc is used for lock-free schema change operations.

   • Advanced Options

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

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

     Note

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

8. Click Pre-check. The system performs a precheck on the data migration task.

   During the precheck, data migration verifies whether the database user's read/write permissions, network connectivity, and other requirements are met. The data migration task can only be started after all checks 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 it is okay to skip, click OK in the dialog box.

9. After the precheck succeeds, click Purchase to go to the Purchase a data migration instance page.

Purchase a data migration instance

1. On the Purchase a data migration instance page, confirm the Migration Task Information, and in the Instance Configuration section, select the required Specification.

   Instance specifications provide different RPS performance for incremental synchronization and support different recommended maximum numbers of table objects. For details, see Data migration specifications. For pricing and billing information, see Billing for data migration.

2. In the Summary section on the right side of the Purchase a data migration instance page, check I have read and agree to the OceanBase Cloud Database Service Agreement and the OceanBase Cloud Data Migration Service Level Agreement.

3. Click Confirm Order. After the instance is purchased, you will be redirected to the details page of the data migration task.

4. If you choose to start the task immediately after purchase when creating the data migration task, the task will be in the Running state.

5. Wait for the data migration task to complete.

Data validation

After the data migration task is complete, use data validation to verify the migrated data.

Create a data validation task

1. Go to the page for creating a data validation task.

   1. Log in to the OceanBase Cloud console.

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

   3. On the Data Validation page, click Create Task in the upper-right corner.

2. In the text box for editing the task name, enter a custom name for the validation task.

   We recommend that you use a combination of Chinese characters, numbers, and letters. The name must not contain spaces and must be no longer than 64 characters in length.

3. In the Configure Source and Target step, click Quick Fill next to Source and select the source data source used for data migration. The source settings are automatically populated. Then, click Quick Fill next to Target and select the target data source used for data migration. The target settings are automatically populated.

4. Click Test and Continue.

5. In the Select Type & Objects step, select the validation type for the current data validation task.

   Currently supported validation types include Full Validation and Row Count Validation. For more information, see Overview.

6. In the Validation Objects section, configure how to select the validation object.

   You can select the validation object by using either Specify Objects or Match by Rule.

7. In the Select Object section, select the object to be verified.

   Note

   Currently, only tables are supported as validation objects.

   • When you select Specify Objects, select the table object to be verified on the left and click > to add it to the list on the right.

     Data validation supports importing objects via text, and you can perform operations such as renaming the target object, setting row filters, viewing column information, and removing one or all validation objects.

     Actions	Description
     Import Object	In the list on the right side of the selection area, click Import Object in the upper-right corner. For more information, see Import validation objects.
     Rename	Data validation supports renaming validation objects. For more information, see Rename a validation object.
     Row Filtering	Full Validation supports row filtering using WHERE conditions. For more information, see Filter validation objects by SQL condition. You can also view the column information of the validation object in the View Column section.
     Remove/Clear All	Data validation supports removing one or more objects temporarily selected 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.

   • If you select Match by Rule, for more information, see Configure matching rules for a validation object.

8. Click Next. In the Validation Options step, configure the parameters.

   • Full Validation

     The following parameters are displayed only if you select Full Validation in the Select Type & Objects step.

     Parameter	Description
     Source Concurrency Configuration/Target Concurrency Configuration	Minimum value: 1. Maximum value: 512. Default value: 4.
     Validation Mode	Includes Adaptive Mode and IN Mode. Adaptive Mode: Retrieves data within the same range from the source and target in batches for precise comparison. IN Mode: Retrieves data within a specified range from the source in batches, and queries the target with the unique key to check if the data is consistent. Note that this mode cannot verify scenarios where the target contains data not present in the source, and validation performance is somewhat degraded. It is recommended to use this mode only when the target contains expected excess data that does not need validation, or in database/table consolidation migration scenarios.
     Limit BPS	Unit: MiB/s. Maximum value: 1024. If you enable this option, select at least the minimum rate for either the source or target.

   • Row Count Validation

     The following parameters are displayed only if Select Type & Objects step selects Row Count Validation.

     Parameter	Description
     Concurrent Tables for Validation	Minimum value: 1, maximum value: 100, default value: 10. This configuration applies to both the source and target.
     Query Timeout	Unit: minutes, minimum value: 1, maximum value: 1440, default value: 10. This configuration applies to both the source and target.

9. Click Next. The system performs a precheck on the data validation task.

10. After the pre-check passes, click Start Task.

    If you do not need to start the task immediately, click Save to go to the data validation task details page, where you can start it manually as needed. For more information, see View details of a data validation task.

View data validation results

1. Log in to the OceanBase Cloud console.

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

3. In the data validation list, view the execution status of the data validation task. When the target task status is Completed, click the target task name.

4. On the data validation task details page, view the execution status of the data validation task.

   • Basic Information: Displays basic information related to the current data validation task.
   • Validation Details: Displays information such as the pre-check task status and result of the current task, as well as the validation status and result of the corresponding validation type.

Summary

If the data validation results show consistency between both ends, it indicates that the migration task is complete.