关于mysql:ShardingSphereProxy-50-分库分表一

@[toc]

一、简述

简述
ShardingSphere-Proxy4.0 曾经降级到5.0了，然而两者的配置文件还有肯定的差异的，这篇文章讲述的就是ShardingSphere-Proxy 5.0 的落地。概念、分表、分库、分库分表的原理的根本和4.0一样的，须要理解可查看 https://blog.csdn.net/Fu_Shi_...。
开发者文档
https://shardingsphere.apache...

二、ShardingSphere-Proxy5.0 落地

环境

JAVA JDK下载

https://pan.baidu.com/s/1A-ksNN0YicT3hXjFscGGwA 提取码：r9e0

JDBC数据连贯驱动下载

https://pan.baidu.com/s/1924iUe7wxGpStAzxxv2K3g 提取码：jy7z

ShardingSphere-Proxy 5.0 下载、

https://archive.apache.org/dist/shardingsphere/5.0.0/

Window 11

落地

条件
1. 实在数据库
2. 逻辑数据库
3. ShardingSphere-Proxy5.0 配置文件

步骤

新建实在数据库和表【hmms.user 表为例】

数据库表构造语句

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for user-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user`  (  `useid` int(11) NOT NULL,  `usenam` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '登录名',  `usepwd` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '登录明码',  `usestate` int(11) NULL DEFAULT 2 COMMENT '-1：删除1：登记 2：失常 3：挂失',  `usekey` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户秘钥',  `usetel` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户手机',  `createbyid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '增加人',  `createbytime` datetime(0) NULL DEFAULT NULL COMMENT '增加工夫',  `modifybyid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '批改人',  `modifybytime` datetime(0) NULL DEFAULT NULL COMMENT '批改工夫',  PRIMARY KEY (`useid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;

