博客

ShardingSphere-Proxy 分库分表

  • Post author:
  • Post category:其他




安装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

查询成功：
请添加图片描述


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