Readwrite Splitting

Readwrite Splitting #

Syntax	Description	Type
CREATE READWRITE_SPLITTING RULE readwriteSplittingRuleDefinition [, readwriteSplittingRuleDefinition] …	Create readwrite splitting rule	RDL
ALTER READWRITE_SPLITTING RULE readwriteSplittingRuleDefinition [, readwriteSplittingRuleDefinition] …	Modify readwrite splitting rule	RDL
DROP READWRITE_SPLITTING RULE	Delete readwrite splitting rule	RDL
ALTER READWRITE_SPLITTING RULE [ groupName ] (ENABLE / DISABLE) storageUnitName [FROM databaseName]	Enable/Disable read database	RDL
SHOW READWRITE_SPLITTING RULES [FROM databaseName]	View readwrite splitting rule	RQL
SHOW STATUS FROM READWRITE_SPLITTING (RULES / RULE	groupName) [FROM databaseName]	Query readwrite splitting rule data source status
COUNT READWRITE_SPLITTING RULE	Count the number of readwrite splitting rules, this syntax will be discarded in subsequent versions	RQL

1. Create Readwrite Splitting Rule #

CREATE READWRITE_SPLITTING RULE readwriteSplittingRuleDefinition [, readwriteSplittingRuleDefinition] ...

readwriteSplittingRuleDefinition:
    ruleName ([staticReadwriteSplittingRuleDefinition | dynamicReadwriteSplittingRuleDefinition] 
              [, loadBalancerDefinition])

staticReadwriteSplittingRuleDefinition:
    WRITE_STORAGE_UNIT=storageUnitName, READ_STORAGE_UNITS(storageUnitName [, storageUnitName] ... )
transactionalReadQueryStrategyDefinition:
    TRANSACTIONAL_READ_QUERY_STRATEGY = transactionalReadQueryStrategyType
dynamicReadwriteSplittingRuleDefinition:
    AUTO_AWARE_RESOURCE=autoAwareResourceName [, WRITE_DATA_SOURCE_QUERY_ENABLED=writeDataSourceQueryEnabled]

loadBalancerDefinition:
    TYPE(NAME=loadBalancerType [, PROPERTIES([algorithmProperties] )] )

algorithmProperties:
    algorithmProperty [, algorithmProperty] ...

algorithmProperty:
    key=value

writeDataSourceQueryEnabled:
    TRUE | FALSE

Parameter Description

Name	Data type	Description
ruleName	IDENTIFIER	Rule name
storageUnitName	IDENTIFIER	Registered data source name
autoAwareResourceName	IDENTIFIER	Logical data source name for database discovery
writeDataSourceQueryEnabled	BOOLEAN	All read databases are offline, whether the primary database bears the read traffic
loadBalancerType	STRING	Load balance algorithm type

Support the creation of static readwrite-splitting rules and dynamic readwrite-splitting rules
Dynamic readwrite-splitting rules rely on database discovery rules
loadBalancerType specifies the load balancing algorithm type, please refer to load balance algorithm；
Duplicate ruleName will not be created
transactionalReadQueryStrategyType specifies the read request routing strategy within a transaction. Static routing can be configured with optional values: PRIMARY (routed to the primary database), FIXED (routed to a fixed data source within the same transaction), DYNAMIC (routed to a non-fixed data source within the same transaction). Default value: DYNAMIC

Example

Create static readwrite splitting rule

// Static
CREATE READWRITE_SPLITTING RULE ms_group_0 (
WRITE_STORAGE_UNIT=write_ds,
READ_STORAGE_UNITS(read_ds_0,read_ds_1),
TYPE(NAME="random")
);

Create dynamic readwrite splitting rule

// Dynamic
CREATE READWRITE_SPLITTING RULE ms_group_1 (
AUTO_AWARE_RESOURCE=group_0,
WRITE_DATA_SOURCE_QUERY_ENABLED=false,
TYPE(NAME="random")
);

2. Modify Readwrite Splitting Rule #

ALTER READWRITE_SPLITTING RULE readwriteSplittingRuleDefinition [, readwriteSplittingRuleDefinition] ...
readwriteSplittingRuleDefinition:
    ruleName ([staticReadwriteSplittingRuleDefinition | dynamicReadwriteSplittingRuleDefinition] 
              [, loadBalancerDefinition])

staticReadwriteSplittingRuleDefinition:
    WRITE_STORAGE_UNIT=storageUnitName, READ_STORAGE_UNITS(storageUnitName [, storageUnitName] ... )

