shardingSphere-jdbc 搭建主从+ 分库分表实战

一、环境及软件版本

服务器版本： centos 7 redhat

服务器数量： 6 台 ,1core , 20G

名称	IP	配置	角色
master1	192.168.1.105	1 core, 20G	cluster1 - master
slave01	192.168.1.104	1 core, 20G	cluster1 - slave01
slave02	192.168.1.107	1 core, 20G	cluster1 - slave02
master2	192.168.1.109	1 core, 20G	cluster2 - master
slave03	192.168.1.110	1 core, 20G	cluster2 - slave03
slave04	192.168.1.108	1 core, 20G	cluster2 - slave04

二、 集群架构图

image.png

三、环境搭建

mysql主从架构搭建

1、 下载yum repo 依赖包

 wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm

2、 安装mysql

rpm -vih mysql80-community-release-el7-3.noarch.rpm

修改 /etc/yum.repo.d/mysql-community.repo
禁用 mysql8.0 ,启用5.7（采用5.7 版本的数据库）

[图片上传失败...(image-62d023-1593258991034)]

3、 修改配置并启动

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
log_bin=mybinlog

binlog_format = ROW

binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

sync-binlog=1

# These are commonly set, remove the # and set as required.
# user=mysql
# # port = .....

4、 主从配置搭建细节这里不再赘述，已在MHA+Mysql 中详细说明。

四、 shardingSphere 配置细节

yaml 配置细节

spring:
  shardingsphere:
    datasource:
      names:  master1,slave1,slave2,master2,slave3,slave4
      #master1 数据源属性配置细节
      master1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.1.105:3306/lagou?characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B0
        username: root
        password: admin
      #slave1 属性配置细节
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.1.104:3306/lagou?characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B0
        username: root
        password: admin
      #slave2 属性配置细节
      slave2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.1.107:3306/lagou?characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B0
        username: root
        password: admin
      #master2 数据源属性配置细节
      master2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.1.109:3306/lagou?characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B0
        username: root
        password: admin
      #slave3 属性配置细节
      slave3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.1.110:3306/lagou?characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B0
        username: root
        password: admin
      #slave4 属性配置细节
      slave4:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.1.108:3306/lagou?characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B0
        username: root
        password: admin

    #sharding 分片策略配置
    sharding:
      tables:
        # c order 表相关配置
        c_order:
          actual-data-nodes: ms_ds${1..2}.c_order${0..1}
          # 库分片策略
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: ms_ds${user_id % 2+1}
          # 表分片策略
          table-strategy:
            inline:
              sharding-column: id
              algorithm-expression: c_order${id % 2}
          key-generator:
            column: id
            type: lagouKey

      #主从规则配置
      master-slave-rules:
        ms_ds1:
          masterDataSourceName: master1
          slaveDataSourceNames: slave1,slave2
          loadBalanceAlgorithmType: ROUND_ROBIN
        ms_ds2:
          masterDataSourceName: master2
          slaveDataSourceNames: slave3,slave4
          loadBalanceAlgorithmType: ROUND_ROBIN


    # sharding 属性配置显示sql
    props:
      sql.show: true

五、分片验证

数据验证

1、基于user_id 实现分库策略

验证是否user_id 为偶数的数据插入到 master1 中， user_id 为奇数的数据插入到 master2 中。

master1

1593257620580.png

master2

1593257646460.png

2、基于 id 实现分表策略

验证是否id 为偶数的数据插入到 c_order0 中， id 为奇数的数据插入到 c_order1 中。

master1 c_order0, c_order1

1593257764964.png 1593257786642.png

master2 c_order0, c_order1

1593257855795.png 1593257887845.png

控制台日志验证

1、 write

1593258078304.png 1593258109705.png

2、read

1593257984835.png