druid mysql读写分离_spring boot:shardingsphere+druid+mysql主从复制的读写分离(未分库分表)(spring boot 2.3.4)...

最新推荐文章于 2022-02-22 16:45:56 发布

半夏256

最新推荐文章于 2022-02-22 16:45:56 发布

阅读量201

点赞数

文章标签： druid mysql读写分离

本文链接：https://blog.csdn.net/weixin_26757939/article/details/113236109

版权

一，如何实现mysql数据库的读写分离?

1,这个需要先实现mysql数据库的主从复制(master/slave)

请参考:

https://www.cnblogs.com/architectforest/p/12579847.html

说明：作者:刘宏缔邮箱: 371125307@qq.com

二，演示项目的相关信息1，项目地址:

https://github.com/liuhongdi/masterslave

2,项目功能说明：

演示了shardingsphere整合druid的无分库分表的读写分离

3,项目结构：如图:

三，配置文件说明

1,pom.xml

org.mybatis.spring.boot

mybatis-spring-boot-starter

2.1.3

com.alibaba

druid-spring-boot-starter

1.1.23

org.springframework.boot

spring-boot-starter-log4j2

com.lmax

disruptor

3.4.2

mysql

mysql-connector-java

runtime

com.github.pagehelper

pagehelper-spring-boot-starter

1.3.0

org.apache.shardingsphere

sharding-jdbc-spring-boot-starter

4.1.1

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=5spring.shardingsphere.datasource.master0.min-idle=5spring.shardingsphere.datasource.master0.maxActive=20spring.shardingsphere.datasource.master0.maxWait=56789spring.shardingsphere.datasource.master0.timeBetweenEvictionRunsMillis=60000spring.shardingsphere.datasource.master0.minEvictableIdleTimeMillis=300000spring.shardingsphere.datasource.master0.validationQuery=SELECT 1spring.shardingsphere.datasource.master0.testWhileIdle=truespring.shardingsphere.datasource.master0.testOnBorrow=falsespring.shardingsphere.datasource.master0.testOnReturn=falsespring.shardingsphere.datasource.master0.poolPreparedStatements=truespring.shardingsphere.datasource.master0.maxPoolPreparedStatementPerConnectionSize=20spring.shardingsphere.datasource.master0.filters=stat,wall,log4j2

spring.shardingsphere.datasource.master0.connectionProperties=druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000spring.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 classGoods {//商品id

Long goodsId;publicLong getGoodsId() {return this.goodsId;

}public voidsetGoodsId(Long goodsId) {this.goodsId =goodsId;

}//商品名称

privateString goodsName;publicString getGoodsName() {return this.goodsName;

}public voidsetGoodsName(String goodsName) {this.goodsName =goodsName;

}//库存

intstock;public intgetStock() {return this.stock;

}public void setStock(intstock) {this.stock =stock;

}//tostring

publicString toString(){return " Goods:goodsId=" + goodsId +" goodsName=" + goodsName+" stock=" +stock;

}

2,GoodsMapper.java

@Repository

@Mapperpublic interfaceGoodsMapper {

Goods selectOneGoods(Long goodsId);int updateGoodsStock(@Param("goodsId") Long goodsId, @Param("changeAmount") intchangeAmount);

}

3,GoodsController.java

@RestController

@RequestMapping("/goods")public classGoodsController {private static final String SUCCESS = "SUCCESS";private static final String FAIL = "FAIL";

@ResourceprivateGoodsMapper goodsMapper;//更新商品库存参数:商品id,增加的库存数量

@RequestMapping("/goodsstock/{goodsId}/{count}")

@ResponseBodypublicString goodsStock(@PathVariable Long goodsId,

@PathVariableintcount) {int res =goodsMapper.updateGoodsStock(goodsId,count);

System.out.println("res:"+res);if (res>0) {returnSUCCESS;

}else{returnFAIL;

}

}//商品详情参数:商品id

@GetMapping("/goodsinfo")

@ResponseBodypublic Goods goodsInfo(@RequestParam(value="goodsid",required = true,defaultValue = "0") Long goodsId) {

Goods goods=goodsMapper.selectOneGoods(goodsId);returngoods;

}

4,DruidConfig.java

@Configurationpublic classDruidConfig {/*** Druid监控*/@BeanpublicServletRegistrationBean statViewServlet(){

ServletRegistrationBean bean= new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

Map 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);returnbean;

}/*** web监控的filter*/@BeanpublicFilterRegistrationBean webStatFilter(){

FilterRegistrationBean bean= newFilterRegistrationBean();

bean.setFilter(newWebStatFilter());

Map initParams = new HashMap<>();

initParams.put("exclusions","/static/*,*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");//过滤掉需要监控的文件

bean.setInitParameters(initParams);

bean.setUrlPatterns(Arrays.asList("/*"));returnbean;

}

说明：配置druid管理ui的访问

5,GoodsMapper.xml

/p>

PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

select * from goods where goodsId=#{goodsId}

UPDATE goods SET

stock = stock+#{changeAmount,jdbcType=INTEGER}

WHERE goodsId = #{goodsId,jdbcType=BIGINT}

6,DemoApplication.java

@SpringBootApplication(exclude = {DruidDataSourceAutoConfigure.class, JtaAutoConfiguration.class})public classDemoApplication {public static voidmain(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)

半夏256

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
druid mysql读写分离_spring boot:shardingsphere+druid+mysql主从复制的读写分离(未分库分表)(spring boot 2.3.4)...

一，如何实现mysql数据库的读写分离?1,这个需要先实现mysql数据库的主从复制(master/slave)请参考:https://www.cnblogs.com/architectforest/p/12579847.html说明：作者:刘宏缔邮箱: 371125307@qq.com二，演示项目的相关信息1，项目地址:https://github.com/liuhongdi/mastersl...
复制链接

扫一扫