运行后果如下：

  2. 批改ShardingSphere-Proxy5.0  配置文件，并连贯   config-sharding.yaml   ```   # 3、创立客户端连贯库   schemaName: hmms      #1、连贯mysql   dataSources:     hmmsdatasources-0:       url: jdbc:mysql://localhost:3306/hmms?serverTimezone=UTC&useSSL=false       username: root       password: ****       connectionTimeoutMilliseconds: 30000       idleTimeoutMilliseconds: 60000       maxLifetimeMilliseconds: 1800000       maxPoolSize: 50       minPoolSize: 1          # 2、分片规定   rules:   - !SHARDING     tables:       user: #表名         actualDataNodes: hmmsdatasources-0.user-${0..1}  #分表规定         tableStrategy:           standard:             shardingColumn: useid   #分片键             shardingAlgorithmName: use_MOD  #分表算法     shardingAlgorithms:  #分表算法       use_MOD:  #取模算法         type: MOD         props:           sharding-count: 2   ```   server.yaml   ```   rules:     - !AUTHORITY       users:         - root@%:*****         - sharding@:sharding       provider:         type: ALL_PRIVILEGES_PERMITTED   ```   运行命令   ```   #在ShardingSphere-Proxy bin   start.bat 端口号   ```   运行后果如下：   ![在这里插入图片形容](https://img-blog.csdnimg.cn/8d7b5ca3c7e84edb927779bcc84da367.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQEDnpZ7lhpw=,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)3. 新建逻辑数据连贯，并执行表构造和数据脚本   逻辑库执行后果如下：   ![在这里插入图片形容](https://img-blog.csdnimg.cn/fcb4986d5c6142b58892c09826ea715e.png#pic_center)    逻辑数据库增加两条数据   SQL语句   ```   INSERT INTO `user` VALUES (1, 'admin', '202CB962AC59075B964B07152D234B70', 2, '123', '123123', 'xiaogang', '2021-08-25 20:12:15', 'xiaogang', NULL);   INSERT INTO `user` VALUES (2, 'admin', '202CB962AC59075B964B07152D234B70', 2, '123', '123123', 'xiaogang', '2021-08-25 20:12:15', 'xiaogang', NULL);   ```   执行后果如下：   ![在这里插入图片形容](https://img-blog.csdnimg.cn/2e27942bdd1b472fb4451ead2f76c133.png#pic_center)

分库分表

ShardingSphere-Proxy 5.0 配置

# 3、创立客户端连贯库schemaName: hmms#1、连贯mysqldataSources:  hmmsdatasources-0:    url: jdbc:mysql://主机地址:端口号/hmms-0?serverTimezone=UTC&useSSL=false    username: root    password: 明码    connectionTimeoutMilliseconds: 30000    idleTimeoutMilliseconds: 60000    maxLifetimeMilliseconds: 1800000    maxPoolSize: 50    minPoolSize: 1  hmmsdatasources-1:    url: jdbc:mysql://主机地址:端口号/hmms-1?serverTimezone=UTC&useSSL=false    username: root    password: 明码    connectionTimeoutMilliseconds: 30000    idleTimeoutMilliseconds: 60000    maxLifetimeMilliseconds: 1800000    maxPoolSize: 50    minPoolSize: 1    # 2、分片规定rules:- !SHARDING  tables:    user:      actualDataNodes: hmmsdatasources-${0..1}.user-${0..1}         tableStrategy:        standard:          shardingColumn: useid          shardingAlgorithmName: use_MOD      databaseStrategy:  #分库规定          standard:            shardingColumn: useid            shardingAlgorithmName: use_MOD      keyGenerateStrategy:         column: useid        keyGeneratorName: snowflake  shardingAlgorithms:    use_MOD:      type: MOD      props:        sharding-count: 2     use_HASH_MOD:      type: HASH_MOD      props:        sharding-count: '2'  keyGenerators:     snowflake:        type: SNOWFLAKE       props:          worker-id: 123

分片算法

取模算法

shardingAlgorithms:  #分表算法use_MOD:  #取模算法  type: MOD  props:    sharding-count: 2  #分表数据和分表的表达式必须是统一的

范畴算法

  shardingAlgorithms:    use_BOUNDARY_RANGE:      type: BOUNDARY_RANGE      props:        sharding-ranges: 2,100  #多个节点是以逗号宰割  [是依据分的表联合配置节点] #备注  #分片键为：useid  int类型  #分表的为3个表  #sharding-ranges: 2,100 是以2为节点，小于2的数据存到表0，2到99存到表1,100以上存到表3

容量算法

shardingAlgorithms:    use_VOLUME_RANGE:            type: VOLUME_RANGE            props:              range-lower: '20000000'  #最小值              range-upper: '40000000'  #最大值              # 分片的区间的数据的距离              sharding-volume: '20000000' #备注    最小值为2000万，也就是说表数据量小于等于2000万，最大数量为4000万    表与表的距离为2000万    比如说分了两张表：    1-2000万       存到表0    2001万-4000万   存到表1    是依据分表的数量来定义最大值的    分了三张表，那最大值为6000万

HASH-CODE算法【如果分片键是字符串类型，须要这种算法分表】

  shardingAlgorithms:    use_HASH_MOD:      type: HASH_MOD      props:        sharding-count: '2'  #分表数量，单引号必须要加

依据工夫分表算法

shardingAlgorithms:    use_AUTO_INTERVAL:        type: AUTO_INTERVAL        props:          datetime-lower: '2020-01-01 23:59:59'          datetime-upper: '2022-12-31 23:59:59'          # 以1年度为单位进行划分          sharding-seconds: '31536000'            # 以1个月为单位进行划分          #sharding-seconds: '2678400'             # 以1天为单位进行划分          #sharding-seconds: '86400' #设置的最大值必须和分多少表匹配才行，否者报错，找不到表

分布式ID

config-sharding.yaml

# 3、创立客户端连贯库schemaName: hmms#1、连贯mysqldataSources:  hmmsdatasources-0:    url: jdbc:mysql://localhost:3306/hmms?serverTimezone=UTC&useSSL=false    username: root    password: 1QAZ2WSX3EDC    connectionTimeoutMilliseconds: 30000    idleTimeoutMilliseconds: 60000    maxLifetimeMilliseconds: 1800000    maxPoolSize: 50    minPoolSize: 1    # 2、分片规定rules:- !SHARDING  tables:    user:      actualDataNodes: hmmsdatasources-0.user-${0..1}      tableStrategy:        standard:          shardingColumn: id          shardingAlgorithmName: use_HASH_MOD      keyGenerateStrategy:         column: id        keyGeneratorName: snowflake  shardingAlgorithms:    use_MOD:      type: MOD      props:        sharding-count: 2     use_HASH_MOD:      type: HASH_MOD      props:        sharding-count: '2'  keyGenerators:     snowflake:        type: SNOWFLAKE       props:          worker-id: 123

表构造sql语句

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for user-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user`  (  `id` varchar(100),  `useid` int(11) NOT NULL,  `usenam` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '登录名',  `usepwd` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '登录明码',  `usestate` int(11) NULL DEFAULT 2 COMMENT '-1：删除1：登记 2：失常 3：挂失',  `usekey` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户秘钥',  `usetel` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户手机',  `createbyid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '增加人',  `createbytime` datetime(0) NULL DEFAULT NULL COMMENT '增加工夫',  `modifybyid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '批改人',  `modifybytime` datetime(0) NULL DEFAULT NULL COMMENT '批改工夫',  PRIMARY KEY (`useid`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;SET FOREIGN_KEY_CHECKS = 1;

数据填充语句

INSERT INTO `user`(useid,usenam,usepwd,usestate,usekey,usetel,createbyid,createbytime,modifybyid,modifybytime) VALUES (1, 'admin', '202CB962AC59075B964B07152D234B70', 2, '123', '123123', 'xiaogang', '2021-08-25 20:12:15', 'xiaogang', NULL);

执行构造如下：