sharding-jdbc实现mysql分库不分表

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: Spring boot2.x整合sharding-jdbc4.1.1实现mysql分库不分表

说明

环境:Spring boot:2.1.11.RELEASE,mybatis plus3.4.1,sharing-jdeb:4.1.1

说明:

  • 配置了三个数据源,数据根据一定的分片规则,分别分布在不同的数据库进行存储,读写都会在同一个数据库
  • yourdburl:项目的实际数据库地址,yourdbname:项目的实际数据库名称
  • yourdbusername:项目的数据库登录用户名称,yuordbpassword:项目的数据库登录密码
  • your-table-name:项目的表名称
  • id:用于确定数据分片的表的字段,根据该字段对数据库的数量进行取模,选择数据实际读写的数据库,可根据实际情况指定该字段

分库不分表

application.properties

# mybatis plus配置
mybatis-plus.mapper-locations=classpath:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.spring.db.springdb.entity
mybatis-plus.global-config.db-config.id-type=auto
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.cache-enabled=false
#返回map时,true:当查询数据为空时字段返回为null;false:不加这个查询数据为空时,字段将被隐藏
mybatis-plus.configuration.call-setters-on-nulls=false
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
# sharding jdbc 读写分离
spring.shardingsphere.datasource.names=ds0,ds1,ds2
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds0.username=yourdbusername
spring.shardingsphere.datasource.ds0.password=yuordbpassword
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds1.username=yourdbusername
spring.shardingsphere.datasource.ds1.password=yuordbpassword
spring.shardingsphere.datasource.ds2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.ds2.username=yourdbusername
spring.shardingsphere.datasource.ds2.password=yuordbpassword
# 配置一个表的分库规则
spring.shardingsphere.sharding.tables.your-table-name.actual-data-nodes=ds$->{0..2}.your-table-name
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 3}
# 指定具体的表的数据库分片策略,未指定的使用默认的分库规则
# 行表达式分片策略
spring.shardingsphere.sharding.tables.your-table-name.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.your-table-name.database-strategy.inline.algorithm-expression=ds$->{id % 3}
# 打印sharding的数据库信息,可以看出每次执行的sql语句是选用的哪个数据库
spring.shardingsphere.props.sql.show=true

参考资料

ps:

  • 官网的数据分片,同时包括了分库分表,考虑的有的公司可能只分库,不分表,所以笔者单独出了一个例子

分库不分表(读写分离)

步骤总结

application.properties

