弹性伸缩 | SphereEx-DBPlusSuite

弹性伸缩 #

语法	描述	类型
ALTER RESHARDING RULE	定义扩缩容规则	RAL
RESHARD TABLE tableName BY rule	启动扩缩容	RDL
[PARTIAL] RESHARD TABLE tableName ADD STORAGE UNITS	分片表部分重分布，增加存储节点	RDL
[PARTIAL] RESHARD TABLE tableName DROP STORAGE UNITS	分片表部分重分布，减少存储节点	RDL
[PARTIAL] RESHARD TABLE tableName REPLACE STORAGE UNITS	分片表部分重分布，替换存储节点	RDL
SHOW RESHARDING RULE	查看扩缩容规则	RAL
SHOW RESHARDING LIST	查看扩缩容作业列表	RAL
SHOW RESHARDING STATUS jobId	查看扩缩容作业状态	RAL
SHOW RESHARDING STALE SHARDING TABLES jobId	查看扩缩容遗留分片表	RAL
START RESHARDING jobId	启动已经停止的扩缩容作业	RAL
STOP RESHARDING jobId	停止扩缩容作业	RAL
SHOW RESHARDING CHECK ALGORITHMS	查看一致性校验算法	RAL
CHECK RESHARDING jobId	执行扩缩容数据一致性校验	RAL
SHOW RESHARDING CHECK STATUS jobId	查看扩缩容数据一致性校验作业状态	RAL
START RESHARDING CHECK jobId	启动停止的扩缩容数据一致性校验作业	RAL
STOP RESHARDING CHECK jobId	停止扩缩容数据一致性校验作业	RAL
DROP RESHARDING CHECK jobId	删除一致性校验作业	RAL
COMMIT RESHARDING jobId	提交扩缩容作业	RAL
FORCE COMMIT RESHARDING jobId	强制提交扩缩容作业	RAL
ROLLBACK RESHARDING jobId	撤销扩缩容作业	RAL
APPLY RESHARDING jobId	应用扩缩容新配置（切换元数据）	RAL
FORCE APPLY RESHARDING jobId	强制应用扩缩容新配置（切换元数据）	RAL
REVERT APPLY RESHARDING jobId	撤销已应用的扩缩容新配置	RAL
STOP RESHARDING SOURCE WRITING jobId	停写	RAL
RESTORE RESHARDING SOURCE WRITING jobId	恢复停写	RAL
AUTO RESHARD TABLE tableName BY rule	扩缩容自动模式	RDL
RESHARD SINGLE TABLE tableName TO BROADCAST	单表扩容成广播表	RDL
AUTO RESHARD SINGLE TABLE tableName TO BROADCAST	单表扩容成广播表自动模式	RDL
CREATE DEFAULT AUTO_RESHARDING STRATEGY	创建默认自动扩缩容策略	RDL
ALTER DEFAULT AUTO_RESHARDING STRATEGY	修改默认自动扩缩容策略	RDL
DROP DEFAULT AUTO_RESHARDING STRATEGY	删除默认自动扩缩容策略	RDL
SHOW DEFAULT AUTO_RESHARDING STRATEGY	查看默认自动扩缩容策略	RAL
SHOW AUTO_RESHARDING STRATEGY tableName	查看指定表的自动扩缩容策略	RAL
SHOW AUTO_RESHARDING STRATEGIES	查看全部表的自动扩缩容策略	RAL
UPDATE RESHARDING jobId OFFSET POSITION	更新扩缩容增量进度	RAL
PRECHECK RESHARD TABLE tableName BY rule	重分布预检查	RAL
PRECHECK RESHARD SINGLE TABLE tableName TO BROADCAST	单表转广播表预检查	RAL
PRECHECK REGISTER STORAGE UNIT storageUnitName rule	注册存储节点时广播表预检查	RAL
PRECHECK [PARTIAL] RESHARD TABLE tableName ADD STORAGE UNITS	分片表部分重分布预检查，增加存储节点	RAL
PRECHECK [PARTIAL] RESHARD TABLE tableName DROP STORAGE UNITS	分片表部分重分布预检查，减少存储节点	RAL
PRECHECK [PARTIAL] RESHARD TABLE tableName REPLACE STORAGE UNITS	分片表部分重分布预检查，替换存储节点	RAL

