热门标签
热门文章
- 1SpringBlade export-user SQL 注入漏洞_eyj0exaioijkv1qilcjhbgcioijiuzuxmij9.eyjpc3mioijpc
- 2机器学习大模型驱动：未来的趋势与应用_介绍大模型时代,传统机器学习的发展趋势,以及数据科学,传统机器学习如何 与大模型
- 3我们如何为 JavaScript 客户端减半模块化 AWS SDK 的发布规模
- 4VMware15.5 Pro 安装 Mac OS 10.15.1(虚拟机内Mac分辨率调整解决办法)_vmwaremacos 1024*768
- 5kaggle竞赛实战8——其他方案之XGBOOST及NLP特征优化
- 6黑苹果+win10双系统折腾笔记
- 7大数据技术原理与应用 【笔记】_大数据技术原理与应用》笔记
- 8喂饭级教程！零代码搭建本地个人知识库 ，支持GPT4、Llama3、Kimi等十几种大模型...
- 9怎么找国企的工作？
- 10MD5加密工具类
当前位置: article > 正文
ShardingSphere同时实现分库分表和读写分离，分库分表不生效问题_shardingrule 分库分表
作者：秋刀鱼在做梦 | 2024-06-25 16:10:10
赞
踩
shardingrule 分库分表
问题产生的配置，依赖
<sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>
#这种配置（spring.shardingsphere.masterslave这一层）会导致分库分表的配置不生效，
masterslave:
# 配置主从名称，可以任意取名字
name: ms
# 配置主库master，负责数据的写入
master-data-source-name: ds0
# 配置从库slave节点
slave-data-source-names: ds1
# 配置slave节点的负载均衡均衡策略，采用轮询机制
load-balance-algorithm-type: round_robin
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
需要更换配置写法
在"spring.shardingsphere.sharding.master-slave-rules"下面
# 这种读写分离配置才能用(读写分离，但是数据库一定要做主从复制)
master-slave-rules:
ds0: #数据库分区,此处必须为 shardingsphere.names中列举的数据源
master-data-source-name: ds0 #ds_0区的主库
slave-data-source-names: ds1,ds2 #ds_0区的从库
load-balance-algorithm-type: round_robin
- 1
- 2
- 3
- 4
- 5
- 6
- 7
当前POM
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.3</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>sharding-jdbc</artifactId> <version>0.0.1-SNAPSHOT</version> <name>sharding-jdbc</name> <description>sharding-jdbc</description> <properties> <java.version>1.8</java.version> <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version> </properties> <dependencies> <!-- 依赖web --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- 依赖mybatis和mysql驱动 --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <!-- <version>5.1.47</version>--> <scope>runtime</scope> </dependency> <!--依赖lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!--依赖sharding--> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>${sharding-sphere.version}</version> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-core-common</artifactId> <version>${sharding-sphere.version}</version> </dependency> <!--依赖数据源druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.21</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> </project>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
当前配置
server: port: 8085 spring: main: allow-bean-definition-overriding: true shardingsphere: # 参数配置，显示sql props: sql: show: true # 配置数据源 datasource: # 给每个数据源取别名，下面的ds1,ds2,ds3任意取名字 names: ds0,ds1,ds2 # 给master-ds1每个数据源配置数据库连接信息 ds0: # 配置druid数据源 type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.49.128:3306/db_test01?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 100 minPoolSize: 5 # 配置ds2-slave ds1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.49.129:3306/db_test01?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 100 minPoolSize: 5 # 配置ds3-slave ds2: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.49.129:3306/db_test01?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 100 minPoolSize: 5 # 配置默认数据源ds1 sharding: # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。 default-data-source-name: ds0 # 配置分表的规则 tables: # ksd_user 逻辑表名 ksd_user: # 数据节点：数据源$->{0..N}.逻辑表名$->{0..N} actual-data-nodes: ds0.ksd_user$->{0..1} # 拆分库策略，也就是什么样子的数据放入放到哪个数据库中。 # database-strategy: # inline: # sharding-column: age # 分片字段（分片键） # algorithm-expression: ds->{age % 2} # 分片算法表达式 table-strategy: inline: sharding-column: age # 分片字段（分片键） algorithm-expression: ksd_user$->{age % 2} # 分片算法表达式 # 这种读写分离配置才能用(读写分离，但是数据库一定要做主从复制) master-slave-rules: ds0: #数据库分区,此处必须为 shardingsphere.names中列举的数据源 master-data-source-name: ds0 #ds_0区的主库 slave-data-source-names: ds1,ds2 #ds_0区的从库 load-balance-algorithm-type: round_robin # #这种配置会导致分库分表的配置不生效，需要在“spring.shardingsphere.masterslave”增加 # masterslave: # # 配置主从名称，可以任意取名字 # name: ms # # 配置主库master，负责数据的写入 # master-data-source-name: ds0 # # 配置从库slave节点 # slave-data-source-names: ds1 # # 配置slave节点的负载均衡均衡策略，采用轮询机制 # load-balance-algorithm-type: round_robin # 整合mybatis的配置XXXXX mybatis: mapper-locations: classpath:mapper/*.xml type-aliases-package: com.xuexiangban.shardingjdbc.entity
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
可以看到：查询，写入都是按照当前的配置策略进行
2024-04-05 15:18:07.029 INFO 11112 --- [nio-8085-exec-8] ShardingSphere-SQL : Rule Type: sharding 2024-04-05 15:18:07.029 INFO 11112 --- [nio-8085-exec-8] ShardingSphere-SQL : Logic SQL: insert into ksd_user(nickname,password,sex,birthday,age) values(?,?,?,?,?) 2024-04-05 15:18:07.029 INFO 11112 --- [nio-8085-exec-8] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=ksd_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=age, tableName=ksd_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=4})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=ksd_user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=20)], parametersIndex=5, logicSQL=insert into ksd_user(nickname,password,sex,birthday,age) values(?,?,?,?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[nickname, password, sex, birthday, age], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@595d7a86, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@322e6e6c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@96c0713, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5fbd6bf7, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6d06b3cc])]) 2024-04-05 15:18:07.029 INFO 11112 --- [nio-8085-exec-8] ShardingSphere-SQL : Actual SQL: ds0 ::: **insert into ksd_user0** (nickname, password, sex, birthday, age) VALUES (?, ?, ?, ?, ?) ::: [zhangsan579865212, 1234567, 1, 1988-12-03, 0] 2024-04-05 15:18:10.566 INFO 11112 --- [nio-8085-exec-9] ShardingSphere-SQL : Rule Type: sharding 2024-04-05 15:18:10.566 INFO 11112 --- [nio-8085-exec-9] ShardingSphere-SQL : Logic SQL: insert into ksd_user(nickname,password,sex,birthday,age) values(?,?,?,?,?) 2024-04-05 15:18:10.566 INFO 11112 --- [nio-8085-exec-9] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=ksd_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=age, tableName=ksd_user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=4})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=ksd_user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=20)], parametersIndex=5, logicSQL=insert into ksd_user(nickname,password,sex,birthday,age) values(?,?,?,?,?)), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[nickname, password, sex, birthday, age], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@595d7a86, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@322e6e6c, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@96c0713, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@5fbd6bf7, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6d06b3cc])]) 2024-04-05 15:18:10.566 INFO 11112 --- [nio-8085-exec-9] ShardingSphere-SQL : Actual SQL: ds0 ::: insert into ksd_user1 (nickname, password, sex, birthday, age) VALUES (?, ?, ?, ?, ?) ::: [zhangsan-1515777454, 1234567, 1, 1988-12-03, 1] 2024-04-05 15:11:01.929 INFO 11112 --- [nio-8085-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet' 2024-04-05 15:11:01.930 INFO 11112 --- [nio-8085-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet' 2024-04-05 15:11:01.930 INFO 11112 --- [nio-8085-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 0 ms 2024-04-05 15:11:02.493 INFO 11112 --- [nio-8085-exec-1] ShardingSphere-SQL : Rule Type: sharding 2024-04-05 15:11:02.494 INFO 11112 --- [nio-8085-exec-1] ShardingSphere-SQL : Logic SQL: select * from ksd_user where 1=1 and age = ? 2024-04-05 15:11:02.494 INFO 11112 --- [nio-8085-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=ksd_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=age, tableName=ksd_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=ksd_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select * from ksd_user where 1=1 and age = ?)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[]) 2024-04-05 15:11:02.494 INFO 11112 --- [nio-8085-exec-1] ShardingSphere-SQL : Actual SQL: ds1 ::: select * from ksd_user0 where 1=1 and age = ? ::: [0] 2024-04-05 15:11:04.459 INFO 11112 --- [nio-8085-exec-2] ShardingSphere-SQL : Rule Type: sharding 2024-04-05 15:11:04.459 INFO 11112 --- [nio-8085-exec-2] ShardingSphere-SQL : Logic SQL: select * from ksd_user where 1=1 and age = ? 2024-04-05 15:11:04.459 INFO 11112 --- [nio-8085-exec-2] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=ksd_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=age, tableName=ksd_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=ksd_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select * from ksd_user where 1=1 and age = ?)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[]) 2024-04-05 15:11:04.459 INFO 11112 --- [nio-8085-exec-2] ShardingSphere-SQL : Actual SQL: ds2 ::: select * from ksd_user0 where 1=1 and age = ? ::: [0] 2024-04-05 15:11:06.884 INFO 11112 --- [nio-8085-exec-3] ShardingSphere-SQL : Rule Type: sharding 2024-04-05 15:11:06.884 INFO 11112 --- [nio-8085-exec-3] ShardingSphere-SQL : Logic SQL: select * from ksd_user where 1=1 and age = ? 2024-04-05 15:11:06.885 INFO 11112 --- [nio-8085-exec-3] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=ksd_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=age, tableName=ksd_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=ksd_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select * from ksd_user where 1=1 and age = ?)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[]) 2024-04-05 15:11:06.885 INFO 11112 --- [nio-8085-exec-3] ShardingSphere-SQL : Actual SQL: ds1 ::: select * from ksd_user0 where 1=1 and age = ? ::: [0] 2024-04-05 15:11:21.776 INFO 11112 --- [nio-8085-exec-4] ShardingSphere-SQL : Rule Type: sharding 2024-04-05 15:11:21.776 INFO 11112 --- [nio-8085-exec-4] ShardingSphere-SQL : Logic SQL: select * from ksd_user where 1=1 and age = ? 2024-04-05 15:11:21.776 INFO 11112 --- [nio-8085-exec-4] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=ksd_user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=age, tableName=ksd_user), operator=EQUAL, compareOperator==, positionValueMap={}, positionIndexMap={0=0})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=ksd_user, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=1, logicSQL=select * from ksd_user where 1=1 and age = ?)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[]) 2024-04-05 15:11:21.776 INFO 11112 --- [nio-8085-exec-4] ShardingSphere-SQL : Actual SQL: ds2 ::: select * from ksd_user0 where 1=1 and age = ? ::: [0]
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/秋刀鱼在做梦/article/detail/756733
推荐阅读
相关标签