# mybatis plus配置
mybatis-plus.mapper-locations=classpath:/mapper/**/*.xml
mybatis-plus.type-aliases-package=com.spring.db.springdb.entity
mybatis-plus.global-config.db-config.id-type=auto
mybatis-plus.configuration.map-underscore-to-camel-case=true
mybatis-plus.configuration.cache-enabled=false
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
# 分库不分表,主从复制读写分离
spring.shardingsphere.datasource.names=master0,master0slave0,master0slave1,master1,master1slave0,master1slave1,master2,master2slave0,master2slave1
# 数据源连接信息
spring.shardingsphere.datasource.master0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master0.username=yourdbusername
spring.shardingsphere.datasource.master0.password=yuordbpassword
spring.shardingsphere.datasource.master0slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0slave0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master0slave0.username=yourdbusername
spring.shardingsphere.datasource.master0slave0.password=yuordbpassword
spring.shardingsphere.datasource.master0slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master0slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0slave1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master0slave1.username=yourdbusername
spring.shardingsphere.datasource.master0slave1.password=yuordbpassword
spring.shardingsphere.datasource.master1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master1.username=yourdbusername
spring.shardingsphere.datasource.master1.password=yuordbpassword
spring.shardingsphere.datasource.master1slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1slave0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master1slave0.username=yourdbusername
spring.shardingsphere.datasource.master1slave0.password=yuordbpassword
spring.shardingsphere.datasource.master1slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master1slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1slave1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master1slave1.username=yourdbusername
spring.shardingsphere.datasource.master1slave1.password=yuordbpassword
spring.shardingsphere.datasource.master2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master2.username=yourdbusername
spring.shardingsphere.datasource.master2.password=yuordbpassword
spring.shardingsphere.datasource.master2slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master2slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2slave0.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master2slave0.username=yourdbusername
spring.shardingsphere.datasource.master2slave0.password=yuordbpassword
spring.shardingsphere.datasource.master2slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master2slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master2slave1.url=jdbc:mysql://yourdburl:3306/yourdbname?useUnicode=true&characterEncoding=utf8&useSSL=false&autoReconnect=true&zeroDateTimeBehavior=convertToNull
spring.shardingsphere.datasource.master2slave1.username=yourdbusername
spring.shardingsphere.datasource.master2slave1.password=yuordbpassword
# 数据库主从规则配置
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0,master0slave1
spring.shardingsphere.sharding.master-slave-rules.ds0.load-balance-algorithm-type=round_robin
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave0,master1slave1
spring.shardingsphere.sharding.master-slave-rules.ds1.load-balance-algorithm-type=round_robin
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names=master2slave0,master2slave1
spring.shardingsphere.sharding.master-slave-rules.ds2.load-balance-algorithm-type=round_robin
# 每个表的实际数据节点信息,这里的数据节点就是用主从规则中的数据库实例名称代替
spring.shardingsphere.sharding.tables.your-table-name-a.actual-data-nodes=ds$->{0..2}.your-table-name-a
spring.shardingsphere.sharding.tables.your-table-name-b.actual-data-nodes=ds$->{0..2}.your-table-name-b
spring.shardingsphere.sharding.tables.your-table-name-b.actual-data-nodes=ds$->{0..2}.your-table-name-c
# 默认的分库规则
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 3}
# 为某个表指定自己的分库规则,没有单独指定,采用默认规则
spring.shardingsphere.sharding.tables.your-table-name-a.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.your-table-name-a.database-strategy.inline.algorithm-expression=ds$->{id % 3}
spring.shardingsphere.props.sql.show=true

参考资料

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
8天前
|
关系型数据库 MySQL 中间件
|
8天前
|
关系型数据库 MySQL Java
MySQL的主从复制 && SpringBoot整合Sharding-JDBC解决读写分离
MySQL的主从复制 && SpringBoot整合Sharding-JDBC解决读写分离
41 0
|
8天前
|
Java 关系型数据库 MySQL
JDBC实现往MySQL插入百万级数据
JDBC实现往MySQL插入百万级数据
|
8天前
|
SQL 关系型数据库 MySQL
Spring_jdbc数据连接池(mysql实现增、删、改、查)
Spring_jdbc数据连接池(mysql实现增、删、改、查)
23 0
|
8天前
|
存储 关系型数据库 MySQL
Mysql 分库分区分表
Mysql 分库分区分表
|
8天前
|
SQL Java 关系型数据库
JDBC批量插入mysql数据
JDBC批量插入mysql数据
|
8天前
|
Java 关系型数据库 MySQL
【JDBC编程】基于MySql的Java应用程序中访问数据库与交互数据的技术
【JDBC编程】基于MySql的Java应用程序中访问数据库与交互数据的技术
|
8天前
|
JSON 前端开发 Java
管理系统总结(前端:Vue-cli, 后端Jdbc连接mysql数据库,项目部署tomcat里)
管理系统总结(前端:Vue-cli, 后端Jdbc连接mysql数据库,项目部署tomcat里)
|
8天前
|
Java 关系型数据库 MySQL
{MySQL}索引事务和JDBC
{MySQL}索引事务和JDBC
21 0
|
8天前
|
关系型数据库 MySQL Java
报错 create connection SQLException, url: jdbc:mysql://noreggie?serverTimezone=Asia/Shanghai&useUnicod
报错 create connection SQLException, url: jdbc:mysql://noreggie?serverTimezone=Asia/Shanghai&useUnicod