开发者社区> lxyangj> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

sharding-jdbc实现mysql分库不分表

简介: 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

参考资料

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
解决yum安装mysql时Requires: libc.so.6(GLIBC_2.17)(64bit)
1、yum install mysql-community-server 1 2 3 4 5 6 7 Error: Package: mysql-community-libs-5.7.17-1.
5106 0
linux CentOS6.5 yum安装mysql 5.6
1.新开的云服务器,需要检测系统是否自带安装mysql # yum list installed | grep mysql 2.如果发现有系统自带mysql,果断这么干 # yum -y remove mysql-libs.
2360 0
[阿里云ECS]基于Ali ECS CentOS安装Mysql
我的云服务器配置:CPU: 1核    内存:1 GB 准备工作: 1、首先使用   rpm -qa|grep mysql  查看系统是否有自带的mysql2、如果有的话使用 yum remove  mysql-server mysql-libs compat-mysql51  卸载掉3、rm -rf /var/lib/mysql    rm /etc/my.cnf  4、查看是否还有mysql软件:rpm -qa|grep mysql5、最好再使用  find / -name mysql  查看是否还有残留的文件如果存在的话,继续删除即可。
1796 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MySQL表和索引优化实战
立即下载
MySQL查询优化实战
立即下载
One Box:解读事务与分析一体化数据库HybridDB for MySQL
立即下载