spring boot:shardingsphere+druid+mysql主从复制的读写分离(分库分表)(spring boot 2.3.4)
一，如何实现mysql数据库的读写分离?
1,这个需要先实现mysql数据库的主从复制(master/slave)
请参考:
https://blog.imgtouch.com/index.php/2023/05/21/mysqlmysql-shu-ju-ku-zhu-cong-tong-bu-mysql8019/
2,说明：如果分库则需要在mysql的主从复制中添加需要复制的各个库：
例如本例中的从库：
replicate-do-db = ebusiness,saleorder01,saleorder02
说明：刘宏缔的架构森林是一个专注架构的博客，
网站：https://blog.imgtouch.com
本文: https://blog.imgtouch.com/index.php/2023/05/26/spring-boot-shardingsphere-druid-mysql-zhu-cong-fu-zhi-de-2/
对应的源码可以访问这里获取： https://github.com/liuhongdi/
说明：作者:刘宏缔 邮箱: 371125307@qq.com
二，演示项目的相关信息
1,项目地址:
https://github.com/liuhongdi/masterslavesharding
2,项目功能说明：
演示了带有分库与分表的sharding 读写分离
ebusiness:默认访问的非分表库
saleorder01:第一个分表库
saleorder02:第二个分表库
它们各对应一个读写分离用到的从库
3,项目结构：
4,数据库的结构：
三，配置文件说明
1,pom.xml
<!--mybatis begin--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.23</version> </dependency> <!--log4j2 begin--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-log4j2</artifactId> </dependency> <dependency> <groupId>com.lmax</groupId> <artifactId>disruptor</artifactId> <version>3.4.2</version> </dependency> <!--mysql begin--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--pagehelper begin--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.3.0</version> </dependency> <!--shardingsphere begin--> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.1.1</version> </dependency> <!--thymeleaf begin--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency>
2,application.properties
#error server.error.include-stacktrace=always #error logging.level.org.springframework.web=trace #thymeleaf spring.thymeleaf.cache=false spring.thymeleaf.encoding=UTF-8 spring.thymeleaf.mode=HTML spring.thymeleaf.prefix=classpath:/templates/ spring.thymeleaf.suffix=.html #shardingsphere names spring.shardingsphere.datasource.names=ebusinessmaster,ebusinessslave01,saleorder01,saleorder02,saleorder01slave01,saleorder02slave01 #shardingsphere ebusinessmaster spring.shardingsphere.datasource.ebusinessmaster.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ebusinessmaster.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ebusinessmaster.url=jdbc:mysql://127.0.0.1:3306/ebusiness?characterEncoding=utf-8 spring.shardingsphere.datasource.ebusinessmaster.username=root spring.shardingsphere.datasource.ebusinessmaster.password=password spring.shardingsphere.datasource.ebusinessmaster.filters=stat,wall,log4j2 spring.shardingsphere.datasource.ebusinessmaster.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 #shardingsphere ebusinessslave01 spring.shardingsphere.datasource.ebusinessslave01.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.ebusinessslave01.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.ebusinessslave01.url=jdbc:mysql://127.0.0.1:3307/ebusiness?characterEncoding=utf-8 spring.shardingsphere.datasource.ebusinessslave01.username=root spring.shardingsphere.datasource.ebusinessslave01.password=password spring.shardingsphere.datasource.ebusinessslave01.filters=stat,wall,log4j2 spring.shardingsphere.datasource.ebusinessslave01.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 #shardingsphere saleorder01master spring.shardingsphere.datasource.saleorder01.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.saleorder01.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.saleorder01.url=jdbc:mysql://127.0.0.1:3306/saleorder01?characterEncoding=utf-8 spring.shardingsphere.datasource.saleorder01.username=root spring.shardingsphere.datasource.saleorder01.password=password spring.shardingsphere.datasource.saleorder01.filters=stat,wall,log4j2 spring.shardingsphere.datasource.saleorder01.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 #shardingsphere saleorder01slave01 spring.shardingsphere.datasource.saleorder01slave01.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.saleorder01slave01.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.saleorder01slave01.url=jdbc:mysql://127.0.0.1:3307/saleorder01?characterEncoding=utf-8 spring.shardingsphere.datasource.saleorder01slave01.username=root spring.shardingsphere.datasource.saleorder01slave01.password=password spring.shardingsphere.datasource.saleorder01slave01.filters=stat,wall,log4j2 spring.shardingsphere.datasource.saleorder01slave01.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 #shardingsphere saleorder02master spring.shardingsphere.datasource.saleorder02.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.saleorder02.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.saleorder02.url=jdbc:mysql://127.0.0.1:3306/saleorder02?characterEncoding=utf-8 spring.shardingsphere.datasource.saleorder02.username=root spring.shardingsphere.datasource.saleorder02.password=password spring.shardingsphere.datasource.saleorder02.filters=stat,wall,log4j2 spring.shardingsphere.datasource.saleorder02.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 #shardingsphere saleorder02slave01 spring.shardingsphere.datasource.saleorder02slave01.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.saleorder02slave01.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.saleorder02slave01.url=jdbc:mysql://127.0.0.1:3307/saleorder02?characterEncoding=utf-8 spring.shardingsphere.datasource.saleorder02slave01.username=root spring.shardingsphere.datasource.saleorder02slave01.password=password spring.shardingsphere.datasource.saleorder02slave01.filters=stat,wall,log4j2 spring.shardingsphere.datasource.saleorder02slave01.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 #database default data source spring.shardingsphere.sharding.default-data-source-name=ebusinessmaster #database strategy spring.shardingsphere.sharding.default-database-strategy.standard.sharding-column=orderId spring.shardingsphere.sharding.default-database-strategy.standard.precise-algorithm-class-name=com.masterslavesharding.demo.algorithm.DatabasePreciseShardingAlgorithm #database table spring.shardingsphere.sharding.binding-tables=t_order spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=saleorder0$->{1..1}.t_order_$->{1..2},saleorder0$->{2..2}.t_order_$->{3..4} #spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=saleorder01master.t_order_$->{1..2},saleorder02master.t_order_$->{3..4} spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.sharding-column=orderId spring.shardingsphere.sharding.tables.t_order.table-strategy.standard.precise-algorithm-class-name=com.masterslavesharding.demo.algorithm.OrderTablePreciseShardingAlgorithm #master slave spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin #指定ebusinessmaster为主库，slave0为它的从库 spring.shardingsphere.sharding.master-slave-rules.ebusinessmaster.master-data-source-name=ebusinessmaster spring.shardingsphere.sharding.master-slave-rules.ebusinessmaster.slave-data-source-names=ebusinessslave01 #指定master1为主库，ebusinessslave01为它的从库 spring.shardingsphere.sharding.master-slave-rules.saleorder01.master-data-source-name=saleorder01 spring.shardingsphere.sharding.master-slave-rules.saleorder01.slave-data-source-names=saleorder01slave01 #指定master1为主库，slave1为它的从库 spring.shardingsphere.sharding.master-slave-rules.saleorder02.master-data-source-name=saleorder02 spring.shardingsphere.sharding.master-slave-rules.saleorder02.slave-data-source-names=saleorder02slave01 #debug spring.shardingsphere.props.sql.show=true #mybatis mybatis.mapper-locations=classpath:/mapper/*Mapper.xml mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
3，数据库中的数据表：
CREATE TABLE `goods` ( `goodsId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id', `goodsName` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'name', `stock` int(11) NOT NULL DEFAULT '0' COMMENT 'stock', PRIMARY KEY (`goodsId`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表'
插入一条示例数据:
INSERT INTO `goods` (`goodsId`, `goodsName`, `stock`) VALUES (3, 'green cup1', 70);
分库分表的订单表：
CREATE TABLE `t_order_4` ( `orderId` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `goodsName` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'name', PRIMARY KEY (`orderId`) ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='order4'
因为4个表的结构相同，其他表也可用这个建表sql
四，java代码说明
1,DatabasePreciseShardingAlgorithm.java
@Component public class DatabasePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) { System.out.println("------------------select database name"); Long curValue = shardingValue.getValue(); String curBase = ""; if (curValue > 0 && curValue<=200) { curBase = "saleorder01"; } else { curBase = "saleorder02"; } return curBase; } }
分库算法类
2,OrderTablePreciseShardingAlgorithm.java
@Component public class OrderTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) { Long curValue = shardingValue.getValue(); String curTable = ""; if (curValue > 0 && curValue<=100) { curTable = "t_order_1"; } else if (curValue > 100 && curValue<=200) { curTable = "t_order_2"; } else if (curValue > 200 && curValue<=300) { curTable = "t_order_3"; } else { curTable = "t_order_4"; } return curTable; } }
分表算法类
3,DruidConfig.java
@Configuration public class DruidConfig { /** * Druid监控 */ @Bean public ServletRegistrationBean statViewServlet(){ ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*"); Map<String,String> initParams = new HashMap<>();//这是配置的druid监控的登录密码 initParams.put("loginUsername","root"); initParams.put("loginPassword","root"); //默认就是允许所有访问 initParams.put("allow","127.0.0.1,192.168.3.4"); //黑名单IP initParams.put("deny","192.168.15.21"); bean.setInitParameters(initParams); return bean; } /** * web监控的filter */ @Bean public FilterRegistrationBean webStatFilter(){ FilterRegistrationBean bean = new FilterRegistrationBean(); bean.setFilter(new WebStatFilter()); Map<String,String> initParams = new HashMap<>(); initParams.put("exclusions","/static/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");//过滤掉需要监控的文件 bean.setInitParameters(initParams); bean.setUrlPatterns(Arrays.asList("/*")); return bean; } }
druid配置
4,GoodsController.java
@RestController @RequestMapping("/goods") public class GoodsController { private static final String SUCCESS = "SUCCESS"; private static final String FAIL = "FAIL"; @Resource private GoodsMapper goodsMapper; //更新商品库存 参数:商品id,增加的库存数量 @RequestMapping("/goodsstock/{goodsId}/{count}") @ResponseBody public String goodsStock(@PathVariable Long goodsId, @PathVariable int count) { int res = goodsMapper.updateGoodsStock(goodsId,count); System.out.println("res:"+res); if (res>0) { return SUCCESS; } else { return FAIL; } } //商品详情 参数:商品id @GetMapping("/goodsinfo") @ResponseBody public Goods goodsInfo(@RequestParam(value="goodsid",required = true,defaultValue = "0") Long goodsId) { Goods goods = goodsMapper.selectOneGoods(goodsId); return goods; } }
对非分库分表的库的访问
5,OrderController.java
@Controller @RequestMapping("/order") public class OrderController { private static final String SUCCESS = "SUCCESS"; private static final String FAIL = "FAIL"; @Resource private OrderMapper orderMapper; //订单列表，列出分库分表的数据 @GetMapping("/orderlist") public String list(Model model, @RequestParam(value="currentPage",required = false,defaultValue = "1") Integer currentPage){ PageHelper.startPage(currentPage, 5); List<Order> orderList = orderMapper.selectAllOrder(); model.addAttribute("orderlist",orderList); PageInfo<Order> pageInfo = new PageInfo<>(orderList); model.addAttribute("pageInfo", pageInfo); System.out.println("------------------------size:"+orderList.size()); return "order/list"; } //添加一个订单 @GetMapping("/addorder") @ResponseBody public String addOrder(@RequestParam(value="orderid",required = true,defaultValue = "0") Long orderId ) throws SQLException, IOException { String goodsId = "3"; String goodsNum = "1"; String goodsName = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()); Order orderOne = new Order(); orderOne.setOrderId(orderId); orderOne.setGoodsName(goodsName); int resIns = orderMapper.insertOneOrder(orderOne); System.out.println("orderId:"+orderOne.getOrderId()); if (resIns>0) { return SUCCESS; } else { return FAIL; } } }
对分库分表的订单表的访问
6,GoodsMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.masterslavesharding.demo.mapper.GoodsMapper"> <select id="selectOneGoods" parameterType="long" resultType="com.masterslavesharding.demo.pojo.Goods"> select * from goods where goodsId=#{goodsId} </select> <update id="updateGoodsStock"> UPDATE goods SET stock = stock+#{changeAmount,jdbcType=INTEGER} WHERE goodsId = #{goodsId,jdbcType=BIGINT} </update> </mapper>
7,OrderMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.masterslavesharding.demo.mapper.OrderMapper" > <select id="selectAllOrder" resultType="com.masterslavesharding.demo.pojo.Order"> select * from t_order order by orderId desc </select> <insert id="insertOneOrder" parameterType="com.masterslavesharding.demo.pojo.Order" useGeneratedKeys="true" keyProperty="orderId" > insert into t_order(orderId,goodsName) values( #{orderId},#{goodsName} ) </insert> </mapper>
8,其他非关键的对象类等可访问github获取
五，测试效果
1,测试非分表库写入:
查看主库的商品表：
查看从库的商品表:
更新数据:访问:
http://127.0.0.1:8080/goods/goodsstock/3/6
给商品添加了6个库存:
查看主库的商品表：
查看从库的商品表:
可以看到修改已生效
查看应用的读取:
http://127.0.0.1:8080/goods/goodsinfo?goodsid=3
返回:
2,测试非分表库读取
修改从库中商品的信息：如图:
再次访问商品的信息：
http://127.0.0.1:8080/goods/goodsinfo?goodsid=3
返回:
查看此时主库的商品表：
可见数据由从库读取，读写分离已生效
3,测试分表库写入,访问:
http://127.0.0.1:8080/order/addorder?orderid=50
返回:
SUCCESS
查看主库的数据表：
查看从库的数据表:
可以看到主库和从库都写入了
4,测试分表库读取:
查看上一步写入的id为50的订单:
手动修改从库中orderId=50的商品的名字:注意是从库:
例如修改为:
再次查看订单列表的读取:
修改已生效，证明数据是由从库中读取
六，查看spring boot的版本:
. ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \ ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ :: Spring Boot :: (v2.3.4.RELEASE)
