Create a project to migrate data from a MySQL database to OceanBase database Community Edition

This topic describes how to use OceanBase Migration Service (OMS) Community Edition to migrate data from a MySQL database to OceanBase database Community Edition.

Background

You can create a data migration project in the OMS Community Edition console to migrate the existing business data and incremental data from a MySQL database to a MySQL tenant of OceanBase Database through schema migration, full migration, and incremental synchronization.

The MySQL database supports the following modes: primary database only, standby database only, and primary/standby databases. The following table describes the data migration operations supported by each mode.

Prerequisites

• You have created a corresponding schema in the destination MySQL tenant of OceanBase Database. OMS Community Edition allows you to migrate tables and columns. Therefore, you must create a corresponding schema in the destination database before migration.

• You have enabled binlogs for the self-managed MySQL database. For more information, see Enable binlogs for a MySQL database.

• You have created dedicated database users in the source MySQL database and the destination MySQL tenant of OceanBase Database for data migration and granted the corresponding privileges to the users. For more information, see Create and authorize a database user.

  Database	Schema migration	Full migration	Incremental synchronization
  Self-managed MySQL database	SELECT privilege For MySQL 8.0, the SHOW VIEW privilege is also required.	SELECT privilege	REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW, and SELECT privileges
  MySQL tenant of OceanBase Database	CREATE, CREATE VIEW, SELECT, INSERT, UPDATE, and DELETE privileges	Read/Write privileges	Read/Write privileges

Limits

• OMS Community Edition supports the following MySQL database versions: 5.5, 5.6, 5.7, and 8.0. OMS Community Edition supports only the MySQL InnoDB storage engine, and is unavailable for other engines.

• OMS Community Edition does not support the migration of a table without a primary key from a MySQL database to a MySQL tenant of OceanBase Database.

• The precheck fails if the primary key is data of the FLOAT or DOUBLE type. We recommend that you do not use this type of data as the primary key.

• The host of the MySQL database must have sufficient outbound bandwidth. Insufficient outbound bandwidth on the host will slow down log parsing and data migration and may increase the latency of data synchronization.

• If the source database is a MySQL database, the character set must be UTF-8, UTF8MB4, or GBK. If the source database uses UTF-8, we recommend that you use UTF-8 or a greater character set for the destination database.

• The clock of the source database is synchronized with that of the destination database.

• If the lower_case_table_names settings of the source and destination databases are different, the project cannot be created.

• In a project of reverse incremental migration from a MySQL database to a MySQL tenant of OceanBase Database, when the OceanBase database Community Edition has a multi-partition table with global unique indexes, if you update the value of a partitioning key of the table, data may be lost during migration.

• If collations of the source and destination databases are different, a table whose primary key is data of the VARCHAR type fails the data verification.

• OMS Community Edition does not support CASCADE foreign key migration for source MySQL databases.

• If incremental files in the MySQL database need to be parsed, the server_id field must be specified for the source MySQL server.

Data type mappings

Create a data migration project

1. Create a migration project.

   1. Log on to the OMS Community Edition console.

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

   3. On the Data Migration page, click Create Migration Project in the upper-right corner.

2. On the Select Source and Destination page, specify related parameters.

   Parameter	Description
   Migration Project Name	It can contain Chinese characters, digits, and letters but must not exceed 64 characters in length.
   Tag	Click the field and select a tag from the drop-down list. You can also click Manage Tags to create, modify, or delete tags. For more information, see Use tags to manage data migration projects.
   Source	If you have created MySQL data sources, select one from the drop-down list. If you have not created a data source, click Add Data Source in the drop-down list, and add a data source in the dialog box that appears on the right. For more information, see Add a MySQL data source. You can select a MySQL data source in primary database only mode or primary/standby databases mode. This topic describes how to create a data migration project with a MySQL data source in primary/standby databases mode.
   Destination	If you have created a MySQL tenant data source in OceanBase Database, select it from the drop-down list. If you have not created a data source, click Add Data Source in the drop-down list, and add a data source in the dialog box that appears on the right. For more information, see Add an OceanBase Community Edition data source.

