读写分离 | SphereEx-DBPlusSuite

读写分离 #

语法	描述	类型
CREATE READWRITE_SPLITTING RULE readwriteSplittingRuleDefinition [, readwriteSplittingRuleDefinition] …	创建读写分离规则	RDL
ALTER READWRITE_SPLITTING RULE readwriteSplittingRuleDefinition [, readwriteSplittingRuleDefinition] …	修改读写分离规则	RDL
DROP READWRITE_SPLITTING RULE	删除读写分离规则	RDL
ALTER READWRITE_SPLITTING RULE [ groupName ] (ENABLE / DISABLE) storageUnitName [FROM databaseName]	启用 / 禁用读库	RDL
SHOW READWRITE_SPLITTING RULES [FROM databaseName]	查看读写分离规则	RQL
SHOW STATUS FROM READWRITE_SPLITTING (RULES / RULE groupName) [FROM databaseName]	查询读写分离数据源状态	RQL
COUNT READWRITE_SPLITTING RULE	统计读写分离规则的数量，在后续版本中将废弃此语法	RQL

1. 创建读写分离规则 #

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

参数说明

名称	数据类型	说明
ruleName	IDENTIFIER	规则名称
storageUnitName	IDENTIFIER	已注册的数据源名称
autoAwareResourceName	IDENTIFIER	数据库发现的逻辑数据源名称
writeDataSourceQueryEnabled	BOOLEAN	读库全部下线，主库是否承担读流量
loadBalancerType	STRING	负载均衡算法类型

支持创建静态读写分离规则和动态读写分离规则；
动态读写分离规则依赖于数据库发现规则；
loadBalancerType 指定负载均衡算法类型，请参考负载均衡算法；
重复的 ruleName 将无法被创建。
transactionalReadQueryStrategyType 指定事务内读请求路由策略，静态路由可配置，可选值：PRIMARY（路由至主库）、FIXED（同一事务内路由至固定数据源）、DYNAMIC（同一事务内路由至非固定数据源）。默认值：DYNAMIC

示例

创建静态的读写分离 rule

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

创建动态的读写分离 rule

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

2. 修改读写分离规则 #

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

参数说明

名称	数据类型	说明
ruleName	IDENTIFIER	规则名称
storageUnitName	IDENTIFIER	已注册的数据源名称
autoAwareResourceName	IDENTIFIER	数据库发现的逻辑数据源名称
writeDataSourceQueryEnabled	BOOLEAN	读库全部下线，主库是否承担读流量
loadBalancerType	STRING	负载均衡算法类型

支持创建静态读写分离规则和动态读写分离规则；
动态读写分离规则依赖于数据库发现规则；
loadBalancerType 指定负载均衡算法类型，请参考负载均衡算法；
重复的 ruleName 将无法被创建

示例

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. 删除读写分离规则 #

DROP READWRITE_SPLITTING RULE ruleName [, ruleName] ...

参数说明

名称	数据类型	说明
ruleName	IDENTIFIER	规则名称

示例

DROP READWRITE_SPLITTING RULE ms_group_1;

4. 启用/禁用读写分离规则 #

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

示例

ALTER READWRITE_SPLITTING RULE group_1 ENABLE read_ds_1

5. 查看读写分离规则 #

SHOW READWRITE_SPLITTING RULES [FROM databaseName]

示例

静态读写分离规则

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)

动态读写分离规则

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)

静态读写分离规则和动态读写分离规则

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)

输出说明

列	说明
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	负载均衡算法参数

6. 查询读写分离数据源状态 #

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

示例

SHOW STATUS FROM READWRITE_SPLITTING RULES;