实践篇-混合部署架构搭建

# 一、前言

此处会采用ShardingSphere‐JDBC 和ShardingSphere‐Proxy混合部署，使用Zookeeper做统一注册中心，以下是三种部署模式的说明

# 1.1、ShardingSphere-JDBC 部署

ShardingSphere‐JDBC 定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动

# 1.2、ShardingSphere-Proxy 部署

ShardingSphere‐Proxy 定位为透明化的数据库代理端，通过实现数据库二进制协议，对异构语言提供支持。目前提供 MySQL 和 PostgreSQL 协议，透明化数据库操作，对 DBA 更加友好

# 1.3、混合部署架构

ShardingSphere‐JDBC 采用无中心化架构，与应用程序共享资源，适用于 Java 开发的高性能的轻量级OLTP 应用；

ShardingSphere‐Proxy 提供静态入口以及异构语言的支持，独立于应用程序部署，适用于OLAP 应用以及对分片数据库进行管理和运维的场景

# 二、环境准备

此处用于验证，安装MySQL和Zookeeper，生产环境请自行部署

# 2.1、MySQL准备

用来验证分库分表相关功能

DROP DATABASE IF EXISTS migration_ds_0;
CREATE DATABASE migration_ds_0 DEFAULT CHARSET utf8;

USE migration_ds_0;

CREATE TABLE t_order (order_id INT NOT NULL, user_id INT NOT NULL, status VARCHAR(45) NULL, PRIMARY KEY (order_id));

INSERT INTO t_order (order_id, user_id, status) VALUES (1,2,'ok'),(2,4,'ok'),(3,6,'ok'),(4,1,'ok'),(5,3,'ok'),(6,5,'ok');

1
2
3
4
5
6
7
8

# 2.2、Zookeeper准备

用来验证治理中心功能，用于集中存放ShardingSphere相关配置

# 三、Shardingsphere Proxy部署搭建

# 3.1、修改配置

修改shardingsphere-->proxy-->bootstrap下的server.yaml配置文件，增加如下配置

#开启SQL日志
props:
  sql-show: true
  #解决DBeaver连接时Unknown system variable 'query_cache_size'问题
  proxy-mysql-default-version: 8.0.22

#运行模式
mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
      
rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: ALL_PERMITTED
  - !TRANSACTION
    defaultType: XA
    providerType: Atomikos
  - !SQL_PARSER
    sqlCommentParseEnabled: true
    sqlStatementCache:
      initialCapacity: 2000
      maximumSize: 65535
    parseTreeCache:
      initialCapacity: 128
      maximumSize: 1024

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

修改 config-sharding.yaml，增加如下配置，此时sharding_db是我们的逻辑库名称

databaseName: sharding_db

# 3.2、启动服务

org.apache.zookeeper.server.quorum.MultipleAddresses
[INFO ] 2023-06-14 20:35:36.751 [main] o.a.s.d.p.c.e.PipelineJobWorker - start worker initialization
[INFO ] 2023-06-14 20:35:36.995 [main] o.a.s.d.p.c.e.PipelineJobWorker - worker initialization done
[INFO ] 2023-06-14 20:35:37.590 [main] o.a.s.p.frontend.ShardingSphereProxy - ShardingSphere-Proxy Cluster mode started successfully

1
2
3
4

出现上述日志，表示集群模式启动成功

# 3.3、连接验证

# 3.4、配置资源

数据源配置

show databases;
-- 切换到我们的逻辑库
use sharding_db;

-- 查看有数据库资源
SHOW DATABASE RESOURCES;

-- 初始化资源，此处做测试，我们只初始化了两个数据源
ADD RESOURCE ds_0 (
    URL="jdbc:mysql://mysql:3306/migration_ds_0?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useSSL=false&allowMultiQueries=true",
    USER="root",
    PASSWORD="*********",
    PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
),ds_1 (
    URL="jdbc:mysql://mysql:3306/migration_ds_1?useUnicode=true&characterEncoding=UTF-8&rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useSSL=false&allowMultiQueries=true",
    USER="root",
    PASSWORD="*********",
    PROPERTIES("minPoolSize"="1","maxPoolSize"="20","idleTimeout"="60000")
);

