MySQL 读写分离，可以这样搞

原创代野（Tank） 2022-11-13

708

对于读写分离的概念，大家一定不会陌生。除了分库分表，Apache ShardingSphere 还有非常实用的读写分离能力，非常适用于读比例较高的业务场景中，可显著地提升系统吞吐上限。

读写分离模式：
- 静态读写分离：将主从节点 IP 信息注册到 ShardingSphere 中，ShardingSphere 不关注数据库节点状态变化，适用于一主一备，或使用了 VIP 的场景。
- 动态读写分离：ShardingSphere 可自动感知数据库节点角色变化，数据库节点 failover 对业务透明，目前支持 MGR 架构。
从库的负载均衡策略：支持随机、轮询和权重策略。

下面通过一个「静态」读写分离的测试用例，进一步了解 Apache ShardingSphere 的读写分离能力。详细配置请参考官方文档读写分离配置章节。

测试目的
预置条件
网络拓扑
预期结果
实操过程

1. 构建集群
2. 创建静态读写分离规则
3. 验证读写分离配置

3.1 通过 ShardingSphere-Prxoy 确认
3.2 通过 MySQL general log 确认

总结

测试目的

基于 MySQL 一主两从复制架构，验证 Apache ShardingSphere 对数据库的静态读写分离能力。

预置条件

ShardingSphere-Proxy 和 MySQL 集群（一主两从）正常运行，网络互通；
MGR 集群中提前建好库，并准备测试表和若干测试记录用于测试；
MySQL 开启 general log，便于通过日志信息确认语句真实执行情况。

网络拓扑

拓扑图：

2PPT 插图素材读写分离.png

版本说明：

ShardingSphere：5.2.1
MySQL：8.0.28

预期结果

ShardingSphere 具有读写分离能力，可在从库日志确认 SELECT 语句；
多个读库节点具有负载均衡能力；

实操过程

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/test?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123",
     PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
), ds_1 (
     URL="jdbc:mysql://192.168.56.104:3306/test?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123",
     PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
), ds_2 (
     URL="jdbc:mysql://192.168.56.105:3306/test?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123",
     PROPERTIES("maximumPoolSize"="10","idleTimeout"="30000")
);
Query OK, 0 rows affected (1.68 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_2 | MySQL | 192.168.56.105 | 3306 | test | 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.104 | 3306 | test | 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 | test | 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} |
+------+-------+----------------+------+------+---------------------------------+---------------------------+---------------------------+---------------+---------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.32 sec)

2. 创建静态读写分离规则

配置静态读写分离的规则相对简单，只需要在 WRITE_RESOURCE 和 READ_RESOURCES 两个参数中填入对应的读和写的数据源即可，TYPE 对应的是负载均衡算法，这里配置为 random。

mysql> CREATE READWRITE_SPLITTING RULE static_rwp_rule (
WRITE_RESOURCE=ds_0,
READ_RESOURCES(ds_1,ds_2),
TYPE(NAME="random")
);
Query OK, 0 rows affected (2.87 sec)

mysql> SHOW READWRITE_SPLITTING RULES\G
*************************** 1. row ***************************
                           name: static_rwp_rule
    auto_aware_data_source_name:
write_data_source_query_enabled:
         write_data_source_name: ds_0
         read_data_source_names: ds_1,ds_2
             load_balancer_type: random
            load_balancer_props:
1 row in set (0.00 sec)

到这里已经完成了静态读写分离策略的配置，下面进入到验证配置的环节。

3. 验证读写分离配置

有多种方法可以确认 SQL 路由，这里采用通过 ShardingSphere 以及 MySQL general log 方式来确认。

3.1 通过 ShardingSphere-Prxoy 确认

在 ShardingSphere-Prxoy 中，在查询语句前加 PREVIEW 命令即可查看执行路由。这种验证方式，SQL 不会在数据库中真实执行。

mysql> SHOW TABLES;
+------------------+------------+
| Tables_in_testdb | Table_type |
+------------------+------------+
| t1               | BASE TABLE |
+------------------+------------+
1 row in set (0.00 sec)

mysql> PREVIEW INSERT INTO t1 VALUES(2,'DaiYe');
+------------------+----------------------------------+
| data_source_name | actual_sql                       |
+------------------+----------------------------------+
| ds_0             | INSERT INTO t1 VALUES(2,'DaiYe') |
+------------------+----------------------------------+
1 row in set (0.07 sec)

