ShardingSphere中间件实现数据库分库分表操作

shardingSphere实现水平分库分表

数据库规则:userid为奇数,添加到edu_db_1数据库,
userid为偶数,添加到edu_db_2数据库
表规则: id为奇数,添加到course_1 表
id为偶数,添加到course_2表
实验环境
springboot版本：2.2.2.RELEASE
shardingsphere版本：4.0.0-RC1
Druid连接池

数据库：edu_db_1，edu_db_2
表course,分别在course_db_0，course_db_1各自建两张表，course_1和course，两个库共四个表

course表结构如下，自行改表名在两个库各执行一次即可

CREATE TABLE user (
id bigint(255) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
uid varchar(255) DEFAULT NULL,
school varchar(255) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
)

第一步：引入shardingsphere依赖

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

第二步：application.properties配置

配置shardingJDBC分片策略
#配置数据源
spring.shardingsphere.datasource.names=ds0,ds1
#配置数据源具体内容包含连接池 驱动 地址 用户名 密码
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.jdbc-url=jdbc:mysql://youraddress:3306/demo_ds_0
spring.shardingsphere.datasource.ds0.username=
spring.shardingsphere.datasource.ds0.password=
 
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.jdbc-url=jdbc:mysql://youraddress:3306/demo_ds_1
spring.shardingsphere.datasource.ds1.username=
spring.shardingsphere.datasource.ds1.password=

#指定数据库的分布情况以及数据库中表的数据分布情况
spring.shardingsphere.sharding.tables.course(规则).actual-data-nodes=ds$.->{0..1}.course_$->{1..2}
#指定表的分布情况,配置表在哪个数据库里面 表名称都是什么
#spring.shardingsphere.sharding.tables.user(表规则).actual-data-nodes=ds0.user_$->{1..2},ds1.user_$->{1..2}
#指定表中主键的生成策略
spring.shardingsphere.sharding.tables.course.key-generator.column=id
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
#指定表分片策略 约定 id为偶数向一个表中添加数据,奇数向另外一张表中添加数据
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{id % 2+1}
#指定数据库所有表的分片策略,userid为奇数添加到ds0,为偶数添加到ds1中
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=userid
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{userid % 2}
#指定数据库中某些表的分片策略,userid为奇数添加到ds0,为偶数添加到ds1中
spring.shardingsphere.sharding.tables.course.default-database-strategy.inline.sharding-column=userid
spring.shardingsphere.sharding.tables.course.default-database-strategy.inline.algorithm-expression=ds$->{userid % 2}
#打开SQL输出日志
spring.shardingsphere.props.sql.show=true
#一个实体类对应两张表 覆盖
spring.main.allow-bean-definition-overriding=true

垂直分库操作

创建user_db数据库和t_user表
配置垂直分库策略

#配置数据源
spring.shardingsphere.datasource.names=ds2
#配置数据源具体内容包含连接池 驱动 地址 用户名 密码
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.jdbc-url=jdbc:mysql://youraddress:3306/demo_ds_0
spring.shardingsphere.datasource.ds2.username=
spring.shardingsphere.datasource.ds2.password=
#配置user_db数据库中t_user,实现专库专表操作
spring.shardingsphere.sharding.tables.t_user(表名).actual-data-nodes=ds$.->{2}.t_user
#指定表中主键的生成策略
spring.shardingsphere.sharding.tables.t_user.key-generator.column=id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE
#指定表分片策略 约定 id为偶数向一个表中添加数据,奇数向另外一张表中添加数据
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

shardingSphere操作公共表

1.存储固定数据的表,表数据很少发生变化,查询的时候经常进行关联
2.在每个数据库中创建出相同结构公共表t_udict
在yml文件中进行公共表配置

#配置公共表
spring.shardingsphere.sharding.broadcast-tables=t_udict
#指定表中主键的生成策略
spring.shardingsphere.sharding.tables.t_udict.key-generator.column=id
spring.shardingsphere.sharding.tables.t_udict.key-generator.type=SNOWFLAKE