dynamicReadwriteSplittingRuleDefinition:
    AUTO_AWARE_RESOURCE=autoAwareResourceName [, WRITE_DATA_SOURCE_QUERY_ENABLED=writeDataSourceQueryEnabled]

loadBalancerDefinition:
    TYPE(NAME=loadBalancerType [, PROPERTIES([algorithmProperties] )] )

algorithmProperties:
    algorithmProperty [, algorithmProperty] ...

algorithmProperty:
    key=value

writeDataSourceQueryEnabled:
    TRUE | FALSE

Parameter Description

Name	Data type	Description
ruleName	IDENTIFIER	Rule name
storageUnitName	IDENTIFIER	Registered data source name
autoAwareResourceName	IDENTIFIER	Logical data source name for database discovery
writeDataSourceQueryEnabled	BOOLEAN	All read databases are offline, whether the primary database bears the read traffic
loadBalancerType	STRING	Load balance algorithm type

Support the creation of static readwrite-splitting rules and dynamic readwrite-splitting rules
Dynamic readwrite-splitting rules rely on database discovery rules
loadBalancerType specifies the load balancing algorithm type, please refer to load balance algorithm；

Example

ALTER READWRITE_SPLITTING RULE ms_group_1 (
WRITE_STORAGE_UNIT=write_ds,
READ_STORAGE_UNITS(read_ds_0,read_ds_1,read_ds_2),
TYPE(NAME="random",PROPERTIES(write_ds=2,read_ds_0=2,read_ds_1=2,read_ds_2=1))
);

3. Delete Readwrite Splitting Rule #

DROP READWRITE_SPLITTING RULE ruleName [, ruleName] ...

Parameter Description

Name	Data type	Description
ruleName	IDENTIFIER	Rule name

Example

DROP READWRITE_SPLITTING RULE ms_group_1;

4. Enable/Disable Readwrite Splitting Rule #

ALTER READWRITE_SPLITTING RULE [ groupName ] (ENABLE / DISABLE) storageUnitName [FROM databaseName]

Example

ALTER READWRITE_SPLITTING RULE group_1 ENABLE read_ds_1

5. View Readwrite Splitting Rule #

SHOW READWRITE_SPLITTING RULES [FROM databaseName]

Example

Static readwrite splitting rules

mysql> SHOW READWRITE_SPLITTING RULES;
+------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
| name       | auto_aware_data_source_name | write_data_source_name | read_data_source_names | load_balancer_type | load_balancer_props |
+------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
| ms_group_0 |                             | ds_primary             | ds_slave_0, ds_slave_1 | random             |                     |
+------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
1 row in set (0.00 sec)

Dynamic readwrite splitting rules

mysql> SHOW READWRITE_SPLITTING RULES FROM readwrite_splitting_db;
+--------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
| name         | auto_aware_data_source_name | write_data_source_query_enabled | write_data_source_name | read_data_source_names | load_balancer_type | load_balancer_props |
+--------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
| readwrite_ds | ms_group_0                  |                                 |                        |                        | random             | read_weight=2:1     |
+-------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
1 row in set (0.01 sec)

Static and dynamic readwrite splitting rules

mysql> SHOW READWRITE_SPLITTING RULES FROM readwrite_splitting_db;
+--------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
| name         | auto_aware_data_source_name | write_data_source_query_enabled | write_data_source_name | read_data_source_names | load_balancer_type | load_balancer_props |
+--------------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
| readwrite_ds | ms_group_0                  |                                 | write_ds               | read_ds_0, read_ds_1   | random             | read_weight=2:1     |
+-------+-----------------------------+------------------------+------------------------+--------------------+---------------------+
1 row in set (0.00 sec)

Output Description

Column	Description
name	Rule name
auto_aware_data_source_name	Auto-Aware discovery data source name (Display configuration dynamic readwrite splitting rules)
write_data_source_query_enabled	All read data source are offline, write data source whether the data source is responsible for read traffic
write_data_source_name	Write data source name
read_data_source_names	Read data source name list
load_balancer_type	Load balance algorithm type
load_balancer_props	Load balance algorithm parameter

6. View Readwrite Splitting Data Source Status #

SHOW STATUS FROM READWRITE_SPLITTING (RULES / RULE groupName) [FROM databaseName]

Example

SHOW STATUS FROM READWRITE_SPLITTING RULES;