• MySQL Sharding + 读写分离配置说明

    前一篇文章介绍了 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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55

    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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30

    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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52

    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)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    通过以上输出信息确认，全表检索通过 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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    通过以上输出信息确认，带有 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)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    INSERT 操作均由主节点 ds_0 和 ds_2 完成，与预期一致，测试结束。

    总结

    在 Sharding 和读写分离组合使用的配置中，务必要先创建读写分离的规则，再去配置 Sharding。因为 Sharding 是需要基于读写分离去完成分片配置，两者顺序不可颠倒。


  • 相关阅读:
    rk3588 大小核启动
    MyBatis-核心配置文件mybatis-config.xml主要参数详解
    npx 有什么作用跟意义？为什么要有 npx？什么场景使用？
    【深度学习 | Transformer】释放注意力的力量:探索深度学习中的 变形金刚，一文带你读通各个模块 —— Positional Encoding（一）
    Cesium源码解析一（terrain文件的加载过程）
    【数据结构】二叉树 二叉树的深度优先遍历和广度优先遍历 完全二叉树和满二叉树的性质 二叉树的节点个数以及叶子节点个数
    数据类型优化
    算法笔记 图论和优先级队列的笔记
    pycharm运行R语言脚本（win10环境下安装）
    工作小计-GPU硬编以及依赖库 nvcuvid&nvidia-encode
  • 原文地址：https://blog.csdn.net/daiyejava/article/details/127857583