1. 定义扩缩容规则 #

ALTER RESHARDING RULE

因扩缩容 rule 具有默认值，无需创建，仅提供 ALTER 语句。

完整配置 DistSQL 示例

ALTER RESHARDING RULE (
READ(
  WORKER_THREAD=20,
  BATCH_SIZE=1000,
  SHARDING_SIZE=10000000,
  RATE_LIMITER (TYPE(NAME='QPS',PROPERTIES('qps'='500')))
),
WRITE(
  WORKER_THREAD=20,
  BATCH_SIZE=1000,
  RATE_LIMITER (TYPE(NAME='TPS',PROPERTIES('tps'='2000')))
),
STREAM_CHANNEL (TYPE(NAME='MEMORY',PROPERTIES('block-queue-size'='2000')))
);

配置说明

ALTER RESHARDING RULE (
READ( -- 数据读取配置。如果不配置则部分参数默认生效。
  WORKER_THREAD=20, -- 从源端摄取全量数据的线程池大小。如果不配置则使用默认值。
  BATCH_SIZE=1000, -- 一次查询操作返回的最大记录数。如果不配置则使用默认值。
  SHARDING_SIZE=10000000, -- 全量数据分片大小。如果不配置则使用默认值。
  RATE_LIMITER ( -- 限流算法。如果不配置则不限流。
  TYPE( -- 算法类型。可选项：QPS
  NAME='QPS',
  PROPERTIES( -- 算法属性
  'qps'='500'
  )))
),
WRITE( -- 数据写入配置。如果不配置则部分参数默认生效。
  WORKER_THREAD=20, -- 数据写入到目标端的线程池大小。如果不配置则使用默认值。
  BATCH_SIZE=1000, -- 一次批量写入操作的最大记录数。如果不配置则使用默认值。
  RATE_LIMITER ( -- 限流算法。如果不配置则不限流。
  TYPE( -- 算法类型。可选项：TPS
  NAME='TPS',
  PROPERTIES( -- 算法属性
  'tps'='2000'
  )))
),
STREAM_CHANNEL ( -- 数据通道，连接生产者和消费者，用于 read 和 write 环节。如果不配置则默认使用 MEMORY 类型。
TYPE( -- 算法类型。可选项：MEMORY
NAME='MEMORY',
PROPERTIES( -- 算法属性
'block-queue-size'='2000' -- 属性：阻塞队列大小
)))
);

2. 启动扩缩容 #

RESHARD TABLE tableName BY rule

参数说明

参数名	说明
tableName	执行扩缩容的表

示例

RESHARD TABLE t_order BY(
    STORAGE_UNITS(ds_0, ds_1, ds_2),
    SHARDING_COLUMN=order_id,
    TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
    KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);

分片表部分重分布可增加、减少或替换存储节点：

[PARTIAL] RESHARD TABLE tableName ADD STORAGE UNITS (storageUnitName [, ...]);

[PARTIAL] RESHARD TABLE tableName DROP STORAGE UNITS (storageUnitName [, ...]);

[PARTIAL] RESHARD TABLE tableName REPLACE STORAGE UNITS (sourceStorageUnitName TO targetStorageUnitName [, ...]);

示例

PARTIAL RESHARD TABLE t_order ADD STORAGE UNITS (ds_2, ds_3);

PARTIAL RESHARD TABLE t_order DROP STORAGE UNITS (ds_3);

PARTIAL RESHARD TABLE t_order REPLACE STORAGE UNITS (ds_2 TO ds_3);

3. 查看扩缩容规则 #

SHOW RESHARDING RULE;

参数说明

无

4. 查看扩缩容作业列表 #

SHOW RESHARDING LIST;

参数说明

无

示例

SHOW RESHARDING LIST;
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id                                    | tables  | sharding_total_count | active | create_time         | stop_time |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| j51017f973ac82cb1edea4f5238a258c25e89 | t_order | 2                    | true   | 2022-10-25 10:10:58 | NULL      |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+

5. 查看扩缩容作业状态 #

SHOW RESHARDING STATUS jobId;

参数说明

参数名	说明
jobid	作业id

示例