3. Click Next.

4. In the dialog box that appears, click OK.

   Note that this project supports only tables with a primary key or a non-null unique index and other tables are automatically filtered out.

5. On the Select Migration Type page, specify related parameters.

   Options available for Migration Type include Schema Migration, Full Migration, Incremental Synchronization, Full Verification, and Reverse Incremental Migration.

   Migration type	Limits
   Full Migration	If you select Full Migration, we recommend that you use the ANALYZE statement to collect the statistics of the MySQL database before data migration.
   Incremental Synchronization	Options available for Incremental Synchronization include DML for Data Change and DDL for Schema Change. The DML operations supported include Insert, Delete, and Update. You can select the operations based on your business needs. For more information, see Supported DDL operations for incremental migration and limits. Limits on using Incremental Synchronization: If you select Incremental Synchronization, you need to enable binlogs for the source MySQL database, and specify binlog_row_image to full and binlog_format to row. If you select Incremental Synchronization, the binlogs of the MySQL database must be retained for no less than 24 hours. Otherwise, the migration project may be interrupted because of the absence of binlogs, which cannot be recovered. If you do not select DDL for Schema Change, for DDL operations on tables in the migration link, perform these operations in the destination database first. Otherwise, data migration may be interrupted. If you select DDL for Schema Change, when you perform a DDL operation for incremental migration that is not supported by OMS Community Edition in the source database, data migration may be interrupted.
   Full Verification	If you select Full Verification, we recommend that you collect the statistics of the MySQL database and the MySQL tenant of OceanBase Database before full verification. If you select Incremental Synchronization but do not select all DML operations in the DML for Data Change section, you cannot select Full Verification.
   Reverse Incremental Migration	You cannot select Reverse Incremental Migration in the following cases: Multi-table aggregation and synchronization is enabled. Multiple schemas are configured in a rule to match one type of objects.

6. (Optional) Click Next. If you select Reverse Incremental Migration but the ConfigUrl, username, or password is not configured for the data source of the destination MySQL tenant of OceanBase Database, the More about Data Sources dialog box appears, prompting you to configure related parameters. For more information, see Add an OceanBase Community Edition data source.

   After you set the required parameters, click Test Connectivity. After the test succeeds, click Save.

