ShardingSphere-Proxy 分库分表

安装ShardingSphere-Proxy

中间件封装

定位为透明化的数据库代理端，提供封装了数据库二进制协议的服务端版本，用于完成对异构语言的支持。 目前提供 MySQL 和 PostgreSQL版本，它可以使用任何兼容 MySQL/PostgreSQL 协议的访问客户端（如：MySQL Command Client, MySQL Workbench, Navicat 等）操作数据，对 DBA 更加友好。

5.1.1安装：

https://shardingsphere.apache.org/document/5.1.1/cn/user-manual/shardingsphere-proxy/startup/bin/)

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

下载jar包后，上传到解压到 /usr/local 文件后解压

tar -zxvf apache-shardingsphere-5.1.1-shardingsphere-proxy-bin.tar.gz 

新建ext-lib目录

cd /usr/local/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin

mkdir  ext-lib

然后将MySQL驱动mysql-connector-java-8.0.22.jar 放到ext-lib目录

修改配置文件

cd /usr/local/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/conf

vim server.yaml

左以下配置，表示任何服务器都可以使用root用户登录，并且有所有的权限

rules:
  - !AUTHORITY
    users:
      - root@%:root
    provider:
      type: ALL_PRIVILEGES_PERMITTED
# 打印sql
props:
  sql-show: true
  
  

启动：

/usr/local/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/bin/start.sh 3306

也可以指定端口号和配置文件目录：`bin/start.bat ${proxy_port} ${proxy_conf_directory}` ，不指定就是3307端口

停止：

/usr/local/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/bin/stop.sh

查看启动状态

 ps -ef|grep shardingsphere|grep -v grep

开启端口:

firewall-cmd --zone=public --add-port=3306/tcp --permanent

重启防火墙:

firewall-cmd --reload #重启
firewall systemctl stop firewalld.service #停止
firewall systemctl disable firewalld.service #禁止firewall开机启动

远程连接

mysql -h192.168.158.166 -p3306 -uroot -p

连接成功

也可以使用可视化工具连接，和正常使用mysql没有区别

配置读写分离

/conf 目录下有以下几个配置文件

修改配置文件 config-readwrite-splitting.yaml

cd  /usr/local/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/conf

vim  config-readwrite-splitting.yaml

schemaName: readwrite_splitting_db

dataSources:
  db1:
    url: jdbc:mysql://192.168.158.134:3306/distributed_server?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: psh120370
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  slave1:
    url: jdbc:mysql://192.168.158.146:3306/distributed_server?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: psh120370
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  db0:
    url: jdbc:mysql://192.168.158.165:3306/distributed_server?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: psh120370
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      type: Static
      props:
        write-data-source-name: db1
        read-data-source-names: slave1

重新启动：

/usr/local/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/bin/start.sh 3306

对 readwrite_splitting_db 的查询就会落在 slave1 上，写就会落在 db1上，slave1 是db1的从库，readwrite_splitting_db 就是一个代理库

通过springboot整合ShardingSphere-Proxy，不需要添加任何额外的依赖，在配置数据源的时候需要连接上面的逻辑库readwrite_splitting_db

spring:
  application:
    name: shardingProxy
  cloud:
    nacos:
      discovery:
        server-addr: http://192.168.158.135:8848
        namespace: d3a69e71-1c55-411c-940a-1f275c8c7bea
      username: nacos
      password: nacos
  datasource:
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://192.168.158.166:3306/readwrite_splitting_db?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: root

水平分片

表结构和查询逻辑见：

https://blog.csdn.net/persistence_PSH/article/details/131367613

修改配置config-sharding.yaml

cd  /usr/local/apache-shardingsphere-5.1.1-shardingsphere-proxy-bin/conf

vim  config-sharding.yaml

这里的 schemaName 需要和上面的读写分离逻辑库的名称不一致，其实就是把原本在spring boot中的配置写到了ShardingSphere-Proxy

schemaName: sharding_db

dataSources:
  db1:
    url: jdbc:mysql://192.168.158.134:3306/distributed_server?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: psh120370
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  slave1:
    url: jdbc:mysql://192.168.158.146:3306/distributed_server?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: psh120370
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
  db0:
    url: jdbc:mysql://192.168.158.165:3306/distributed_server?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
    username: root
    password: psh120370
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    test_order:
      actualDataNodes: db$->{0..1}.test_order$->{0..1}
      databaseStrategy:
        standard:
          shardingColumn: account
          shardingAlgorithmName: inline_account
      tableStrategy:
        standard:
          shardingColumn: order_no
          shardingAlgorithmName: hash_mod
      keyGenerateStrategy:
        column: id
        keyGeneratorName: snowflake
    test_order_item:
      actualDataNodes: db$->{0..1}.test_order_item$->{0..1}
      databaseStrategy:
        standard:
          shardingColumn: account
          shardingAlgorithmName: inline_account
      tableStrategy:
        standard:
          shardingColumn: order_no
          shardingAlgorithmName: hash_mod
      keyGenerateStrategy:
        column: id
        keyGeneratorName: snowflake

  bindingTables:
    - test_order,test_order_item


  shardingAlgorithms:
    inline_account:
      type: HASH_MOD
      props:
        sharding-count: 2
    hash_mod:
      type: HASH_MOD
      props:
        sharding-count: 2
  
  keyGenerators:
    snowflake:
      type: SNOWFLAKE

springboot配置文件

spring:
  application:
    name: shardingProxy
  cloud:
    nacos:
      discovery:
        server-addr: http://192.168.158.135:8848
        namespace: d3a69e71-1c55-411c-940a-1f275c8c7bea
      username: nacos
      password: nacos
  datasource:
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://192.168.158.166:3306/sharding_db?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&useSSL=false
      username: root
      password: root

查询成功：