Data Migration

Data Migration #

Syntax	Description	Type
ALTER MIGRATION RULE	Modify data migration rule	RAL
SHOW MIGRATION RULE	View data migration rule	RAL
SHOW MIGRATION LIST	Modify data migration rule list	RAL
SHOW MIGRATION STATUS jobId	Modify data migration job status	RAL
MIGRATE TABLE x INTO y	Start migration job	RAL
START MIGRATION jobId	Start stopped migration job	RAL
STOP MIGRATION jobId	Stop migration job	RAL
SHOW MIGRATION CHECK ALGORITHMS	View data migration consistency check algorithm	RAL
CHECK MIGRATION jobId	Check job data consistency	RAL
START MIGRATION CHECK jobId	Start the stopped data consistency check job	RAL
STOP MIGRATION CHECK jobId	Stop data consistency check job	RAL
SHOW MIGRATION CHECK STATUS jobId	View data consistency check job status	RAL
COMMIT MIGRATION jobId	Commit job	RAL
ROLLBACK MIGRATION jobId	Rollback job. Note: This statement will clean up the target table, please operate with caution	RAL
REGISTER MIGRATION SOURCE STORAGE UNIT	Register data migration task storage unit	RAL

1. Modify data migration rule #

ALTER MIGRATION RULE ();

Config Description

ALTER MIGRATION RULE (
READ( -- Data reading configuration. If it is not configured, part of the parameters will take effect by default.
  WORKER_THREAD=20, -- Obtain the thread pool size of all the data from the source side. If it is not configured, the default value is used.
  BATCH_SIZE=1000, -- The maximum number of records returned by a query operation. If it is not configured, the default value is used.
  SHARDING_SIZE=10000000, -- Sharding size of all the data. If it is not configured, the default value is used.
  RATE_LIMITER ( -- Traffic limit algorithm. If it is not configured, traffic is not limited.
  TYPE( -- Algorithm type. Option: QPS
  NAME='QPS',
  PROPERTIES( -- Algorithm property
  'qps'='500'
  )))
),
WRITE( -- Data writing configuration. If it is not configured, part of the parameters will take effect by default.
  WORKER_THREAD=20, -- The size of the thread pool on which data is written into the target side. If it is not configured, the default value is used.
  BATCH_SIZE=1000, -- The maximum number of records for a batch write operation. If it is not configured, the default value is used.
  RATE_LIMITER ( -- Traffic limit algorithm. If it is not configured, traffic is not limited.
  TYPE( -- Algorithm type. Option: TPS
  NAME='TPS',
  PROPERTIES( -- Algorithm property.
  'tps'='2000'
  )))
),
STREAM_CHANNEL ( -- Data channel. It connects producers and consumers, used for reading and writing procedures. If it is not configured, the MEMORY type is used by default.
TYPE( -- Algorithm type. Option: MEMORY
NAME='MEMORY',
PROPERTIES( -- Algorithm property
'block-queue-size'='2000' -- Property: blocking queue size.
)))
);

Default configuration

+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+ 
| read | write | stream_channel | 
+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+ 
| {"workerThread":40,"batchSize":1000,"shardingSize":10000000} | {"workerThread":40,"batchSize":1000} | {"type":"MEMORY", "props":{"block‑queue‑size":10000}} |
 +‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑+

Example

