SpringBoot2.x+shardingsphere-jdbc5.0.0-alpha搭建读写分离

x33g5p2x  于2021-09-24 转载在 Spring  
字(3.9k)|赞(0)|评价(0)|浏览(214)

MySQL一主多从请参考:https://blog.csdn.net/MadLifeBin/article/details/119067776

SpringBoot2.x微服务通过AOP方式实现读写分离请参考:https://blog.csdn.net/MadLifeBin/article/details/119259244

版本说明

  • SpringBoot 2.3.10.RELEASE
  • shardingsphere 5.0.0-alpha

Maven依赖

<dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
        </dependency>
    </dependencies>

配置

server.port=8081

mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath:mapper/*.xml

spring.shardingsphere.datasource.common.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.common.type=com.zaxxer.hikari.HikariDataSource

spring.shardingsphere.datasource.names=write-ds,read-ds-0,read-ds-1

spring.shardingsphere.datasource.write-ds.jdbc-url=jdbc:mysql://192.168.1.8:3306/test_for_repl?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.write-ds.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.write-ds.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.write-ds.username=root
spring.shardingsphere.datasource.write-ds.password=Mysql57*

spring.shardingsphere.datasource.read-ds-0.jdbc-url=jdbc:mysql://192.168.1.9:3306/test_for_repl?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.read-ds-0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-0.driver-class-name=com.mysql.cj.jdbc.Driver
# 最好使用有库权限的只读账户
spring.shardingsphere.datasource.read-ds-0.username=root
spring.shardingsphere.datasource.read-ds-0.password=Mysql57*

spring.shardingsphere.datasource.read-ds-1.jdbc-url=jdbc:mysql://192.168.1.10:3306/test_for_repl?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.read-ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.read-ds-1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.read-ds-1.username=root
spring.shardingsphere.datasource.read-ds-1.password=Mysql57*

# 负载均衡算法类型
# ROUND_ROBIN 轮询
# RANDOM 随机
spring.shardingsphere.rules.replica-query.load-balancers.round-robin.type=ROUND_ROBIN
# 写数据源名称
spring.shardingsphere.rules.replica-query.data-sources.pr_ds.primary-data-source-name=write-ds
# 读数据源名称,多个从数据源用逗号分隔
spring.shardingsphere.rules.replica-query.data-sources.pr_ds.replica-data-source-names=read-ds-0,read-ds-1
spring.shardingsphere.rules.replica-query.data-sources.pr_ds.load-balancer-name=round-robin
# 必须的
spring.shardingsphere.rules.replica-query.load-balancers.round-robin.props.x=x

# 打印sql
spring.shardingsphere.props.sql-show=true

效果展示

坑点

ShardingShpere官网配置文档(中英)有误,不要参考。笔者为此填坑头发掉一地。请参考properties配置文件。

中文文档地址:https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/spring-boot-starter/readwrite-splitting/
英文文档地址:https://shardingsphere.apache.org/document/current/en/user-manual/shardingsphere-jdbc/configuration/spring-boot-starter/readwrite-splitting/

代码参考地址:https://gitee.com/pengld-demo/read-write-separate-demo

相关文章

最新文章

更多