前一篇文章介绍了 MySQL 静态读写分离的配置，那么面对 Sharding+ 读写分离组合使用的场景，该如何去配置？下面内容记录了操作过程，我们通过 ShardingSphere-Proxy 和 4 个 MySQL 节点即可验证。
Table of Contents
测试目的
基于两组 MySQL 一主两从复制架构，验证 Apache ShardingSphere 的 Sharding + 静态读写分离组合能力。
预置条件
- ShardingSphere-Proxy 和 2 组 MySQL 集群（一主两从）正常运行，网络互通。
网络拓扑
拓扑图
版本说明
- ShardingSphere：5.2.1
- MySQL：8.0.28
预期结果
Sharding + 读写分离能力可组合使用。
实操过程
1. 构建集群
在 ShardingSphere-Proxy 中创建逻辑库，注册 MGR 节点，完成集群构建。
# mysql -uroot -p -h127.0.0.1 -P3307
mysql> CREATE DATABASE testdb;
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW DATABASES;
+--------------------+
| schema_name |
+--------------------+
| shardingsphere |
| information_schema |
| performance_schema |
| testdb |
| mysql |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> USE testdb;
Database changed
mysql> ADD RESOURCE ds_0 (
URL="jdbc:mysql://192.168.56.103:3306/testdb0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
USER="test",
PASSWORD="Test@123",
PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
), ds_1 (
URL="jdbc:mysql://192.168.56.103:3306/testd0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
USER="test",
PASSWORD="Test@123",
PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
), ds_2 (
URL="jdbc:mysql://192.168.56.104:3306/testdb1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
USER="test",
PASSWORD="Test@123",
PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
), ds_3 (
URL="jdbc:mysql://192.168.56.104:3306/testdb1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true",
USER="test",
PASSWORD="Test@123",
PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);
Query OK, 0 rows affected (1.06 sec)
mysql> SHOW DATABASE RESOURCES;
+------+-------+----------------+------+---------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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_3 | MySQL | 192.168.56.104 | 3306 | testdb1 | 30000 | 30000 | 2100000 | 10 | 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,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
| ds_2 | MySQL | 192.168.56.104 | 3306 | testdb1 | 30000 | 30000 | 2100000 | 10 | 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,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
| ds_1 | MySQL | 192.168.56.103 | 3306 | testdb0 | 30000 | 30000 | 2100000 | 10 | 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,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
| ds_0 | MySQL | 192.168.56.103 | 3306 | testdb0 | 30000 | 30000 | 2100000 | 10 | 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,"registerMbeans":false,"allowPoolSuspension":false,"autoCommit":true,"isolateInternalQueries":false} |
+------+-------+----------------+------+---------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.01 sec)
2. 创建静态读写分离规则
本用例包含两个 MySQL 主从集群，因此读写分离规则也应配置为两个。
mysql> CREATE READWRITE_SPLITTING RULE static_rwp_rule1 (
WRITE_RESOURCE=ds_0,
READ_RESOURCES(ds_1)
);
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE READWRITE_SPLITTING RULE static_rwp_rule2 (
WRITE_RESOURCE=ds_2,
READ_RESOURCES(ds_3)
);
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW READWRITE_SPLITTING RULES\G
*************************** 1. row ***************************
name: static_rwp_rule1
auto_aware_data_source_name:
write_data_source_query_enabled:
write_data_source_name: ds_0
read_data_source_names: ds_1
load_balancer_type:
load_balancer_props:
*************************** 2. row ***************************
name: static_rwp_rule2
auto_aware_data_source_name:
write_data_source_query_enabled:
write_data_source_name: ds_2
read_data_source_names: ds_3
load_balancer_type:
load_balancer_props:
2 rows in set (0.00 sec)
3. 创建分片表
在 ShardingSphere-Proxy 中创建分片规则和分片表，注意规则名称和表名需要保持一致，RESOURCES 中需填写上一步所创建的两个读写分离规则。
mysql> CREATE SHARDING TABLE RULE t_user(
RESOURCES(static_rwp_rule1,static_rwp_rule2),
SHARDING_COLUMN=user_id,
TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="4"))
);
Query OK, 0 rows affected (0.11 sec)
mysql> SHOW SHARDING TABLE RULE t_user\G
*************************** 1. row ***************************
table: t_user
actual_data_nodes:
actual_data_sources: static_rwp_rule1,static_rwp_rule2
database_strategy_type:
database_sharding_column:
database_sharding_algorithm_type:
database_sharding_algorithm_props:
table_strategy_type: STANDARD
table_sharding_column: user_id
table_sharding_algorithm_type: hash_mod
table_sharding_algorithm_props: sharding-count=4
key_generate_column:
key_generator_type:
key_generator_props:
auditor_types:
allow_hint_disable:
1 row in set (0.00 sec)
mysql> CREATE TABLE `t_user` (
`user_id` int NOT NULL,
`order_id` int NOT NULL,
`status` varchar(45) DEFAULT NULL,
PRIMARY KEY (`user_id`)
);
Query OK, 0 rows affected (0.66 sec)
mysql> INSERT INTO t_user VALUES
(1,1,'active'),
(2,2,'active'),
(3,3,'active'),
(4,4,'active');
Query OK, 4 rows affected (0.22 sec)
mysql> SELECT * FROM t_user ORDER BY user_id;
+---------+----------+--------+
| user_id | order_id | status |
+---------+----------+--------+
| 1 | 1 | active |
| 2 | 2 | active |
| 3 | 3 | active |
| 4 | 4 | active |
+---------+----------+--------+
4 rows in set (0.05 sec)
4. 验证读写分离配置
在 ShardingSphere-Proxy 中，可在 SQL 前添加 PREVIEW 关键字来确认语句路由情况。
mysql> PREVIEW SELECT * FROM t_user ORDER BY user_id;
+------------------+-----------------------------------------+
| data_source_name | actual_sql |
+------------------+-----------------------------------------+
| ds_1 | SELECT * FROM t_user_0 ORDER BY user_id |
| ds_1 | SELECT * FROM t_user_2 ORDER BY user_id |
| ds_3 | SELECT * FROM t_user_1 ORDER BY user_id |
| ds_3 | SELECT * FROM t_user_3 ORDER BY user_id |
+------------------+-----------------------------------------+
4 rows in set (0.05 sec)
通过以上输出信息确认，全表检索通过 ds_1 和 ds_3 来完成，均为主从集群的备节点，与预期一致。下面通过一条带有 WHERE 条件的 SQL 确认。
mysql> PREVIEW SELECT * FROM t_user WHERE user_id=1;
+------------------+----------------------------------------+
| data_source_name | actual_sql |
+------------------+----------------------------------------+
| ds_3 | SELECT * FROM t_user_1 WHERE user_id=1 |
+------------------+----------------------------------------+
1 row in set (0.01 sec)
mysql> PREVIEW SELECT * FROM t_user WHERE user_id=2;
+------------------+----------------------------------------+
| data_source_name | actual_sql |
+------------------+----------------------------------------+
| ds_1 | SELECT * FROM t_user_2 WHERE user_id=2 |
+------------------+----------------------------------------+
1 row in set (0.04 sec)
通过以上输出信息确认，带有 WHERE 条件的查询也都是通过备节点 ds_1 和 ds_3 来完成检索，与预期一致。
最后，确认 INSERT 语句的路由。
mysql> PREVIEW INSERT INTO t_user VALUES(5,5,'active');
+------------------+---------------------------------------------+
| data_source_name | actual_sql |
+------------------+---------------------------------------------+
| ds_2 | INSERT INTO t_user_1 VALUES(5, 5, 'active') |
+------------------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> PREVIEW INSERT INTO t_user VALUES(6,6,'active');
+------------------+---------------------------------------------+
| data_source_name | actual_sql |
+------------------+---------------------------------------------+
| ds_0 | INSERT INTO t_user_2 VALUES(6, 6, 'active') |
+------------------+---------------------------------------------+
1 row in set (0.01 sec)
mysql> PREVIEW INSERT INTO t_user VALUES(7,7,'active');
+------------------+---------------------------------------------+
| data_source_name | actual_sql |
+------------------+---------------------------------------------+
| ds_2 | INSERT INTO t_user_3 VALUES(7, 7, 'active') |
+------------------+---------------------------------------------+
1 row in set (0.01 sec)
INSERT 操作均由主节点 ds_0 和 ds_2 完成，与预期一致，测试结束。
总结
在 Sharding 和读写分离组合使用的配置中，务必要先创建读写分离的规则，再去配置 Sharding。因为 Sharding 是需要基于读写分离去完成分片配置，两者顺序不可颠倒。
最后修改时间：2022-11-15 18:00:03
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