7. Click Next. On the Select Objects page, select the migration objects and migration scope.

   Select Specify Objects or Match Rules. If you select DDL for Schema Change, only the Match Rules option is available.

   • Select Specify Objects. Then, select the objects to be migrated on the left, and click > to add them to the list on the right. You can select tables and views of one or more databases as the migration objects.

     Notice:

     • The name of a table to be migrated, as well as the names of columns in the table, must not contain Chinese characters.

     • Do not select a table named in the format of *_ghc.

     • If the database or table name contains a double dollar sign ($$), you cannot create the migration project.

     When you migrate data from a MySQL database to a MySQL tenant of OceanBase Database, OMS Community Edition allows you to import objects through text, rename object names, set row filters, view column information, and remove one or all objects to be migrated.

     Operation	Steps
     Import Objects	In the list on the right of the Specify Migration Scope section, click Import Objects in the upper-right corner. In the dialog box that appears, click OK. Notice: This operation will overwrite previous selections. Proceed with caution. In the Import Migration Object dialog box, import the objects to be migrated. You can import a CSV file to perform operations such as rename database tables or set row filters. For more information, see Download and import the settings of migration objects. Click Validate. After the validation succeeds, click OK.
     Rename	In the list on the right of the Specify Migration Scope section, hover the pointer over the target object. Click Rename. Enter a new name and click OK.
     Settings	OMS Community Edition allows you to set WHERE conditions to filter data by row and view column information. In the list on the right of the Specify Migration Scope section, hover the pointer over the target object. Click Settings. In the Settings dialog box, enter a WHERE clause of a standard SQL statement to configure row-based filtering. Only the data meeting the WHERE condition is synchronized to the destination data source, thereby filtering data by row. Notice: Add an escape character (`) for column names. Example: `col`. If row-based filtering with the WHERE clause is enabled, right-trim is forcibly performed on data of the CHAR or VARCHAR type, which may cause an inaccurate comparison of the VARCHAR data. Proceed with caution. Click OK. You can also view column information of the migration object in the View Column section.
     Remove/Remove All	OMS Community Edition allows you to remove a single migration object or all migration objects. Remove a single migration object In the list on the right of the Specify Migration Scope section, hover the pointer over the target object, and click Remove. The migration object is removed. Remove all migration objects In the list on the right of the Specify Migration Scope section, click Remove All in the upper-right corner. In the dialog box that appears, click OK to remove all migration objects.

   • Select Match Rules. For more information, see Configure matching rules for migration objects.

     Notice:

     Set Object Exclusion Rule to {database_name}.*_ghc.

8. Click Next. On the Migration Options page, specify the following parameters.

   Category	Parameter	Description
   Basic Settings	Concurrency for Full Migration	The value can be Smooth, Normal, or Fast. The quantity of resources to be consumed by a full data migration task varies based on the migration performance. You can also modify the configurations of the Checker-Full component to customize the concurrency. Notice: To enable this feature, select Full Migration on the Select Migration Type page.
   Basic Settings	Full Verification Concurrency	The value can be Smooth, Normal, or Fast. Different quantities of resources of the source and destination databases are consumed at different concurrencies. You can also modify the configurations of the Checker-Verify component to customize the concurrency.
   Basic Settings	Incremental Record Retention Time	The duration that incremental parsed files are cached in OMS Community Edition. A longer retention period indicates more disk space occupied by the Store component of OMS Community Edition.
   Advanced Settings	Whether to Allow Destination Table to Be Not Empty During Full Migration	If destination tables are allowed to be not empty during full migration, full verification is performed in IN mode, and you do not need to deselect Full Verification. Notice: To enable this feature, select Full Migration on the Select Migration Type page.
   Advanced Settings	Whether to Allow Post-indexing	You can specify whether to create indexes after the full migration is completed. Post-indexing can shorten the time of full migration. Notice: To enable this feature, select both Schema Migration and Full Migration on the Select Migration Type page. Only non-unique key indexes can be created after the migration is completed.

9. Click Precheck to start a precheck on the data migration project.

   During the precheck, OMS Community Edition checks the read and write privileges of the database users and the network connections of the databases. The data migration project can be started only after it passes all check items. If the precheck fails, identify the cause, fix the problem, and run the precheck again until it succeeds. You can modify the system configurations to skip a precheck item.

10. Click Start Project.

    If you do not need to start the project now, click Save to go to the details page of the data migration project. You can start the project later as needed. For more information about project details, see View details of a data migration project.

Start a data migration project

You can start a data migration project only after it passes all check items. If any check item fails, fix the problem manually and perform the check again. After the data migration task starts, run the selected migration types in sequence:

1. Schema migration

   The migration of the definitions of data objects such as tables, indexes, constraints, comments, and views from the source database to the destination OceanBase database. Temporary tables are automatically filtered out.

   If the source database is not an OceanBase database, OMS Community Edition performs data type conversion and SQL syntax encapsulation based on the syntax definition and standard of the tenant type of the destination OceanBase database. Then, OMS Community Edition replicates the data to the destination OceanBase database.

   You can view the migration progress of tables and views and perform the following operations on the target object:

   • View Creation Syntax: View table creation syntax and modify index creation syntax.

     Fully compatible DDL syntax executed on the OBServer is displayed. Incompatible syntax is converted before it is displayed.

   • Modify the creation syntax and try again: Check the error information, check and modify the definition of the conversion result of a failed DDL statement, and then migrate the data to the destination again.

   • View Database Return Code: View the DDL statements executed on the OBServer and the execution error information of a failed schema migration task.

   • Retry / Retry All Failed Objects: You can retry failed schema migration tasks one by one or retry all failed tasks at a time.

   • Skip / Batch Skip: You can skip failed schema migration tasks one by one or skip multiple failed tasks at a time. To skip multiple objects at a time, click Batch Skip in the upper-right corner. When you skip an object, its index is also skipped. When you skip a table, you need to manually specify the DDL operations to be skipped in the destination database.

   • Remove / Batch Remove: You can remove failed schema migration tasks one by one or remove multiple failed tasks at a time. To remove multiple failed tasks at a time, click Batch Remove in the upper-right corner. When you remove an object, its index is also removed.

2. Full migration

   The migration of inventory data from tables in the source database to corresponding tables in the OceanBase database. On the Full Migration page, you can view Tables, Table Indexes, and Full Load Performance. The status of the full migration changes to Completed only after migration of the table objects and table indexes is completed.

   • On the Tables tab, you can view the names, source and destination databases, estimated data volume, migrated data volume, and status of tables.

   • On the Table Indexes tab, you can view the table objects, source and destination databases, creation time, end time, time consumed, and status. You can click View Creation Syntax in the Actions column of the object to view the creation syntax of the index.

   • On the Full Load Performance tab, you can view the graphs of records per second (RPS) and migration traffic of the source and the destination to understand the data migration conditions in a timely manner.

   You can combine full migration with incremental synchronization to ensure data consistency between the source and destination databases. If any objects failed to be migrated during a full migration, the causes of the failure are displayed.

   Notice:

   If you do not select Schema Migration for Migration Type , OMS Community Edition migrates the fields in the source database that match those in the destination database during full migration, without checking whether the table structures are consistent.

   After full migration has completed and the subsequent step has started, you are not allowed to click Rerun behind Checker on the page displayed after you choose O&M and Monitoring > Component > Checker.

3. Incremental synchronization

   The synchronization of changed data in the source database to the corresponding tables in the destination OceanBase database.

   When services are continuously writing data to the source database, OMS Community Edition starts the incremental data pull module to pull incremental data from the source instance, parses and encapsulates the incremental data, and then stores the data in OMS Community Edition, before it starts the full data synchronization.

   After a full migration task is completed, OMS Community Edition starts the incremental data replay module to pull incremental data from the incremental data pull module. The incremental data is synchronized to the destination instance after it is filtered, mapped, and converted.

   In the incremental synchronization section, you can view the performance metrics of the incremental synchronization project, such as the migration latency, synchronization timestamp, and migration traffic.

   If the data migration project is in the Paused or Failed state, you can modify the Current Timestamp parameter. Note that you cannot select a timestamp after the current time when you modify the incremental synchronization timestamp. In addition, data may be duplicated or lost after you modify the incremental synchronization timestamp. Proceed with caution.

4. Full verification

   After the full data migration and incremental data migration are completed, OMS Community Edition automatically initiates a full data verification task to verify the data tables in the source database and the tables in the destination database.

   Notice:

   If you do not select Schema Migration for Migration Type , OMS Community Edition verifies the fields in the source database that match those in the destination database during full verification, without checking whether the table structures are consistent.

   During full verification, a CREATE, DROP, ALTER, or RENAME operation on tables in the source database may end the full verification.

   OMS Community Edition also provides corresponding APIs for you to initiate custom data verification tasks during incremental data synchronization.

   On the Full Verification page, you can view the overall status, start time, end time, total consumed time, estimated total number of rows, number of migrated rows, real-time traffic, and RPS of the full verification.

   The Full Verification page contains the Verified Objects and Verification Performance tabs.

   • On the Verified Objects tab, you can view the verification progress and verification object list.

     • You can view the names, source and destination databases, full data verification progress and results, and result summary of all migration objects.

     • You can filter migration objects by source or destination database.

     • You can select View Completed Objects Only to view basic information (such as the name) of objects that have completed schema migration.

     • You can choose Re-verify > Restart Full Verification to run a full verification again for all migration objects.

     • For tables with inconsistent verification results:

       If you need to reverify all data in the tables, choose Re-verify > Reverify Abnormal Table.

       If you only need to reverify inconsistent data, choose Re-verify > Verify Inconsistent Objects.

       Notice:

       The correction operation is not supported if the source database has no corresponding data.

   • On the Verification Performance tab, you can view the graphs of records per second (RPS) and migration traffic of the source and the destination to understand the data verification conditions in a timely manner.

   OMS Community Edition allows you to skip full verification for a project that is being verified or has failed verification. On the Full Verification page, click Skip Full Verification. In the dialog box that appears, click OK.

   After the full verification is completed, you can click Go To Next Stage to start the forward switchover. After you enter the switchover process, you cannot recheck the current verification task for data comparison or correction.

5. Forward switchover

   Forward switchover is the abstraction and standardization of the conventional system cutover process, a type of switchover that does not operate business application connections, and a task flow that needs to be executed before and after the data migration link of OMS Community Edition is used for application switchover. You need to make sure that the entire forward switchover process is completed before the application connections are switched over to OceanBase Database.

   Forward switchover will be included if you choose to perform data migration. You need to terminate forward incremental synchronization, delete the additional columns and unique indexes that the migration depends on, add the filtered CHECK constraint, and activate the triggers and foreign keys in the destination database to ensure the data integrity and availability of the destination database.

   If reverse incremental migration is configured, the subtasks for starting reverse incremental migration and disabling triggers and foreign keys in the source database are included in the forward switchover process to start real-time incremental synchronization from the destination database to the source database.

   1. Start forward switchover

      In this step, the migration does not stop. You only need to confirm the switchover process that is about to start. To start the forward switchover task, click Start Forward Switchover.

      Notice:

      Before you start a forward switchover task, make sure that the source data source is about to stop writing or has stopped writing.

   2. Perform switchover precheck

      Check whether the current project status supports switchover. The precheck involves the following steps:

      • Check the synchronization latency: If the synchronization latency is within 15 seconds after incremental synchronization is started, the switchover passes this check item. If incremental synchronization is not started, the switchover automatically passes this check item.

      • Check the user write privilege on the source side.

      If the switchover passes the precheck, the system automatically performs the next step. If the switchover fails the precheck, the system shows the error details.

      In this case, you can retry or skip the precheck.

      After you click Skip, you need to click Skip again in the dialog box that appears.

   3. Start the destination Store

      Start incremental data pulling in the destination database. Create and start a destination Store. If the start fails, you can choose to click Retry or Skip.

   4. Confirm that writing has stopped in the source database

      In the Confirm Writing Stopped at Source section, click OK to make sure that no incremental data is generated in the source database.

   5. Confirm the writing stop timestamp upon synchronization completion

      OMS Community Edition automatically checks whether the source and destination databases are synchronized to the same timestamp. After the check is completed, the latency and timestamp of the incremental synchronization are displayed.

   6. Stop forward synchronization

      Stop the JDBCWriter from the source database to the destination database. If the forward synchronization fails to be stopped, you can choose to click Retry or Skip.

   7. Process database objects

      In this step, the database objects are migrated, the additional columns and indexes added by OMS Community Edition are deleted, and the constraints that are automatically ignored during the schema migration are added. You also need to confirm that objects such as triggers and sequences have been manually migrated, and that the triggers and foreign keys of the source are disabled.

      You need to click Run to process the database objects. For a running project, the View Logs and Skip options are provided in the Actions column. For manually handled projects, you need to click Mark as Complete . After all projects have been marked as completed, proceed to the next step.

   8. Start reverse incremental migration

      In the Start Reverse Incremental Migration section, click Start Reverse Incremental Migration to start the JDBCWriter from the destination database to the source database. Wait until the Successfully started reverse increment message appears.

6. After reverse incremental migration is started, click the Reverse Incremental Migration tab to view detailed information such as Start Time and Reverse Synchronization Performance.

   Performance metrics of reverse incremental migration include:

   • Latency: The time consumed to synchronize changed incremental data from the destination database to the source database in the unit of seconds.

   • Migration traffic: The traffic throughput of incremental data synchronization from the destination database to the source database in the unit of KB/s.