-- 删除数据源忽略单表
DROP RESOURCE ds_0,ds_1 ignore single tables;

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

广播表配置

-- 查询广播表
SHOW SHARDING BROADCAST TABLE RULES;
-- 创建广播表
CREATE SHARDING BROADCAST TABLE RULES (t_order0,t_order2);
-- 修改广播表
ALTER SHARDING BROADCAST TABLE RULES (t_order0,t_order2,t_order3,t_order4);
-- 删除某个广播表
DROP SHARDING BROADCAST TABLE RULES t_order2;

1
2
3
4
5
6
7
8

强制路由

-- 通过租户id强制路由
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=44 */
select * from t_order;

-- 查看路由信息
preview select * from t_order /* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=ds_0 */

-- 禁用审计
/* SHARDINGSPHERE_HINT: DISABLE_AUDIT_NAMES=sharding_key_required_auditor */ 
select * from t_order 
/* SHARDINGSPHERE_HINT: SHARDING_DATABASE_VALUE=1 */

-- 生成路由预览
PREVIEW /* SHARDINGSPHERE_HINT: t_order.SHARDING_DATABASE_VALUE=1, t_order.SHARDING_TABLE_VALUE=1 */ SELECT * FROM t_order;

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

# 四、Sharingsphere JDBC部署搭建

# 4.1、引入依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
    <version>5.2.1</version>
</dependency>

1
2
3
4
5

# 4.2、增加配置

# 五、常见问题

# 5.1、Can not find transaction manager of `XA`

### Error querying database.  Cause: java.lang.NullPointerException: Can not find transaction manager of `XA`
### The error may exist in com/demo/mapper/CityMapper.java (best guess)
### The error may involve com.demo.mapper.CityMapper.findByState
### The error occurred while executing a query
### Cause: java.lang.NullPointerException: Can not find transaction manager of `XA`] with root cause

java.lang.NullPointerException: Can not find transaction manager of `XA`
	at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:987) ~[guava-30.0-jre.jar:na]

1
2
3
4
5
6
7
8

问题原因：Shardingsphere默认的是XA事务

解决方法：

<!--XA事务必须配置如下，否则如下报错-->
<!--Caused by: java.lang.NullPointerException: Cannot find transaction manager of [XA]-->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-transaction-xa-core</artifactId>
    <version>${shardingsphere.version}</version>
</dependency>

1
2
3
4
5
6
7

# 5.2、Shardingshpere 5.2.1 依赖问题，启动报错

