Configure matching rules for migration objects

This topic describes the background information, limitations, procedure, scenarios, and troubleshooting tips for configuring matching rules for migration objects.

Background

When you create a data migration project, you must specify the migration objects. To do so, OceanBase Migration Service (OMS) allows you to specify the names of migration objects, import migration objects, and specify matching rules for migration objects. If you set the migration object selection mode to Match Rules for a migration project, you can specify the migration objects by configuring wildcard rules and specify the object-mapping logic between the source and destination data sources. This feature makes it easier to migrate a great number of objects based on simple and efficient string-matching rules. If a new table at the source meets a matching rule, the DDL statement that created the table can be automatically synchronized to the destination. For more information about DDL operations, see topics in the Supported DDL operations for synchronization and limitations chapter.

The following table describes the matching rules supported by OMS.

The requirements for matching rules are as follows:

• You cannot use wildcards for both database and table names at the destination, for example, kd_test*.person*=kd_test*.person*.

• If you use wildcards for both the source and destination databases, the database expression must be the same for the source and destination, indicating smooth database migration.

• If you use wildcards for both the source and destination tables, the table expression must be the same for the source and destination, indicating smooth table migration.

• If you use a wildcard for databases at the destination, you must also use a wildcard for databases at the source.

• If you use a wildcard for tables at the destination, you must also use a wildcard for tables at the source.

Limitations

• OMS allows you to specify multiple rules. Each rule occupies a single row, and spaces are not allowed before or after a rule.

• Object migration rules are required and object exclusion rules are optional.

• OMS does not support DDL modifications during schema migration or full migration.

• When you set the migration object selection mode to Match Rules, OMS does not support table names that contain special characters. Special characters are spaces and \n . | " ' ` ( ) = ; / & * ? [] [!]

• OMS does not allow you to configure multiple matching rules to map different tables in the same database at the source to different databases at the destination, for example, a.a* = b.a* & a.b* = c.b*.

• In a scenario of database or table aggregation, reverse incremental migration is not supported.

  Note

  OMS checks whether database or table aggregation exists only when a data migration or synchronization project is saved or started. OMS does not block reverse incremental migration if database or table aggregation occurs during the running of a project. In this case, data quality may be compromised because reverse incremental migration may fail to identify database or table mappings between the source and destination.

• At present, OMS does not support the DDL statement CREATE DATABASE. If the name of a new database created at the source meets a matching rule, you must manually create a corresponding database at the destination to continue with the data synchronization of the new database.

Considerations

• After you configure object migration rules and object exclusion rules, if the name of a source table exists in the difference set between the object migration rules and object exclusion rules, the related object can be selected.

  Note

  A difference set between two sets contains all elements that exist in one set but do not exist in the other set.

• After DDL synchronization is enabled, when you use a DDL statement to create a table or modify the schema of a table at the source, if the table name or schema name is within the difference set of the object migration rules and object exclusion rules, this DDL statement can be synchronized to the destination by OMS in real time.

• If you want to aggregate multiple tables:

  • We recommend that you configure the mappings between the source and destination databases by specifying matching rules.

  • We recommend that you manually create schemas at the destination. If you use OMS to create schemas, skip failed objects in the schema migration step.

  • If you select DDL Synchronization, databases or tables may be dropped by mistake. For example, when multiple databases or tables at the source are aggregated to a single database or table at the destination, if a database or table is dropped at the source, the aggregated database or table may be dropped at the destination.

  • When you create a data migration project, select Ignore for Processing Strategy When Destination Table Has Records.

    Note

    If you select Ignore, data is pulled in IN mode for full verification. In this case, the scenario where the destination contains more data than the source cannot be verified, and the verification efficiency will be decreased.

• If a renaming mapping rule is configured for tables, the renaming mapping rule takes precedence. For example, if the rules a.b[0-3] and a.b[3-5]=a.c are configured, the a.b3 table is renamed as a.c.

• When you execute the DDL statement RENAME TABLE, if the new table name falls out of the original migration rules or exclusion rules, unexpected synchronization errors may occur. Proceed with caution.

Procedure

1. Create a data migration project and configure it to the Select Migration Objects step.

   For more information, see the topic about creating a data migration project between the corresponding data sources in the Data migration chapter.

2. On the Select Migration Objects page, select the migration objects and migration scope.

   You can select Specify Objects or Match Rules to specify the migration objects. This section describes how to configure a matching rule.

   1. Select Match Rules.

   2. In the Specify Migration Scope section, specify object migration rules in the Object Migration Rule field and object exclusion rules in the Object Exclusion Rule field. The Object Exclusion Rule field is optional. For more information, see Wildcard patterns supported for matching rules.

   3. Click Verify.

      To view the matching results, click Preview Objects after the verification succeeds. The object migration rules and object exclusion rules apply both to tables and views. The matching results are displayed on the Final Objects, New Objects, and Removed Objects tabs.

      Tab	Description
      Final Objects	Displays the migration objects that are hit by the specified matching rules.
      New Objects	Displays the migration objects that are not in the result of the previous matching.
      Removed Objects	Displays the migration objects that are only in the result of the previous matching.

3. Complete subsequent project settings as prompted.

Sample Scenarios

Smooth migration of objects

Migrate all tables whose name starts with test in all databases whose name starts with jenkins_api at the source to the destination, and retain the original database and table names. To do so, configure the matching rule as shown in the following figure.

Renaming of objects after migration

Migrate all tables whose name starts with test in the database named jenkins_my2dh_one at the source to the destination, rename the jenkins_my2dh_one database as jenkins_my2dh_one_new, and retain the original table names. To do so, configure the matching rule as shown in the following figure.

Aggregation of objects

Aggregate all tables whose name starts with order in all databases whose name starts with jenkins_api at the source to the order table in the jenkins_api_all database at the destination. To do so, configure the matching rule as shown in the following figure.

Configuration of object exclusion rules

Exclude historical tables whose name starts with history_ and log tables whose name ends with log in the jenkins_api_mysql56 database at the source from migration. To do so, configure the matching rule as shown in the following figure.

FAQ

• Insufficient privilege

  Pay attention to the privilege settings of the source database user. If you do not grant all required privileges to the migration user, some objects are not displayed in the frontend by OMS, and you cannot properly configure matching rules. In this case, you need to add these objects to Object Exclusion Rule to prevent the data migration project from being interrupted because OMS cannot find the destination objects.

• DML filtering unsupported

  If DDL synchronization is not enabled, OMS allows you to set the migration object selection mode to Match Rules. If a new table meets a matching rule during incremental synchronization, OMS ignores the related DDL statements but synchronizes the DML statements. As a result, data cannot be written to the destination, and the data migration project fails. Therefore, you must create a table in the destination or add the table to the blocklist of OMS.