SHOW RESHARDING STATUS 'j51017f973ac82cb1edea4f5238a258c25e89';
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | remaining_seconds | incremental_idle_seconds | error_message |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
| 1    | ds_1        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+

查询扩缩容遗留分片表：

SHOW RESHARDING STALE SHARDING TABLES jobId;

示例

SHOW RESHARDING STALE SHARDING TABLES 'j51017f973ac82cb1edea4f5238a258c25e89';

6. 启动已经停止的扩缩容作业 #

START RESHARDING jobId

参数说明

参数名	说明
jobid	作业id

示例

START RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

7. 停止扩缩容作业 #

STOP RESHARDING jobId

参数说明

参数名	说明
jobid	作业id

示例

STOP RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

8. 查看数据一致性校验算法 #

一致性校验算法选择请参考数据一致性校验算法。

SHOW RESHARDING CHECK ALGORITHMS

示例

SHOW RESHARDING 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.                   |
| BATCH_CRC32_MATCH          | SphereEx:BATCH_CRC32_MATCH,SphereEx:DATA_CONSISTENCY_CHECK_CRC32_MATCH | MySQL,MariaDB,H2,Aurora                                                                       | Match CRC32 of records (Commercial edition). |
| DATA_DIFF                  | SphereEx:DATA_DIFF                         | MySQL,MariaDB,H2,Aurora,PostgreSQL,openGauss                                                  | Diff raw data of records.                    |
+----------------------------+---------------------------------------------+-----------------------------------------------------------------------------------------------+----------------------------------------------+

输出说明

type : 算法名
type_aliases : 算法别名
supported_database_types : 算法支持哪些数据库
description : 算法说明

9. 执行扩缩容数据一致性 #

CHECK RESHARDING jobId [BY TYPE (NAME='algorithmType' [, PROPERTIES('key'='value')])] [, LOGIC_TABLES(TABLE(NAME='logicTableName', QUERY_CONDITION='queryCondition') [, ...])];

参数名	说明
jobId	作业id
algorithmType	一致性校验算法类型。可选，不指定时使用默认算法
PROPERTIES	一致性校验算法属性。可选
LOGIC_TABLES	指定待校验的逻辑表及查询条件。可选。可配置多个 `TABLE`
logicTableName	需要校验的逻辑表名称
queryCondition	逻辑表校验范围，必须使用引号包裹，且字符串内容必须使用括号包裹，例如 `'(id>=1001 AND id<2000)'`

示例

指定 jobId 指定校验算法执行数据一致性校验

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

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

CHECK RESHARDING 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='BATCH_CRC32_MATCH');

CHECK RESHARDING 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='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'));

CHECK RESHARDING 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='DATA_MATCH', PROPERTIES('chunk-size'='1000')), LOGIC_TABLES(TABLE(NAME='t_order', QUERY_CONDITION='(order_id >= 1000 AND order_id < 2000)'));

查询数据一致性校验作业状态：

SHOW RESHARDING CHECK STATUS jobId;

示例

SHOW RESHARDING CHECK STATUS 'j51017f973ac82cb1edea4f5238a258c25e89';
+---------+--------+---------------------+----------------+----------+--------+-------------------------------+-----------------------------+-----------------------------------+--------------------------+-------------------------+-------------------------+------------------+----------------+-----------------+---------------+---------------+
| tables  | result | check_failed_tables | ignored_tables | status   | active | inventory_finished_percentage | inventory_remaining_seconds | incremental_local_last_commit_time | incremental_idle_seconds | check_begin_time        | check_end_time          | duration_seconds | algorithm_type | algorithm_props | tables_filter | error_message |
+---------+--------+---------------------+----------------+----------+--------+-------------------------------+-----------------------------+-----------------------------------+--------------------------+-------------------------+-------------------------+------------------+----------------+-----------------+---------------+---------------+
| t_order | true   |                     |                | FINISHED | false  | 100                           | 0                           |                                   |                          | 2024-02-19 16:07:40.926 | 2024-02-19 16:07:42.178 | 1                | DATA_MATCH     |                 |               |               |
+---------+--------+---------------------+----------------+----------+--------+-------------------------------+-----------------------------+-----------------------------------+--------------------------+-------------------------+-------------------------+------------------+----------------+-----------------+---------------+---------------+