2023-05-09 13:11:35.822  WARN 14692 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'shardingSphereDataSource' defined in class path resource [org/apache/shardingsphere/spring/boot/ShardingSphereAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'shardingSphereDataSource' threw exception; nested exception is java.lang.NoSuchMethodError: org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.setCodePointLimit(I)V

解决方法：

<!--解决依赖问题 ： nested exception is java.lang.NoSuchMethodError: org.apache.shardingsphere.infra.util.yaml.constructor.ShardingSphereYamlConstructor$1.setCodePointLimit(I)V
-->
<dependency>
    <groupId>org.yaml</groupId>
    <artifactId>snakeyaml</artifactId>
    <version>1.33</version>
</dependency>

1
2
3
4
5
6
7

# 5.3、引入Nacos做持久化，启动耗时长的问题

#模式配置
#mode:
#  type: Cluster
#  repository:
#    type: Nacos
#    props:
#      namespace: bebd94d5-0a33-4ef9-8960-78e9c29861bb
#      server-lists: nacos.dev-qinsilk.svc.cluster.local:8848
#      retryIntervalMilliseconds: 500
#      timeToLiveSeconds: 60
#      maxRetries: 3
#      operationTimeoutMilliseconds: 500

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

Nacos持久化问题，TableMetaDataPersistService持久化所有table

 @Override
    public void persist(final String databaseName, final String schemaName, final Map<String, ShardingSphereTable> tables) {
        tables.forEach((key, value) -> repository.persist(DatabaseMetaDataNode.getTableMetaDataPath(databaseName, schemaName, key.toLowerCase()),
                YamlEngine.marshal(new YamlTableSwapper().swapToYamlConfiguration(value))));
    }

1
2
3
4
5
6

nacos-client 持久化 254s，zookeeper持久化 0.6s

一层层跟进方法的调用耗时

org.apache.shardingsphere.mode.repository.cluster.nacos.NacosRepository:persist()

org.apache.shardingsphere.mode.repository.cluster.nacos.NacosRepository:put()

org.apache.shardingsphere.mode.repository.cluster.nacos.NacosRepository:waitValue()

org.apache.shardingsphere.mode.repository.cluster.nacos.NacosRepository waitValue

@SneakyThrows(InterruptedException.class)
private void waitValue(final Collection<KeyValue> keyValues) throws NacosException {
    if (!isAvailable(keyValues)) {
        long retryIntervalMilliseconds = nacosProps.getValue(NacosPropertyKey.RETRY_INTERVAL_MILLISECONDS);
        int maxRetries = nacosProps.getValue(NacosPropertyKey.MAX_RETRIES);
        for (int retry = 0; retry < maxRetries; retry++) {
            Thread.sleep(getSleepTimeMs(retry, retryIntervalMilliseconds));
            if (isAvailable(keyValues)) {
                return;
            }
        }
        throw new NacosException(NacosException.RESOURCE_NOT_FOUND, "Wait value availability timeout exceeded");
    }
}

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

最后发现，在NacosRepository waitValue方法中，每一次持久化表元数据，都会休眠500ms，当数据库几百张表时，启动时间会非常长，这么坑，这么坑，只能放弃Nacos做持久化

# 5.4、DBeaver连接proxy时，出现如下报错，SQL 错误 [1193] [HY000]: Unknown system variable 'query_cache_size'

问题原因：数据库版本问题

解决方式：

在proxy端增加如下参数即可：proxy-mysql-default-version: 8.0.22

#开启SQL日志
props:
  sql-show: true
  proxy-mysql-default-version: 8.0.22

1
2
3
4

# 5.5、Proxy Windows 环境下，运行 ShardingSphere-Proxy，找不到或无法加载主类org.apache.shardingsphere.proxy.Bootstrap，如何解决?

问题原因:

如果你是通过解压方式启动proxy的，需要注意一下某些解压缩工具在解压 ShardingSphere‐Proxy 二进制包时可能将文件名截断，导致找不到某些类。

解决方法:

打开 cmd.exe 并执行下面的命令

tar zxvf apache-shardingsphere-${RELEASE.VERSION}-shardingsphere-proxy-bin.tar.gz

# 六、其它

ShardingSphere-Proxy 默认不支持 hint，如需支持，请在 conf/server.yaml 中，将 properties 的属性 proxy-hint-enabled 设置为 true

参考：

ShardingSphere-Proxy 部署和启动 (opens new window)、Shardingsphere DistSQL配置 (opens new window)

#Shardingsphere

← 实践篇-快速开始实践篇-数据迁移→

实践篇-混合部署架构搭建

# 一、前言

# 1.1、ShardingSphere-JDBC 部署

# 1.2、ShardingSphere-Proxy 部署

# 1.3、混合部署架构

# 二、环境准备

# 2.1、MySQL准备

# 2.2、Zookeeper准备

# 三、Shardingsphere Proxy部署搭建

# 3.1、修改配置

# 3.2、启动服务

# 3.3、连接验证

# 3.4、配置资源

# 四、Sharingsphere JDBC部署搭建

# 4.1、引入依赖

# 4.2、增加配置

# 五、常见问题

# 5.1、Can not find transaction manager of XA

# 5.2、Shardingshpere 5.2.1 依赖问题，启动报错

# 5.3、引入Nacos做持久化，启动耗时长的问题

# 5.4、DBeaver连接proxy时，出现如下报错，SQL 错误 [1193] [HY000]: Unknown system variable 'query_cache_size'

# 5.5、Proxy Windows 环境下，运行 ShardingSphere-Proxy，找不到或无法加载主类org.apache.shardingsphere.proxy.Bootstrap，如何解决?

# 六、其它

# 5.1、Can not find transaction manager of `XA`