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/
说明：刘宏缔的架构森林是一个专注架构的博客，
网站：https://blog.imgtouch.com
本文: https://blog.imgtouch.com/index.php/2023/05/26/spring-boot-shardingsphere-druid-mysql-zhu-cong-fu-zhi-de/
对应的源码可以访问这里获取： https://github.com/liuhongdi/
说明：作者:刘宏缔 邮箱: 371125307@qq.com
二，演示项目的相关信息
1，项目地址:
https://github.com/liuhongdi/masterslave
2,项目功能说明：
演示了shardingsphere整合druid的无分库分表的读写分离
3,项目结构：如图:
三，配置文件说明
1,pom.xml
<!--mybatis begin--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!--druid begin--> <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>
2,application.properties
#shardingsphere spring.shardingsphere.datasource.names=master0,slave0 spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.master0.url=jdbc:mysql://127.0.0.1:3306/ebusiness?characterEncoding=utf-8 spring.shardingsphere.datasource.master0.username=root spring.shardingsphere.datasource.master0.password=password spring.shardingsphere.datasource.master0.initial-size=5 spring.shardingsphere.datasource.master0.min-idle=5 spring.shardingsphere.datasource.master0.maxActive=20 spring.shardingsphere.datasource.master0.maxWait=56789 spring.shardingsphere.datasource.master0.timeBetweenEvictionRunsMillis=60000 spring.shardingsphere.datasource.master0.minEvictableIdleTimeMillis=300000 spring.shardingsphere.datasource.master0.validationQuery=SELECT 1 spring.shardingsphere.datasource.master0.testWhileIdle=true spring.shardingsphere.datasource.master0.testOnBorrow=false spring.shardingsphere.datasource.master0.testOnReturn=false spring.shardingsphere.datasource.master0.poolPreparedStatements=true spring.shardingsphere.datasource.master0.maxPoolPreparedStatementPerConnectionSize=20 spring.shardingsphere.datasource.master0.filters=stat,wall,log4j2 spring.shardingsphere.datasource.master0.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000 spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.slave0.url=jdbc:mysql://127.0.0.1:3307/ebusiness?characterEncoding=utf-8 spring.shardingsphere.datasource.slave0.username=root spring.shardingsphere.datasource.slave0.password=password #打印sql spring.shardingsphere.props.sql.show=true #master slave spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin spring.shardingsphere.masterslave.name=ms spring.shardingsphere.masterslave.master-data-source-name=master0 spring.shardingsphere.masterslave.slave-data-source-names=slave0 #有多个从库用逗号隔开,例: #spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1 #mybatis mybatis.mapper-locations=classpath:/mapper/*Mapper.xml mybatis.type-aliases-package=com.example.demo.mapper mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl #error server.error.include-stacktrace=always #error logging.level.org.springframework.web=trace
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);
四，java代码说明
1,Goods.java
public class Goods { //商品id Long goodsId; public Long getGoodsId() { return this.goodsId; } public void setGoodsId(Long goodsId) { this.goodsId = goodsId; } //商品名称 private String goodsName; public String getGoodsName() { return this.goodsName; } public void setGoodsName(String goodsName) { this.goodsName = goodsName; } //库存 int stock; public int getStock() { return this.stock; } public void setStock(int stock) { this.stock = stock; } //tostring public String toString(){ return " Goods:goodsId=" + goodsId +" goodsName=" + goodsName+" stock=" + stock; } }
2,GoodsMapper.java
@Repository @Mapper public interface GoodsMapper { Goods selectOneGoods(Long goodsId); int updateGoodsStock(@Param("goodsId") Long goodsId, @Param("changeAmount") int changeAmount); }
3,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; } }
4,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管理ui的访问
5,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.masterslave.demo.mapper.GoodsMapper"> <select id="selectOneGoods" parameterType="long" resultType="com.masterslave.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>
6,DemoApplication.java
@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class, JtaAutoConfiguration.class})
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
说明：排除了druid的自动配置
五，效果测试
1,测试增加数据:访问:
http://127.0.0.1:8080/goods/goodsstock/3/5
这个访问为goodsId为3的商品的库存加5
查看数据库:主库:
从库:
可见从库的主从同步是生效的
2,读取数据，数据应该由从数据库得到:
访问:
http://127.0.0.1:8080/goods/goodsinfo?goodsid=3
返回:
手动修改从库的数据，验证数据是否由从库查询得到:
再次查询:
查看主库数据库：
说明数据是由从库查询得到，
读写分离已生效
3，查看druid的管理ui:
http://127.0.0.1:8080/druid/index.html
返回:
六，查看spring boot的版本:
. ____ _ __ _ _ /\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \ ( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \ \\/ ___)| |_)| | | | | || (_| | ) ) ) ) ' |____| .__|_| |_|_| |_\__, | / / / / =========|_|==============|___/=/_/_/_/ :: Spring Boot :: (v2.3.4.RELEASE)