输出说明

tables : 表名
result : 校验结果
check_failed_tables : 校验失败的表
ignored_tables : 被忽略的表
status : 校验作业状态
active : 校验作业是否活跃
inventory_finished_percentage : 存量数据校验完成百分比。由于计算基数是历史值，并且可能是估算结果，所以完成百分比可能存在一定误差
inventory_remaining_seconds : 存量数据校验剩余时间（单位：秒）。由于计算基数是历史值，并且可能是估算结果，所以剩余时间可能存在一定误差
incremental_local_last_commit_time : 最近一次增量数据提交时间
incremental_idle_seconds : 增量数据不活跃时间（单位：秒）
check_begin_time : 校验开始时间
check_end_time : 校验结束时间
duration_seconds : 校验耗时（单位：秒）
algorithm_type : 一致性校验算法类型
algorithm_props : 一致性校验算法属性
tables_filter : 本次校验指定的逻辑表及查询条件
error_message : 错误信息

10. 启动停止的扩缩容数据一致性检测作业 #

START RESHARDING CHECK jobId

参数说明

参数名	说明
jobid	作业id

示例

START RESHARDING CHECK  'j51017f973ac82cb1edea4f5238a258c25e89';

11. 停止扩缩容数据一致性检测作业 #

STOP RESHARDING CHECK jobId

参数说明

参数名	说明
jobid	作业id

示例

STOP RESHARDING CHECK  'j51017f973ac82cb1edea4f5238a258c25e89';

12. 删除扩缩容数据一致性检测作业 #

DROP RESHARDING CHECK jobId

参数说明

参数名	说明
jobid	作业id

示例

DROP RESHARDING CHECK 'j51017f973ac82cb1edea4f5238a258c25e89';

13. 提交扩缩容作业 #

COMMIT RESHARDING jobId [, DROP SOURCE TABLE = true|false]

参数说明

参数名	说明
jobId	作业id
DROP SOURCE TABLE	是否删除源端表，默认值为 false

示例

COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89', DROP SOURCE TABLE = false;

14. 强制提交扩缩容作业 #

FORCE COMMIT RESHARDING jobId [, DROP SOURCE TABLE = true|false]

参数说明

参数名	说明
jobid	作业id
DROP SOURCE TABLE	是否删除源端表，默认值为 false

示例

FORCE COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89', DROP SOURCE TABLE = true;

15. 撤销扩缩容作业 #

ROLLBACK RESHARDING jobId [, DROP TARGET TABLE = true|false]

参数说明

参数名	说明
jobid	作业id
DROP TARGET TABLE	是否删除目标端表，默认值为 false

示例

ROLLBACK RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

ROLLBACK RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89', DROP TARGET TABLE = true;

16. 应用扩缩容新配置（切换元数据） #

APPLY RESHARDING jobId

参数说明

参数名	说明
jobid	作业id

示例

APPLY RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

17. 强制应用扩缩容新配置（切换元数据） #

FORCE APPLY RESHARDING jobId

参数说明

参数名	说明
jobid	作业id

示例

FORCE APPLY RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

18. 撤销已应用的扩缩容新配置 #

REVERT APPLY RESHARDING jobId

参数说明

参数名	说明
jobid	作业id

示例

REVERT APPLY RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

19. 停写 #

STOP RESHARDING SOURCE WRITING jobId

参数说明

参数名	说明
jobid	作业id

示例

STOP RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';

20. 恢复停写 #

RESTORE RESHARDING SOURCE WRITING jobId

参数说明

参数名	说明
jobid	作业id

示例

RESTORE RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';

21. 扩缩容自动模式 #

AUTO RESHARD TABLE tableName BY rule

参数说明

参数名	说明
tableName	执行扩缩容的表

示例

AUTO RESHARD TABLE t_order BY(
    STORAGE_UNITS(ds_0, ds_1, ds_2),
    SHARDING_COLUMN=order_id,
    TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
    KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);

表规则可配置自动扩缩容策略：