mysql> PREVIEW SELECT * FROM t1;
+------------------+------------------+
| data_source_name | actual_sql       |
+------------------+------------------+
| ds_2             | SELECT * FROM t1 |
+------------------+------------------+
1 row in set (0.08 sec)

从以上输出信息来看，INSERT 请求由 ds_0 处理，SELECT 请求由 ds_2 处理。在负载均衡策略中，上面已经配置了 random 策略，下面进行验证。

mysql> PREVIEW SELECT * FROM t1;
+------------------+------------------+
| data_source_name | actual_sql       |
+------------------+------------------+
| ds_2             | SELECT * FROM t1 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> PREVIEW SELECT * FROM t1;
+------------------+------------------+
| data_source_name | actual_sql       |
+------------------+------------------+
| ds_2             | SELECT * FROM t1 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> PREVIEW SELECT * FROM t1;
+------------------+------------------+
| data_source_name | actual_sql       |
+------------------+------------------+
| ds_1             | SELECT * FROM t1 |
+------------------+------------------+
1 row in set (0.01 sec)

mysql> PREVIEW SELECT * FROM t1;
+------------------+------------------+
| data_source_name | actual_sql       |
+------------------+------------------+
| ds_1             | SELECT * FROM t1 |
+------------------+------------------+
1 row in set (0.01 sec)

mysql> PREVIEW SELECT * FROM t1;
+------------------+------------------+
| data_source_name | actual_sql       |
+------------------+------------------+
| ds_2             | SELECT * FROM t1 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> PREVIEW SELECT * FROM t1;
+------------------+------------------+
| data_source_name | actual_sql       |
+------------------+------------------+
| ds_1             | SELECT * FROM t1 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> PREVIEW SELECT * FROM t1;
+------------------+------------------+
| data_source_name | actual_sql       |
+------------------+------------------+
| ds_2             | SELECT * FROM t1 |
+------------------+------------------+
1 row in set (0.00 sec)

mysql> PREVIEW SELECT * FROM t1;
+------------------+------------------+
| data_source_name | actual_sql       |
+------------------+------------------+
| ds_2             | SELECT * FROM t1 |
+------------------+------------------+
1 row in set (0.00 sec)

3.2 通过 MySQL general log 确认

这种方式适合在测试环境进行验证，期间会产生大量日志信息，验证后务必及时关闭该功能。

-- 查看 general log 状态和日志位置
SHOW VARIABLES LIKE '%general_log%';

-- 开启
SET GLOBAL general_log = on;

-- 关闭
SET GLOBAL general_log = off;

下面在 ShardingSphere-Proxy 中执行 SQL，验证路由情况。

mysql> INSERT INTO t1 VALUES(2,'DaiYe');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;
+----+-------+
| c1 | c2    |
+----+-------+
|  1 | Tank  |
|  2 | DaiYe |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;
+----+-------+
| c1 | c2    |
+----+-------+
|  1 | Tank  |
|  2 | DaiYe |
+----+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;
+----+-------+
| c1 | c2    |
+----+-------+
|  1 | Tank  |
|  2 | DaiYe |
+----+-------+
2 rows in set (0.00 sec)

mysql>

MySQL general log 中输出信息如下。

ds_0 日志：

2022-11-13T14:30:13.784662Z	   42 Query	INSERT INTO t1 VALUES(2,'DaiYe')

ds_1 日志：

2022-11-13T14:30:18.492112Z	   42 Query	SELECT * FROM t1
2022-11-13T14:30:19.356047Z	   42 Query	SELECT * FROM t1
2022-11-13T14:30:20.321698Z	   42 Query	SELECT * FROM t1

ds_2 日志：

2022-11-13T14:30:19.368517Z	   38 Query	SELECT * FROM t1

INSERT 语句在写节点（ds_0）中完成，读节点（ds_1 和 ds_2）中完成，与预期一致，测试完成。

总结

静态读写分离配置相对简单，填写对应的数据源信息及负载均衡策略即可；
静态配置满足了读写数据源映射配置，由于固定的数据源配置不具备 failover 能力，如写数据源发生宕机，此时业务将不可写，因此结合 VIP 方案使用会更为合适。

最后修改时间：2022-11-13 22:53:46

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Apache ShardingSphere - DBA 实践（共4篇）

从 5.x 开始，Apache ShardingSphere 从分库分表中间件蜕变为以 Database Plus 为理念的分布式数据库生态系统，本合辑整理了 DBA 角度对 Apache ShardingSphere 的理解，以及对应技术实践。