shardingSphere读写分离

为了确保数据库产品的稳定性,很多数据库拥有双机热备功能,也就是第一台数据库服务器对外提供增删改业务的生产服务器,第二台服务器主要进行读的操作
原理:让主数据库处理事务性增,删,改操作,而从数据库进行查询操作
主从复制:当主服务器有写入(insert/update/delete)语句的时候,从服务器自动获取
读写分离:insert/update/delete操作一台服务器,select操作另外一台服务器
shardingJDBC通过SQL语句语义分析实现读写分离过程,不会进行数据同步
配置主从配置

第一步: 创建两个mysql数据库服务,并且启动
1.复制原数据库目录作为从库,并且修改从库my.ini文件
设置端口 port=3307
设置mysql的安装目录:basedir = 
设置mysql数据库的数据存放目录:datadir=
2.把修改之后的从数据库在windows中安装服务
命令:mysqld install mysqls1 --defaults-file="D:\mysql-5.7.25-sl\my.ini"
第二步:修改主从库的配置文件如下:
1.主库 my.ini
开启日志 log-bin = mysql-bin
设置服务id,主从不能一致:server-id = 1
设置需要同步的数据库binlog-do-db = user_db
屏蔽系统库同步:binlog-ignore-db=mysql
						binlog-ignore-db=information_schema
						binlog-ignore-db=performance_schema
2.从库 my.ini
开启日志 log-bin = mysql-bin
设置服务id,主从不能一致:server-id = 2
设置需要同步的数据库replicate-wild-db_table = user_db
屏蔽系统库同步:replicate-wild-ignore_table=mysql
						replicate-wild-ignore_table=information_schema
						replicate-wild-ignore_table=performance_schema
第三步:创建用于主从复制的账号
#切换主数据库bin目录,登录主库
mysql -h localhost -uroot -p
#授权主备复制专用账号
GRANT REPLICATION SLAVE ON *.*  'db_sync'@'%' IDENTIFIED BY 'db_sync';
#刷新权限
FLUSH PRIVILEGES;
#确认位点,记录下文件名以及位点
show master status;
第四步:设置从库向主库同步数据
#切换从库bin目录,
mysql -h localhost -p3307 -uroot -p
#先停止同步
STOP SLAVE;
#修改从库指向到主库,使用上一步记录的文件名以及位点
CHANGE MASTER TO
master_host = 'localhost',
master_user = 'db_sync',
master_password = 'db_sync',
master_log_file = 'log-bin.000002',
master_log_pos = 154;
#启动同步
START SLAVE;
#查看从库状态Slave_IO_running和Slave_SQL_Running都为yes说明同步成功,如果不为yes,请检查error_log,然后排查相关异常
show slave status

ShardingJDBC配置读写分离

# 增加数据源s0，使用上面主从同步配置的从库。
spring.shardingsphere.datasource.names = m0,m1,m2, (主服务器数据源)s0(从服务器数据源)

spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.s0.url = jdbc:mysql://localhost:3307/user_db?useUnicode=true
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = root
# 主库从库逻辑数据源定义 ds0为user_db
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0

# t_user分表策略，固定分配至ds0的t_user真实表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = ds0.t_user
#一主多从配置
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1

Sharding-Proxy简介

定位为透明的数据库代理端
属于一个独立应用,需要安装服务应用,在其中进行分库分表和读写分离应用
官网进行安装下载,启动bin目录下面的启动文件就可以

分表配置
/conf/server.yaml内容如下(放开注释):

authentication:
  users:
    root:
      password: root
    sharding:
      password: sharding 
      authorizedSchemas: sharding_db
 
#
props:
  max.connections.size.per.query: 1
  acceptor.size: 16  # The default value is available processors count * 2.
  executor.size: 16  # Infinite by default.
  proxy.frontend.flush.threshold: 128  # The default value is 128.