ALTER MIGRATION RULE (READ(RATE_LIMITER (TYPE(NAME='QPS’,PROPERTIES(‘qps'='5000’)))))

2. View Data Migration Rule #

SHOW MIGRATION RULE

Parameter Description

None

Example

SHOW MIGRATION RULE;

Output Description

Column	Description
read	Data reading configuration. If it is not configured, part of the parameters will take effect by default.
write	Data writing configuration. If it is not configured, part of the parameters will take effect by default.
stream_channel	Data channel. It connects producers and consumers, used for reading and writing procedures. If it is not configured, the MEMORY type is used by default.

3. Modify Data Migration Rule List #

SHOW MIGRATION LIST;

Parameter Description

None

Example

+-------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id                                  | tables  | sharding_total_count | active | create_time         | stop_time |
+-------------------------------------+---------+----------------------+--------+---------------------+-----------+
|j01016e501b498ed1bdb2c373a2e85e2529a6| t_order | 1                    | true   | 2022-08-22 16:37:01 | NULL      |
+-------------------------------------+---------+----------------------+--------+---------------------+-----------+

Output Description

Column	Description
Id	Migration job id
tables	The name of the table being migrated
active	Whether the task is active true or false
create_time	job create time
stop_time	Job stop time, NULL means the job is not stopped

4. View Data Migration Job Status #

SHOW MIGRATION STATUS jobId;

Parameter Description

Name	Description
jobId	Data migration job id

Example

SHOW MIGRATION STATUS 'j01016e501b498ed1bdb2c373a2e85e2529a6';
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
| item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | incremental_idle_seconds | error_message |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+
| 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 6                       | 100                           | 81                       |               |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+--------------------------+---------------+

Output Description

Column	Description
item	Sequence number within the migration job
data_source	Data storage unit name
status	Job status, please refer to the status description for details
active	whether the task is active true or false
processed_records_count	Number of processed record
inventory_finished_percentage	Full data processing completion progress (percentage)
incremental_idle_seconds	Inactive time of the incremental synchronization job (unit: second)
error_message	Error message

Status Description

Value	Description
PREPARING	Preparing
RUNNING	Running
EXECUTE_INVENTORY_TASK	Full migration
EXECUTE_INCREMENTAL_TASK	Incremental migration
FINISHED	Completed (the entire process is complete and the new rules are in effect)
PREPARING_FAILURE	Preparation phase failed
EXECUTE_INVENTORY_TASK_FAILURE	Full migration phase failed
EXECUTE_INCREMENTAL_TASK_FAILURE	Incremental migration phase failed

5. Start Data Migration #

MIGRATE TABLE schema_name.table_name INTO schema_name.table_name;

Example

The storage node is MySQL starts data migration

MIGRATE TABLE ds_0.t_order INTO t_order;

The storage node is MySQL or other specified target logical database

MIGRATE TABLE ds_0.t_order INTO sharding_db.t_order;

Migrate multiple tables at the same time, separate multiple tables with commas

MIGRATE TABLE ds_0.t_order INTO sharding_db.t_order,ds_0.t_order1 INTO sharding_db.t_order1,ds_0.t_order2 INTO sharding_db.t_order2;

6. Start Stopped Job #

START MIGRATION jobId

Parameter Description

Name	Description
jobId	Job id

Example

Start stopped job with the specified job id

START MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';

7. Stop Migration Job #

STOP MIGRATION jobId

Parameter Description

Name	Description
jobId	Job id

Example

Start stopped job with the specified job id

STOP MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';

8. View data consistency algorithm #

For consistency check algorithm selection, please refer to Data Consistency Check Algorithm。

SHOW MIGRATION CHECK ALGORITHMS;

Example

SHOW MIGRATION CHECK ALGORITHMS;
+----------------------------+---------------------------------------------+-----------------------------------------------------------------------------------------------+----------------------------------------------+
| type                       | type_aliases                                | supported_database_types                                                                      | description                                  |
+----------------------------+---------------------------------------------+-----------------------------------------------------------------------------------------------+----------------------------------------------+
| CRC32_MATCH                |                                             | MySQL,MariaDB,H2,Aurora                                                                       | Match CRC32 of records.                      |
| DATA_MATCH                 |                                             | SQL92,MySQL,PostgreSQL,openGauss,Oracle,SQLServer,MariaDB,H2,Hive,Presto,Aurora,DM,KingbaseES | Match raw data of records.                   |
| SphereEx:BATCH_CRC32_MATCH | SphereEx:DATA_CONSISTENCY_CHECK_CRC32_MATCH | MySQL,MariaDB,H2,Aurora                                                                       | Match CRC32 of records (Commercial edition). |
| SphereEx:DATA_DIFF         |                                             | SQL92,MySQL,PostgreSQL,openGauss,Oracle,SQLServer,MariaDB,H2,Hive,Presto,Aurora,DM,KingbaseES | Diff raw data of records.                    |
+----------------------------+---------------------------------------------+-----------------------------------------------------------------------------------------------+----------------------------------------------+

Output Description

type : algorithm type
type_aliases: algorithm aliases
supported_database_types: Which databases the algorithm supports
description: Algorithm description

9. Perform data consistency check #

CHECK MIGRATION jobId BY TYPE (NAME='algorithmType');

Name	Description
jobId	Job id

Example

Specify the jobId to specify the check algorithm to perform data consistency verification

CHECK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='CRC32_MATCH');

CHECK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='DATA_MATCH', PROPERTIES('chunk-size'='1000'));

CHECK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='SphereEx:BATCH_CRC32_MATCH');

CHECK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='SphereEx:DATA_DIFF', PROPERTIES('diff-storage-type'='DATABASE','chunk-size'='1000','diff-storage-unit-name'='ds_2','diff-table-name'='spex_cnschk_1','incremental-idle-seconds-threshold'='30'));

10. Start The Stopped Data Consistency Check Job #

START MIGRATION CHECK jobId

Name	Description
jobId	Job id

Example

Specify the jobId to specify the check algorithm to perform data consistency verification

START MIGRATION CHECK  'j01016e501b498ed1bdb2c373a2e85e2529a6' ;
Query OK, 0 rows affected (0.09 sec)

11. Stop Data Consistency Check Job #

STOP MIGRATION CHECK jobId

Name	Description
jobId	Job id

Example

Specify the jobId to specify the check algorithm to perform data consistency verification

STOP MIGRATION CHECK  'j01016e501b498ed1bdb2c373a2e85e2529a6' ;
Query OK, 0 rows affected (0.09 sec)

12. View Progress Of Data Consistency Check #

SHOW MIGRATION CHECK STATUS jobId

Name	Description
jobId	Job id

Example

Specify the jobId to view the progress of data consistency verification:

show migration check status j0102p00009e6221cef2c4467323e205a1e4d1604b;
+-------------------+--------+---------------------+---------------------+-------------------+-------------------------+----------------+------------------+---------------+
| tables            | result | check_failed_tables | finished_percentage | remaining_seconds | check_begin_time        | check_end_time | duration_seconds | error_message |
+-------------------+--------+---------------------+---------------------+-------------------+-------------------------+----------------+------------------+---------------+
| ds_0.t_order_copy |        |                     | 0                   | 0                 | 2023-07-26 18:57:37.431 |                | 0                |               |
+-------------------+--------+---------------------+---------------------+-------------------+-------------------------+----------------+------------------+---------------+

13. Commit Job #

COMMIT MIGRATION jobId;

Parameter Description

Name	Description
jobId	Job id

Example

Commit job specifying the jobId.

COMMIT MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';

14. Rollback Job #

ROLLBACK MIGRATION jobId;

Name	Description
jobId	Job id

Note: This statement will clean up the target table, please operate with caution.

Example

Specify the jobId to roll back the job.

ROLLBACK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';

15. Register Data Migration Task Storage Unit #

REGISTER MIGRATION SOURCE STORAGE UNIT

Parameter Description

Refer to Storage Node

Example

REGISTER MIGRATION SOURCE STORAGE UNIT ds_0 (
    URL="jdbc:mysql://127.0.0.1:3306/migration_ds_0?serverTimezone=UTC&useSSL=false",
    USER="root",
    PASSWORD="root",
    PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
);