存储节点 #
- 语法列表
| 语法 | 描述 | 类型 |
|---|---|---|
| REGISTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] … | 注册db级别的存储节点 | RDL |
| ALTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] … | 调整db级别的存储节点 | RDL |
| UNREGISTER STORAGE UNIT storageUnitName [, storageUnitName] … [ignore single tables] | 注销db级别的存储节点 | RDL |
| SHOW STORAGE UNITS [ [FROM databaseName] [WHERE USAGE_COUNT = usageCount] ] | 查看当前逻辑库下的所有db级别的存储节点 | RQL |
1. 注册 db 级别的存储节点 #
REGISTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] ...
storageUnitDefinition:
simpleSource | urlSource
simpleSource:
storageUnitName(HOST=hostname,PORT=port,DB=dbName,USER=user [,PASSWORD=password] [,PROPERTIES(property [,property]) ...])
urlSource:
storageUnitName(URL=url,USER=user [,PASSWORD=password] [,PROPERTIES(property [,property]) ...])
property:
key=value
参数说明
| 名称 | 数据类型 | 说明 |
|---|---|---|
| storageUnitName | IDENTIFIER | 存储单元名称 |
| hostname | STRING | 数据源地址 |
| port | INT | 数据源端口 |
| dbName | STRING | 物理库名称 |
| url | STRING | URL 地址 |
| user | STRING | 用户名 |
| password | STRING | 密码 |
- 添加存储单元前请确认已经创建分布式数据库，并执行 use 命令成功选择一个数据库；
- 确认将要添加或修改的存储单元是可以正常连接的，否则将不能操作成功；
- 不允许重复的 storageUnitName ；
- PROPERTIES 用于自定义连接池参数，key 和 value 均为 STRING 类型；
- ALTER STORAGE UNIT 修改存储单元时不允许改变该存储单元关联的真实数据源；
- ALTER STORAGE UNIT 修改存储单元时会发生连接池的切换，此操作可能对进行中的业务造成影响，请谨慎使用；
- UNREGISTER STORAGE UNIT 只会删除逻辑存储单元，不会删除真实的数据源；
- 被规则引用的存储单元将无法被删除；
- 若存储单元只被 single table rule 引用，且用户确认可以忽略该限制，则可以添加可选参数 ignore single tables 进行强制删除。
- 1.5.0版本之后注册存储单元的逻辑发生变化，注册存储单元不会自动加载单表。如需加载单表请参考单表加载语法
示例
REGISTER STORAGE UNIT ds_0 (
HOST="127.0.0.1",
PORT=3306,
DB="db0",
USER="root",
PASSWORD="root"
),ds_1 (
HOST="127.0.0.1",
PORT=3306,
DB="db1",
USER="root"
),ds_2 (
HOST="127.0.0.1",
PORT=3306,
DB="db2",
USER="root",
PROPERTIES("maximumPoolSize"="10")
),ds_3 (
URL="jdbc:mysql://127.0.0.1:3306/db3?serverTimezone=UTC&useSSL=false",
USER="root",
PASSWORD="root",
PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);
2. 调整 db 级别的存储节点 #
ALTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] ...
storageUnitDefinition:
simpleSource | urlSource
simpleSource:
storageUnitName(HOST=hostname,PORT=port,DB=dbName,USER=user [,PASSWORD=password] [,PROPERTIES(property [,property]) ...])
urlSource:
storageUnitName(URL=url,USER=user [,PASSWORD=password] [,PROPERTIES(property [,property]) ...])
property:
key=value
参数说明
| 名称 | 数据类型 | 说明 |
|---|---|---|
| storageUnitName | IDENTIFIER | 存储单元名称 |
| hostname | STRING | 数据源地址 |
| port | INT | 数据源端口 |
| dbName | STRING | 物理库名称 |
| url | STRING | URL 地址 |
| user | STRING | 用户名 |
| password | STRING | 密码 |
示例
ALTER STORAGE UNIT ds_0 (
HOST="127.0.0.1",
PORT=3309,
DB="db0",
USER="root",
PASSWORD="root"
),ds_1 (
URL="jdbc:mysql://127.0.0.1:3309/db1?serverTimezone=UTC&useSSL=false",
USER="root",
PASSWORD="root",
PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);
3. 注销db级别的存储节点 #
UNREGISTER STORAGE UNIT storageUnitName [, storageUnitName] ... [ignore single tables]
示例
UNREGISTER STORAGE UNIT ds_0, ds_1;
UNREGISTER STORAGE UNIT ds_2, ds_3 ignore single tables;
4. 查看当前逻辑库下的所有 db 级别的存储节点 #
SHOW STORAGE UNITS [ [FROM databaseName] | [WHERE USAGE_COUNT = usageCount] ]
示例
查看当前逻辑库下的所有 db 级别的存储节点：
mysql> SHOW STORAGE UNITS;
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | type | host | port | db | connection_timeout_milliseconds | idle_timeout_milliseconds | max_lifetime_milliseconds | max_pool_size | min_pool_size | read_only | other_attributes |
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0 | MySQL | 127.0.0.1 | 3306 | db_0 | 30000 | 60000 | 1800000 | 50 | 1 | false | {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"poolName":"HikariPool-1","registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
| ds_1 | MySQL | 127.0.0.1 | 3306 | db_1 | 30000 | 60000 | 1800000 | 50 | 1 | false | {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"poolName":"HikariPool-2","registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.84 sec)
查看当前逻辑库下的指定 ID 的 db 级别的存储节点
mysql> SHOW STORAGE UNITS FROM sharding_db;
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name | type | host | port | db | connection_timeout_milliseconds | idle_timeout_milliseconds | max_lifetime_milliseconds | max_pool_size | min_pool_size | read_only | other_attributes |
+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ds_0 | MySQL | 127.0.0.1 | 3306 | db_0 | 30000 | 60000 | 1800000 | 50 | 1 | false | {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"poolName":"HikariPool-1","registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
| ds_1 | MySQL | 127.0.0.1 | 3306 | db_1 | 30000 | 60000 | 1800000 | 50 | 1 | false | {"dataSourceProperties":{"cacheServerConfiguration":"true","elideSetAutoCommits":"true","useServerPrepStmts":"true","cachePrepStmts":"true","rewriteBatchedStatements":"true","cacheResultSetMetadata":"false","useLocalSessionState":"true","maintainTimeStats":"false","prepStmtCacheSize":"8192","tinyInt1isBit":"false","prepStmtCacheSqlLimit":"2048","netTimeoutForStreamingResults":"0","zeroDateTimeBehavior":"round"},"healthCheckProperties":{},"initializationFailTimeout":1,"validationTimeout":5000,"leakDetectionThreshold":0,"poolName":"HikariPool-2","registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |+------+-------+-----------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.84 sec)
输出说明
| 列 | 说明 |
|---|---|
| name | 数据源名称 |
| type | 数据源类型 |
| host | 数据源地址 |
| port | 数据源端口 |
| db | 数据库名称 |
| attribute | 数据源参数 |
5. 查看当前逻辑库下的未被使用的所有 db 级别的存储节点 #
SHOW UNUSED STORAGE UNITS
参数说明
无
示例
SHOW UNUSED STORAGE UNITS