MySQL Sharding + 读写分离配置说明

已于 2022-11-15 18:01:21 修改
阅读量1.0k 收藏 1
点赞数
分类专栏： ShardingSphere 文章标签： mysql ShardingSphere MySQL读写分离 分布式数据库 分库分表
于 2022-11-14 22:59:35 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/daiyejava/article/details/127857583
版权
本文档详细介绍了如何在ShardingSphere-Proxy中配置Sharding+静态读写分离，以实现MySQL数据库的高可用和数据分片。通过创建逻辑库、资源、读写分离规则和分片表，最终验证了读写操作在不同节点的正确路由，确保了读写分离和分片的组合使用。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

前一篇文章介绍了 MySQL 静态读写分离的配置，那么面对 Sharding+ 读写分离组合使用的场景，该如何去配置？下面内容记录了操作过程，我们通过 ShardingSphere-Proxy 和 4 个 MySQL 节点即可验证。

文章目录

测试目的

基于两组 MySQL 一主两从复制架构，验证 Apache ShardingSphere 的 Sharding + 静态读写分离组合能力。

预置条件

  • ShardingSphere-Proxy 和 2 组 MySQL 集群（一主两从）正常运行，网络互通。

网络拓扑

拓扑图

开源图Sharding读写分离.png

版本说明

  • 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/testdb0?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 是需要基于读写分离去完成分片配置，两者顺序不可颠倒。


评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值