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
DROP MIGRATION CHECK jobId	Drop 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

Modify data migration rule #

copyALTER MIGRATION RULE ();

Config Description

copyALTER 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

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

Example

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

View Data Migration Rule #

copySHOW MIGRATION RULE

Parameter Description

None

Example

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

Modify Data Migration Rule List #

copySHOW MIGRATION LIST;

Parameter Description

None

Example

copy+-------------------------------------+---------+----------------------+--------+---------------------+-----------+
| 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

View Data Migration Job Status #

copySHOW MIGRATION STATUS jobId;

Parameter Description

Name	Description
jobId	Data migration job id

Example

copySHOW 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

Start Data Migration #

copyMIGRATE TABLE schema_name.table_name INTO schema_name.table_name;

Example

The storage node is MySQL starts data migration

copyMIGRATE TABLE ds_0.t_order INTO t_order;

The storage node is MySQL or other specified target logical database

copyMIGRATE TABLE ds_0.t_order INTO sharding_db.t_order;

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

copyMIGRATE 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;

Start Stopped Job #

copySTART MIGRATION jobId

Parameter Description

Name	Description
jobId	Job id

Example

Start stopped job with the specified job id

copySTART MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';

Stop Migration Job #

copySTOP MIGRATION jobId

Parameter Description

Name	Description
jobId	Job id

Example

Start stopped job with the specified job id

copySTOP MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';

View data consistency algorithm #

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

copySHOW MIGRATION CHECK ALGORITHMS;

Example

copySHOW 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

Perform data consistency check #

copyCHECK 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

copyCHECK 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'));

Start The Stopped Data Consistency Check Job #

copySTART MIGRATION CHECK jobId

Name	Description
jobId	Job id

Example

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

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

Stop Data Consistency Check Job #

copySTOP MIGRATION CHECK jobId

Name	Description
jobId	Job id

Example

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

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

Drop Data Consistency Check Job #

copyDROP MIGRATION CHECK jobId

Name	Description
jobId	Job id

Example

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

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

View Progress Of Data Consistency Check #

copySHOW MIGRATION CHECK STATUS jobId

Name	Description
jobId	Job id

Example

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

copyshow 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                |               |
+-------------------+--------+---------------------+---------------------+-------------------+-------------------------+----------------+------------------+---------------+

Commit Job #

copyCOMMIT MIGRATION jobId;

Parameter Description

Name	Description
jobId	Job id

Example

Commit job specifying the jobId.

copyCOMMIT MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';

Rollback Job #

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

copyROLLBACK MIGRATION 'j01016e501b498ed1bdb2c373a2e85e2529a6';

Register Data Migration Task Storage Unit #

copyREGISTER MIGRATION SOURCE STORAGE UNIT

Parameter Description

Refer to Storage Node

Example

copyREGISTER 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")
);