AUTO RESHARD TABLE t_order BY(
    STORAGE_UNITS(ds_0, ds_1),
    SHARDING_COLUMN=creation_date,
    TYPE(NAME="AUTO_INTERVAL", PROPERTIES("datetime-lower"="2024-01-01 00:00:00", "datetime-upper"="2024-12-31 23:59:59", "sharding-seconds"="86400")),
    AUTO_RESHARDING_STRATEGY(
        MATCHING_ALGORITHM(TYPE(NAME="INTERVAL_UPPER_TIME_HIGH_WATERLINE", PROPERTIES("remaining-seconds-until-upper-time"=1296000))),
        ACTION_ALGORITHM(TYPE(NAME="SCALE_SHARDING", PROPERTIES("operation-type"="ADD", "amount"=10)))
    )
);

默认自动扩缩容策略：

CREATE DEFAULT AUTO_RESHARDING STRATEGY(
    MATCHING_ALGORITHM(TYPE(NAME="INTERVAL_UPPER_TIME_HIGH_WATERLINE", PROPERTIES("remaining-seconds-until-upper-time"=1296000))),
    ACTION_ALGORITHM(TYPE(NAME="SCALE_SHARDING", PROPERTIES("operation-type"="ADD", "amount"=10))),
    AUTO_RESHARDING_TIME_WINDOW="00:00,23:59"
);

ALTER DEFAULT AUTO_RESHARDING STRATEGY(
    MATCHING_ALGORITHM(TYPE(NAME="INTERVAL_UPPER_TIME_HIGH_WATERLINE", PROPERTIES("remaining-seconds-until-upper-time"=86400))),
    ACTION_ALGORITHM(TYPE(NAME="SCALE_SHARDING", PROPERTIES("operation-type"="ADD", "amount"=5))),
    AUTO_RESHARDING_TIME_WINDOW="00:00,23:59"
);

DROP DEFAULT AUTO_RESHARDING STRATEGY;

SHOW DEFAULT AUTO_RESHARDING STRATEGY;

SHOW AUTO_RESHARDING STRATEGY t_order;

SHOW AUTO_RESHARDING STRATEGIES;

22. 单表扩容成广播表 #

RESHARD SINGLE TABLE tableName TO BROADCAST

参数说明

参数名	说明
tableName	执行扩缩容的单表

示例

RESHARD SINGLE TABLE t_address TO BROADCAST

23. 单表扩容成广播表自动模式 #

AUTO RESHARD SINGLE TABLE tableName TO BROADCAST

参数说明

参数名	说明
tableName	执行扩缩容的单表

示例

AUTO RESHARD SINGLE TABLE t_address TO BROADCAST

24. 更新扩缩容增量进度 #

UPDATE RESHARDING jobId OFFSET POSITION('storageUnitName'='position');

参数名	说明
storageUnitName	存储单元名称
position	增量进度位点

示例

更新 MySQL 进度的格式是 file#pos，写法如下

UPDATE RESHARDING j51017f973ac82cb1edea4f5238a258c25e89 OFFSET POSITION('ds_0'='mysql-bin.000001#111','ds_1'='mysql-bin.000002#222');

25. 重分布预检查 #

PRECHECK RESHARD TABLE tableName BY rule

参数名	说明
tableName	表名称

示例

PRECHECK RESHARD TABLE t_order BY (
    STORAGE_UNITS(ds_1, ds_2),
    SHARDING_COLUMN=order_id,
    TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
    KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);

mysql> PRECHECK RESHARD TABLE t_order BY (
    -> DATANODES("ds_${0..1}.t_single_${0..1}"),
    -> DATABASE_STRATEGY(TYPE="standard",SHARDING_COLUMN=id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="ds_${id % 2}")))),
    -> TABLE_STRATEGY(TYPE="standard",SHARDING_COLUMN=id,SHARDING_ALGORITHM(TYPE(NAME="inline",PROPERTIES("algorithm-expression"="t_single_${id % 2}")))),
    -> KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
    -> );
+-----------------------------+--------+--------------------------------------------------------------------------------------------------------------------------+
| item                        | status | reason                                                                                                                   |
+-----------------------------+--------+--------------------------------------------------------------------------------------------------------------------------+
| Connection                  | true   |                                                                                                                          |
| Binlog                      | true   |                                                                                                                          |
| Replication                 | true   |                                                                                                                          |
| Table name length           | true   |                                                                                                                          |
| Primary key or unique key   | false  | No primary key and unique key: ds_0.t_order                                                                              |
| Table structure             | true   |                                                                                                                          |
| Privileges                  | true   |                                                                                                                          |
| TimeZone                    | true   |                                                                                                                          |
| Character set and collation | false  | Sample storage unit: ds_1, sample character set: utf8mb3, sample collation: utf8mb3_unicode_ci, diff storage units: ds_0 |
+-----------------------------+--------+--------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.08 sec)

