数据库配置读写分离及双数据源同时配置,shardingsphere配置和双数据源dynamic该如何配置
今天在需要配置读写分离的时候我遇见的问题,随手做个笔记。好记性不如烂笔头嘛。
在之前的数据库的配置中已经配置过得是双数据源的配置,相信大家 都熟悉。直接说下配置文件:
双数据源
<!--多数据源配置-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>2.5.6</version>
</dependency>
配置文件:
spring:
application:
name: provider-engineering
main:
allow-bean-definition-overriding: true
datasource:
type: com.zaxxer.hikari.HikariDataSource
dynamic:
primary: engineer
datasource:
engineer:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${base.config.db.hostname}:${base.config.db.port}/shuiku_connect?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&nullNamePatternMatchesAll=true&zeroDateTimeBehavior=CONVERT_TO_NULL&tinyInt1isBit=false&autoReconnect=true&useSSL=false&pinGlobalTxToPhysicalConnection=true
username: root
password: #密码
hikari:
minimum-idle: 5
idle-timeout: 600000
maximum-pool-size: 10
auto-commit: true
pool-name: MyHikariCP
max-lifetime: 1800000
connection-timeout: 30000
connection-test-query: SELECT 1
gnss:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://${base.config.db2.hostname}:${base.config.db2.port}/smos_client?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&nullNamePatternMatchesAll=true&zeroDateTimeBehavior=CONVERT_TO_NULL&tinyInt1isBit=false&autoReconnect=true&useSSL=false&pinGlobalTxToPhysicalConnection=true
username: root
password: #密码
hikari:
minimum-idle: 5
idle-timeout: 600000
maximum-pool-size: 10
auto-commit: true
pool-name: MyHikariCP2
max-lifetime: 1800000
connection-timeout: 30000
connection-test-query: SELECT 1
在这里配置了两个环境:engineer和gnss,具体的使用方法我就不在这里多说了。
接下来是需要配置shardingsphere的读写分离的配置,一开始我还在想,这到底是是该怎么同时配置这两个玩意儿。后来才恍然大悟。
我们在配置了双数据源之后,直接在后面配置读写分离就好了,并不会冲突。
读写分离
#注意格式
spring:
shardingsphere:
datasource:
names: master,slave
master:
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://${base.config.masterdb.hostname}:${base.config.masterdb.port}/shuiku_base?useUnicode=true&characterEncoding=utf-8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: #密码
hikari:
minimum-idle: 5
idle-timeout: 600000
maximum-pool-size: 10
auto-commit: true
pool-name: MyHikariCP
max-lifetime: 1800000
connection-timeout: 30000
connection-test-query: SELECT 1
slave:
type: com.zaxxer.hikari.HikariDataSource
jdbc-url: jdbc:mysql://${base.config.slavedb.hostname}:${base.config.slavedb.port}/shuiku_base?useUnicode=true&characterEncoding=utf-8&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: #密码
hikari:
minimum-idle: 5
idle-timeout: 600000
maximum-pool-size: 10
auto-commit: true
pool-name: MyHikariCP
max-lifetime: 1800000
connection-timeout: 30000
connection-test-query: SELECT 1
rules:
readwrite-splitting:
load-balancers:
round_robin:
type: ROUND_ROBIN
data-sources:
read_write_db:
type: Static
props:
write-data-source-name: master
read-data-source-names: slave
load-balancer-name: round_robin
props:
sql-show: true
遇见问题:
经过搜索发现原因就是没有设置默认的数据库,导致的错误。新增配置:
sharding:
default-data-source-name: master