介绍:https://github.com/apache/shardingsphere
数据库:主库(写)+2从库(读)
db_write(写数据库master)
db_read1(读数据库slave1)
db_read2(读数据库slave2)
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.zsk</groupId>
<artifactId>springboot-read-write-separate</artifactId>
<version>0.0.1-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.8.RELEASE</version>
<relativePath />
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0.M1</version>
</dependency>
<!-- 添加Swagger2依赖,用于生成接口文档 -->
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.7.0</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.7.0</version>
</dependency>
<!--end -->
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
application.yml
server:
port: 8080
spring:
jpa:
show-sql: false
properties:
hibernate:
hbm2ddl:
auto: none #update
sharding:
jdbc:
dataSource:
names: db-write,db-read1,db-read2
# 配置主库
db-write: #org.apache.tomcat.jdbc.pool.DataSource
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/db_write?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: amsuser
password: ams2018
#最大连接数
maxPoolSize: 20
db-read1: # 配置第一个从库
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/db_read1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
username: amsuser
password: ams2018
maxPoolSize: 20
db-read2: # 配置第二个从库
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/db_read2?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
username: amsuser
password: ams2018
maxPoolSize: 20
config:
masterslave: # 配置读写分离
load-balance-algorithm-type: round_robin # 配置从库选择策略random(随机)round_robin(轮循)
name: db-write-read
master-data-source-name: db-write
slave-data-source-names: db-read1,db-read2
props:
sql: # 开启SQL显示,默认值: false,注意:仅配置读写分离时不会打印日志
show: true
效果:
post http://127.0.0.1:8080/api/user 数据写入了db_write库
get http://127.0.0.1:8080/api/users 多次访问轮询读取db_read1库,db_read2库的数据
返回数据如下
[{"id":"bc2bc1ce-c278-11ea-8c98-8c1645da95ec","userName":"db_read1","password":"123456","createTime":"2020-07-10T06:43:59.000+0000"}]
[{"id":"bc2bc1ce-c278-11ea-8c98-8c1645da95ec","userName":"db_read2","password":"123456","createTime":"2020-07-10T06:43:59.000+0000"}]
完整代码demo:https://gitee.com/zhangsike/springboot-learn/tree/master/springboot-read-write-separate,注意运行项目之前先执行sql.sql
配置mysql从库监听同步主库数据