Storage Units

Storage Units #

Syntax list

Syntax	Description	Type
REGISTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] …	Register db level storage unit	RDL
ALTER STORAGE UNIT storageUnitDefinition [, storageUnitDefinition] …	Alter db level storage unit	RDL
UNREGISTER STORAGE UNIT storageUnitName [, storageUnitName] … [ignore single tables]	Unregister db level storage unit	RDL
SHOW STORAGE UNITS [ [FROM databaseName] [WHERE USAGE_COUNT = usageCount] ]	View all db level storage node for current logical database	RQL
SHOW UNUSED STORAGE UNITS	Query unused storage units	RQL
SHOW LOADING METADATA LIST	Query metadata loading progress	RQL

1. Register Storage Unit #

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

Parameter Explanation

Name	Data type	Description
storageUnitName	IDENTIFIER	Storage unit name
hostname	STRING	Data source address
port	INT	Data source port
dbName	STRING	Physical database name
url	STRING	URL address
user	STRING	User name
password	STRING	Password

Before adding storage unit, please confirm that a distributed database has been created, and execute the use command to successfully select a database;
Confirm that the storage unit to be added or altered can be connected, otherwise the operation will not be successful;
Duplicate storageUnitName is not allowed;
PROPERTIES is used to customize connection pool parameters, key and value are both STRING types;
ALTER STORAGE UNIT is not allowed to change the real data source associated with this storage unit;
ALTER STORAGE UNIT will switch the connection pool. This operation may affect the ongoing business, please use it with caution;
UNREGISTER STORAGE UNIT will only delete logical storage unit, not real data sources;
Storage unit referenced by rules cannot be deleted;
If the storage unit is only referenced by single table rule, and the user confirms that the restriction can be ignored, the optional parameter ignore single tables can be added to perform forced deletion.
After 1.5.0, the logic of the registered storage unit has changed. The registered storage unit does not automatically load the single tables. To load a single table, refer to Load the single tables syntax

Example

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. Alter Storage Unit #

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

Parameter Explanation

Name	Data type	Description
storageUnitName	IDENTIFIER	Storage unit name
hostname	STRING	Data source address
port	INT	Data source port
dbName	STRING	Physical database name
url	STRING	URL address
user	STRING	User name
password	STRING	Password

Example

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. Unregister Storage Unit #

UNREGISTER STORAGE UNIT storageUnitName [, storageUnitName] ... [ignore single tables]

Example

UNREGISTER STORAGE UNIT ds_0, ds_1;
UNREGISTER STORAGE UNIT ds_2, ds_3 ignore single tables;

4. Query storage unit #

SHOW STORAGE UNITS [ [FROM databaseName] | [WHERE USAGE_COUNT = usageCount] ]

Example

View all db level storage units for current logical database

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)

View all db level storage units for specified logical database

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)

Output Description

Column	Description
name	Data source name
type	Data source type
host	Data source host
port	Data source port
db	Database name
attribute	Data source attribute

5. View all unused storage units for current logical database #

SHOW UNUSED STORAGE UNITS

Example

SHOW UNUSED STORAGE UNITS

6. Query metadata loading progress #

When a user registers a storage unit and there are a large number of tables in the data source, metadata loading may take more time. In this case, the user can

SHOW LOADING METADATA LIST

示例

mysql> SHOW LOADING METADATA LIST;
+-------------------+--------------------------------------+-------+-----------+
| storage_unit_name | instance_id                          | total | completed |
+-------------------+--------------------------------------+-------+-----------+
| ds_0              | 9b05aa47-5e72-4603-8641-375e1f78e9bb | 129   | 118       |
+-------------------+--------------------------------------+-------+-----------+
1 rows in set (0.01 sec)

Output Description

Column	Description
storage_unit_name	Storage unit name
instance_id	Instance id
total	Total number of table
completed	Total number of loaded

Speical Note #

Since Proxy does not support the Oracle protocol, users using Oracle database can set the Proxy front-end protocol (proxy-frontend-database-protocol-type) to MySQL and register the Oracle storage unit:

REGISTER STORAGE UNIT ds_0 (
    URL="jdbc:oracle:thin:@//127.0.0.1:1521/helowin",
    USER="system",
    PASSWORD="oracle",
    PROPERTIES("maximumPoolSize"=10)
);