26. 单表转广播表预检查 #

PRECHECK RESHARD SINGLE TABLE tableName TO BROADCAST

参数名	说明
tableName	表名称

示例

PRECHECK RESHARD SINGLE TABLE t_single TO BROADCAST;

mysql> PRECHECK RESHARD SINGLE TABLE t_single TO BROADCAST;
+-----------------------------+--------+--------------------------------------------------------------------------------------------------------------------------+
| item                        | status | reason                                                                                                                   |
+-----------------------------+--------+--------------------------------------------------------------------------------------------------------------------------+
| Connection                  | true   |                                                                                                                          |
| Binlog                      | false  | Not enabled binlog: ds_1                                                                                                 |
| Replication                 | true   |                                                                                                                          |
| Table name length           | true   |                                                                                                                          |
| Primary key or unique key   | false  | No primary key and unique key: ds_1.t_single                                                                             |
| Table structure             | true   |                                                                                                                          |
| Privileges                  | true   |                                                                                                                          |
| TimeZone                    | true   |                                                                                                                          |
| Character set and collation | false  | Sample storage unit: ds_1, sample character set: utf8mb3, sample collation: utf8mb3_unicode_ci, diff storage units: ds_0 |
+-----------------------------+--------+--------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.09 sec)

27. 注册存储节点时广播表预检查 #

PRECHECK REGISTER STORAGE UNIT storageUnitName rule

参数名	说明
storageUnitName	存储节点名称

示例

PRECHECK REGISTER STORAGE UNIT ds_3 (
    URL='jdbc:mysql://127.0.0.1:3306/db3?useSSL=false',
    USER='root',
    PASSWORD='root',
    PROPERTIES('minPoolSize'='1', 'connectionTimeoutMilliseconds'='30000', 'maxLifetimeMilliseconds'='1800000', 'idleTimeoutMilliseconds'='60000', 'maxPoolSize'='50')
);

mysql> PRECHECK REGISTER STORAGE UNIT ds_3 (
    ->     URL = "jdbc:mysql://127.0.0.1:3306/demo_ds?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
    ->     USER = "root",
    ->     PASSWORD = "123456",
    ->     PROPERTIES("maximumPoolSize" = 10, "minimumIdle"=5, "idleTimeout" = "30000")
    -> )\G
*************************** 1. row ***************************
      name: ds_3
    result: false
connection: success
 privilege: success
 broadcast: fail
   message: Broadcast table `t_address` check failed: [Binlog] Not enabled binlog: ds_1;[Primary key or unique key] No primary key and unique key: ds_1.t_address, ds_0.t_address;[Character set and collation] Sample storage unit: ds_3, sample character set: utf8mb4, sample collation: utf8mb4_unicode_ci, diff storage units: ds_1
1 row in set (0.07 sec)

28. 分片表部分重分布预检查，增加存储节点 #

PRECHECK [PARTIAL] RESHARD TABLE tableName ADD STORAGE UNITS

参数名	说明
tableName	表名称

示例

PRECHECK PARTIAL RESHARD TABLE t_order ADD STORAGE UNITS (ds_2,ds_3);

29. 分片表部分重分布预检查，减少存储节点 #

PRECHECK [PARTIAL] RESHARD TABLE tableName DROP STORAGE UNITS

参数名	说明
tableName	表名称

示例

PRECHECK PARTIAL RESHARD TABLE t_order DROP STORAGE UNITS (ds_2, ds_3);

30. 分片表部分重分布预检查，替换存储节点 #

PRECHECK [PARTIAL] RESHARD TABLE tableName REPLACE STORAGE UNITS

参数名	说明
tableName	表名称

示例

PRECHECK PARTIAL RESHARD TABLE t_order REPLACE STORAGE UNITS (ds_1 TO ds_11, ds_2 TO ds_12);