下面我将详细讲解如何使用SpringBoot整合Sharding-JDBC实现MySQL8读写分离的过程,包括环境搭建、配置文件编写、代码实现和示例说明等:
一、环境搭建
-
使用MySQL8搭建读写分离环境并创建两个数据库:
sharding_db_0
和sharding_db_1
,分别对应写库和读库。 -
在maven中引入Sharding-JDBC和相关依赖:
xml
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>com.mysql.cj</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
二、配置文件编写
-
在
application.yml
中配置数据源和Sharding-JDBC:
yaml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/
username: root
password: root
shardingsphere:
datasource:
primary:
url: jdbc:mysql://localhost:3306/sharding_db_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root
replica:
url: jdbc:mysql://localhost:3306/sharding_db_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root
# 配置数据源的负载均衡
props:
sql:
show: true
# 配置分片规则
sharding:
tables:
user:
actual-data-nodes: primary.user,replica.user
key-generator:
column: id
type: SNOWFLAKE
database-strategy:
inline:
algorithm-expression: primary
# 配置读写分离规则
# 主库路由规则
master-slave-rules:
master-data-source-name: primary
slave-data-source-names: replica
load-balance-algorithm-type: ROUND_ROBIN
# 是否开启只读功能
read-only: true -
配置Sharding-JDBC属性文件
sharding-jdbc.yml
:
```yaml
# 配置一个Datasource,这里的Datasource使用的就是ShardingSphere-JDBC内置的单库数据源DBCP2
dataSources:
primary:
url: jdbc:mysql://localhost:3306/sharding_db_0?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root
# 设置限制连接数最大值和闲置时间,尤其在高并发场景下,这个配置项非常重要,恰当的设置可以减速对数据库的冲击,避免系统瘫痪。
maxPoolSize: 50
minPoolSize: 25
idleTimeout: 6000
replica:
url: jdbc:mysql://localhost:3306/sharding_db_1?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root
# 配置默认规则
rules:
# 配置分片规则- !SHARDING
tables:
user:
# 实际数据节点
actualDataNodes: primary.user, replica.user
# 数据分片策略
databaseStrategy:
inline:
# 表示使用id字段mod 2的结果作为路由的数据
shardingColumn: id
# 配置具体的Sharding策略,如果是单分片键,可以使用StandardShardingStrategy
algorithmExpression: primary
# 主键生成策略
keyGenerator:
column: id
type: SNOWFLAKE
# 配置读写分离规则
masterSlaveRules:
# 主库名称,对应sharding-jdbc.yml中关联的数据源名称
- name: primary
# 从库名称,对应sharding-jdbc.yml中关联的数据源名称
slaveDataSourceNames:
- replica
# 负载均衡策略
loadBalanceAlgorithmType: ROUND_ROBIN
```
- !SHARDING
三、代码实现
-
创建
User
实体类,用于映射user
表:
```java
public class User {
private Long id;
private String username;
private String password;// 省略getters、setters、构造函数
}
``` -
创建
UserMapper
接口和UserMapper.xml
文件,用于执行CRUD操作:
java
public interface UserMapper {
int save(User user);
List<User> findAll();
}
xml
<mapper namespace="com.example.demo.mapper.UserMapper">
<insert id="save" parameterType="com.example.demo.entity.User">
insert into user (id, username, password) values (#{id}, #{username}, #{password})
</insert>
<select id="findAll" resultType="com.example.demo.entity.User">
select * from user
</select>
</mapper> -
创建
UserController
类,用于测试读写分离:
```java
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserMapper userMapper;@PostMapping("/save")
public String save(@RequestBody User user) {
userMapper.save(user);
return "success";
}@GetMapping("/findAll")
public ListfindAll() {
return userMapper.findAll();
}
}
```
四、示例说明
-
插入一条记录并查看主从数据库是否都有数据:
curl
curl --location --request POST 'http://localhost:8080/user/save' \
--header 'Content-Type: application/json' \
--data-raw '{
"id": 1,
"username": "test",
"password": "123456"
}'
查询从库:
mysql
mysql> select * from sharding_db_1.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | test | 123456 |
+----+----------+----------+
查询主库:
mysql
mysql> select * from sharding_db_0.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | test | 123456 |
+----+----------+----------+ -
查询记录时查看是否自动路由到从库:
curl
curl --location --request GET 'http://localhost:8080/user/findAll'
查看控制台日志输出,可以看到读请求已经使用了从库:
log
o.a.s.c.s.p.ShardingSphereMasterSlaveDataSource - Current datasource is [replica], SQL: select * from user
到此,SpringBoot整合Sharding-JDBC实现MySQL8读写分离的完整攻略就讲解完毕了。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:SpringBoot整合Sharding-JDBC实现MySQL8读写分离 - Python技术站