#    # LOCAL: Proxy will run with LOCAL transaction.
#    # XA: Proxy will run with XA transaction.
#    # BASE: Proxy will run with B.A.S.E transaction.
  proxy.transaction.type: LOCAL
  proxy.opentracing.enabled: false
  query.with.cipher.column: true
  sql.show: trye
#  allow.range.query.with.inline.sharding: false

其中，orchestration是连接zookeeper注册中心，暂时用不到，将其注释掉。
authentication中，配置的是用户名和密码，以及授权的数据库，在这里，配置了两个用户，分别为：root/root和sharding/sharding，其中root默认授权所有的数据库，而sharding用户则授权sharding_db数据库。这里的数据库（schema）是逻辑数据库，在config-*.yaml中配置的。

分库分表配置
/conf/config-sharding.yaml内容如下:

首先复制mysql驱动jar到lib目录,否则会报错
schemaName: sharding_db
dataSources:
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/coursedb?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_2:
    url: jdbc:mysql://127.0.0.1:3306/coursedb2?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
shardingRule:
  tables:
    course:
      actualDataNodes: ds_${1..2}.course_${1..2}
      tableStrategy:
        inline:
          shardingColumn: cid
          algorithmExpression: course_${cid % 2 + 1}
      keyGenerator:
        type: SNOWFLAKE
        column: cid
  defaultDatabaseStrategy:
    inline:
      shardingColumn: user_id
      algorithmExpression: ds_${user_id % 2 + 1}
  defaultTableStrategy:
    none:

schemaName：是逻辑数据库的名称，这里是sharding_db。在server.yaml文件中，授权的schema就是这里schemaName。
dataSources是数据源配置，这里配置了2个数据源ds_1和ds_2。
shardingRule：defaultTableStrategy，默认表的分片规则，这里配置的是none，没有。也就是说所有的分片表都要配置表的分片规则。
defaultDatabaseStrategy，默认数据库的分片规则，这里配置它的规则为行内表达式，分片字段为user_id，规则为ds_${user_id
% 2}，当user_id为偶数时，数据源为ds_0，也就是前面配置的读写分离数据源；而当user_id为奇数时，数据源为ds_1。
tables,配置分片表规则，actualDataNodes，实际的数据节点，这个节点是在MySQL中真实存在的；tableStrategy，它的规则也是用行内表达式配置的，按cid进行分片，通过cid%2+1将cid为偶数的数据分到course_1表中，cid为奇数的数据分到course_2表中，插入数据时如果没有指定cid，将使用雪花算法SNOWFLAKE生成cid。

启动Sharding-Proxy

start.bat 3307

cmd命令行连接到Sharding-Proxy代理端

mysql -P3307 -usharding -p

读写分离配置
配置config-master_slave.yaml

schemaName: master_slave_db

dataSources:
  ds_master:
    url: jdbc:mysql://127.0.0.1:3306/ds_master?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_slave0:
    url: jdbc:mysql://127.0.0.1:3306/ds_slave0?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  ds_slave1:
    url: jdbc:mysql://127.0.0.1:3306/ds_slave1?serverTimezone=UTC&useSSL=false
    username: root
    password: 123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

masterSlaveRule:
  name: ms_ds
  masterDataSourceName: ds_master
  slaveDataSourceNames:
    - ds_slave0
    - ds_slave1

首先，定义逻辑数据库的名称，schemaName: master_slave_db。
然后在dataSources中定义数据源，这里配置了3个数据源，一主两从，master_ds（主）、slave_ds（从）和slave_ds_1（从）。
最后就是主从的规则masterSlaveRule，在单独的读写分离配置中，只能配置一个主从数据源。主从数据源的名字叫做ds_master，主数据源masterDataSourceName是master_ds，从数据源slaveDataSourceNames配置了两个，ds_slave0和ds_slave1。

Windows下启动服务：

start.bat 3307

使用命令行连接到Sharding-Proxy

mysql -P3307 -umaster_slave -p

参考链接:https://river106.cn/posts/4b118c75.html

版权声明：本文为weixin_46088323原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。