一、简介
Sharding-JDBC通过sql语句语义分析,当sql语句有insert、update、delete时,Sharding-JDBC就把这次操作在主数据库上执行;当sql语句有select时,就会把这次操作在从数据库上执行,从而实现读写分离过程。但Sharding-JDBC并不会做数据同步,数据同步是配置MySQL后由MySQL自己完成的。
二、主从mysql环境准备
主服务器Master01(192.168.56.20)、从服务器Slave01(192.168.56.21):
我们这次同步的策略是同步user_db库。
首先我们在user_db创建一张表:
create table t_user( `user_id` bigint(20) primary key, `username` varchar(100) not null, `ustatus` varchar(50) not null )
三、使用yml配置读写分离策略
spring: shardingsphere: # 数据源配置 datasource: # 数据源名称,多数据源以逗号分隔,名称可以随意起名 names: master,slave master: #配置第一个数据源 driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource password: xxxxxx username: dhapp url: jdbc:mysql://192.168.56.20:3306/user_db?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai slave: #配置第一个数据源 driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource password: xxxxxx username: dhapp url: jdbc:mysql://192.168.56.21:3306/user_db?allowPublicKeyRetrieval=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai # 规则配置 rules: readwrite-splitting: data-sources: ds0: #主库从库逻辑数据源定义 ds0 为 user_db write-data-source-name: master read-data-source-names: slave load-balancer-name: round-robin load-balancers: round-robin: # 负载均衡算法 type: ROUND_ROBIN # 轮询 # 属性配置 props: # 展示修改以后的sql语句 sql-show: true
四、使用java测试代码进行测试
1、UserController
package com.sharding.demo.web; import com.sharding.demo.model.User; import com.sharding.demo.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RequestMapping("user") @RestController public class UserController { @Autowired private UserService userService; @RequestMapping(value = "addUser") public void addUser(){ for (int i = 0; i < 10; i++) { User user = new User(); user.setUserId(Long.valueOf(i)); user.setUsername("java"+i); user.setUstatus(i%2==0?String.valueOf(1):String.valueOf(0)); userService.addUser(user); } } @RequestMapping(value = "queryUserByParams") public List<User> queryUserByParams(){ return userService.queryUserByParams(); } }
2、UserService
package com.sharding.demo.service; import com.sharding.demo.model.User; import java.util.List; public interface UserService { void addUser(User user); List<User> queryUserByParams(); }
3、UserServiceImpl
package com.sharding.demo.service.impl; import com.sharding.demo.mapper.UserMapper; import com.sharding.demo.model.User; import com.sharding.demo.service.UserService; import lombok.extern.slf4j.Slf4j; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.util.List; @Service public class UserServiceImpl implements UserService { @Resource private UserMapper userMapper; @Override public void addUser(User user) { userMapper.insert(user); } @Override public List<User> queryUserByParams() { return userMapper.selectList(null); } }
4、UserMapper
package com.sharding.demo.mapper; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import com.sharding.demo.model.User; public interface UserMapper extends BaseMapper<User> { }
五、通过浏览器请求开始测试
1、http://localhost:8080/user/addUser新增结果测试
可以看到新增使用的是master数据库:
2、http://localhost:8080/user/queryUserByParams 查询结果测试
可以通过日志发现查询使用的是slave服务器