Sharding sphere实现读写分离+分库分表

作者: 54番茄 | 来源:发表于2021-07-29 17:00 被阅读0次

数据库要实现读写分离，比较主流使用的有sharding sphere和mycat，sharding作为一个组件集成在应用内，Sharding是一个Jar形式，在本地应用层重写Jdbc原生的方法，实现数据库分片形式。而mycat则作为一个独立的应用需要单独部署，是一个基于第三方应用中间件数据库代理框架，客户端所有的jdbc请求都必须要先交给MyCat，再有MyCat转发到具体的真实服务器中。MyCat属于服务器端数据库中间件,更倾向于运维层面，而Sharding是一个本地数据库中间件框架。

从设计理念上看,两则确实有一定的相似性。主要流程都是SQL 解析 -> SQL 路由 -> SQL 改写 -> SQL 执行 -> 结果归并。但架构设计上是不同的。Mycat是基于 Proxy，它复写了MySQL 协议，将 Mycat Server伪装成一个 MySQL数据库，而 Sharding是基于 JDBC 的扩展，是以 jar 包的形式提供轻量级服务的。

借张图

要实现Sharding完成读写分离，提前需要设置一下数据源的主从配置，以常用的Mysql数据库为例子，其他的我也不会。

第一步：数据库的主从配置

准备两台 MySQL 服务器
配置主服务器（Master），配置从服务器（Slave）
完成Master和Slave授权

原理描述：

主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。

从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

每个从服务器都会收到主服务器二进制日志的全部内容的副本,从服务器设备负责决定应该执行二进制日志中的哪些语句,如果需要，您可以将从服务器配置为仅处理一些特定数据库或表的事件。

主从复制是异步的，从库可以获取到所有主库的数据，也可以指定某个库或者某个表。

1、Master(主)数据库节点的配置，进入/etc/my.cnf中(Master节点服务器上)：

主服务器mysql.cnf配置截图

操作以下命令，主服务器完成配置后，重启一下mysql：

1、执行  vim  /etc/my.cnf 进行编辑操作
#设置mysql服务id，同一个网段不能是一样的，必须
server-id=100  
#开启二进制，数据同步需要读取Binary log，必须
log-bin=mysql-bin
#同步过滤，可以指定某个库或者某张表同步，例如本例子只需要对seal_sign_record库需要同步
binlog-do-db=seal_sign_record
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed  
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=20
##为每个session 分配的内存，主要是在事务过程中用来存储二进制日志的缓存，不涉及事物的可以不用管
binlog_cache_size=1M

2、配置完成后重启 mysql ，使用如下命令：
service mysql restart

2、Slave(从)数据库节点配置，进入/etc/my.cnf (Slave节点服务器上)：

1、执行  vim  /etc/my.cnf 进行编辑操作
#设置mysql服务id，同一个网段不能是一样的，必须
server-id=101
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用，如Master->Slave1->Slave2
log-bin=mysql-slave1-bin   
## 开启relay_log配置中继日志,
##relay log很多方面都跟binary log差不多,
##作用：从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致
relay_log=edu-mysql-relay-bin
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed  
#同步过滤，可以指定某个库或者某张表同步，例如本例子只需要对seal_sign_record库需要同步
replicate_do_db=seal_sign_record
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=20
##为每个session 分配的内存，主要是在事务过程中用来存储二进制日志的缓存，不涉及事物的可以不用管
binlog_cache_size=1M

2、配置完成后重启 mysql ，使用如下命令：
service mysql restart

3、在Master节点授权Slave节点,拥有同步权限(在Master节点服务器上执行)：

登录Master服务器上的Mysql授权，记得要flush
mysql > mysql -u root -p12345678
# 授予slave服务器可以同步master服务
mysql > grant replication slave, replication client on *.* to 'root'@'slave服务的ip' identified by 'slave服务器的密码';
mysql > flush privileges;
#查询主节点的mysql中 binlog文件名和位置，如下图，日志文件名：mysql-bin.000018，复制的位置：25262
mysql > show master status;

Master节点的信息

4、在Slave节点上，关联Master节点(在Slave节点服务器上执行)：

mysql > mysql -u root -p12345678
##开始在Slave服务器节点上，关联主节点
##master_log_file=指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
##master_log_pos=从哪个 Position 开始读，即上文中提到的 Position 字段的值
##master_connect_retry=当重新建立主从连接时，如果连接失败，重试的时间间隔，单位是秒，默认是60秒
mysql> change master to master_host='master服务器ip', master_user='root', master_password='master密码', master_port=3306, master_log_file='mysql-bin.000018',master_log_pos=25262;
##启动主从复制
mysql> start slave;
##查看从节点状态
mysql> show slave status\G;

Slave节点信息
主从复制设置完成后，可以在主节点的Mysql中新增数据，在从节点的Mysql就可以看到了。这样后面，就可以做读写分离，主节点为写，从节点为读。

第二步：项目搭建，并引入相关jar包，增加相关配置，注意有大坑

相关jar引入需要注意版本问题，com.mysql.jdbc.Driver驱动是mysql-connector-java 5中的
com.mysql.cj.jdbc.Driver驱动是mysql-connector-java 6以上版本的，以下项目目录 +POM.xml。

项目目录

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.XXX</groupId>
    <artifactId>sign-record</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sign-record</name>
    <description>project for xxxx record</description>
    <properties>
        <java.version>1.8</java.version>
        <spring-cloud.version>Hoxton.SR8</spring-cloud.version>
        <fastjson.version>1.2.44</fastjson.version>
        <!-- 数据库 -->
        <mysql.version>8.0.16</mysql.version>
        <druid.version>1.1.23</druid.version>
        <!-- mybatis版本号 -->
        <mybatis-spring.version>1.3.1</mybatis-spring.version>
        <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>
    </properties>


    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--数据库连接池 Druid引入 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>${druid.version}</version>
        </dependency>
<!--        分页查询插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.2.1</version>
        </dependency>
<!--        mybatis-plus依赖-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring.version}</version>
        </dependency>
<!--        数据源驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <!-- lombok注解，需要安装lombok插件 -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.18</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.23</version>
        </dependency>

        <!--依赖sharding-->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-core-common</artifactId>
            <version>${sharding-sphere.version}</version>
        </dependency>
    </dependencies>


    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>

        <resources>
            <resource>
                <!--设置在java目录下的xml文件，能打进入包，也就是mapper.xml-->
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <!--设置resources中properties、xml、yml 能打进入包-->
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.yml</include>
                    <include>**/*.xml</include>
                </includes>
            </resource>
        </resources>
    </build>

</project>

yml文件配置：此yml根据数据插入时间自然月进行分表，并设置了record1(主)写，record2(从)读，实现读写分离，表名规则为seal_log_202107，分表依据为action_time时间字段

spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，record*
      names: record1,record2
      # 给master-record1每个数据源配置数据库连接信息
      record1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.200.5:3306/seal_sign_record?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: 123456
        maxPoolSize: 100
        minPoolSize: 5
      # 配置record2-slave
      record2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.200.20:3306/seal_sign_record?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: 123456
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds1
    sharding:
      # 配置数据源的读写分离，但是数据库一定要做主从复制
      master-slave-rules:
        # 配置主从名称，可以任意取名字
        ms:
          # 配置主库master，负责数据的写入
          master-data-source-name: record1
          # 配置从库slave节点，多个从节点以逗号分隔
          slave-data-source-names: record2
          # 配置slave节点的负载均衡均衡策略，采用轮询机制
          load-balance-algorithm-type: round_robin
      # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ms
      # 配置分表的规则
      tables:
      #分表表名，表名规则seal_log_20210721，分表依据为action_time时间字段
        seal_log:
          actual-data-nodes: ms.seal_log_$->{2021}${(7..9).collect{t ->t.toString().padLeft(2,'0')} }
          table-strategy:
            standard:
              shardingColumn: action_time
              preciseAlgorithmClassName: com.XXXXXX.config.DatePreciseShardingAlgorithm

本文使用的是精确分片算法PreciseShardingAlgorithm，从写了此接口的方法，在yml中standard下配置生效。

import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.text.NumberFormat;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;

/**
 * @author fanqie
 * @ClassName DatePreciseShardingAlgorithm
 * @date 2021/7/28 下午4:40
 **/

public class DatePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Date>  {


    private static final Logger logger = LoggerFactory.getLogger(DatePreciseShardingAlgorithm.class);
    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Date> preciseShardingValue) {
        Date date = preciseShardingValue.getValue();
        logger.info("Sharding input:" + preciseShardingValue.getValue());
        String suffix = getSuffixByYearMonth(date);
        for (String tableName : availableTargetNames) {
            logger.info("suffix:" + suffix + ", 表明:{}" + tableName);
            if (tableName.endsWith(suffix)) {
                return tableName;
            }
        }
        throw new IllegalArgumentException("未找到匹配的数据表");

    }
/**
 * 自然月为个位数的，进行补零，如07 、08 、09
 **/
    private static String getSuffixByYearMonth(Date date) {
        NumberFormat nf = NumberFormat.getInstance();
        nf.setMinimumIntegerDigits(2);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        return calendar.get(Calendar.YEAR)  +"" +  nf.format((calendar.get(Calendar.MONTH) + 1));
    }
}

注意哈有个大坑：
在很多网上教程中，要么只做了读写分离、要么只做了分库分表，如果你在完成读写分离后，在再同配置中进行分库分表，有个读写分离配置masterslave标签，这个masterslave会造成自定义配置类DatePreciseShardingAlgorithm不起效，无法进行分库分表，血泪啊这个问题找了一下午,区别看下上面yml中master-slave-rules这段配置：
错误配置错误配置错误配置错误配置错误配置如下：

sharding:
      default-data-source-name: record1
    masterslave:
      name: ms
      master-data-source-name: record1
      slave-data-source-names: record2
      load-balance-algorithm-type: round_robin
    # 配置分表的规则
    tables:
      seal_log:
       actual-data-nodes: record1.seal_log_$->{2021}${(7..9).collect{t ->t.toString().padLeft(2,'0')} }
       table-strategy:
         standard:
           shardingColumn: action_time
           preciseAlgorithmClassName: com.XXXXX.DatePreciseShardingAlgorithm

有么有中招的小伙伴？

----------------分割线-------------
主从权限没设定好，正常从库的用户账号权限应该设置成只读权限，保证不会因修改从库造成，主从复制错误的问题，我遇到的就是在从库中增加了字段数据，又返回主库添加字段数据，造成从库已经有这个字段跟主库冲突了，必须控制权限，才能彻底解决问题。
** 解决方式：**
第一步：查看从数据库状态：

mysql> show  slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.5
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000018
          Read_Master_Log_Pos: 459455617
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 459420641
        Relay_Master_Log_File: mysql-bin.000018
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: seal_sign_record
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1060
                   Last_Error: Error 'Duplicate column name 'seal_type'' on query. Default database: 'seal_sign_record'. Query: 'alter table ps_seal_log_202107 add seal_type tinyint(2) DEFAULT NULL COMMENT '类型''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 459420983
              Relay_Log_Space: 459455486
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1060
               Last_SQL_Error: Error 'Duplicate column name 'seal_type'' on query. Default database: 'seal_sign_record'. Query: 'alter table ps_seal_log_202107 add seal_type tinyint(2) DEFAULT NULL COMMENT '类型''
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: 9e968da7-05b0-11ea-a9b4-98be9446c9a2
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 210730 03:23:40
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 9e968da7-05b0-11ea-a9b4-98be9446c9a2:4214744-4214887
            Executed_Gtid_Set: 18d7f02c-c861-11eb-86fd-b4055d9da070:1-68781027,
9e968da7-05b0-11ea-a9b4-98be9446c9a2:4214744-4214810
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

第二步：找到错误码1060
常见一些error code代表的错误如下：

错误码	错误描述
1007	数据库已存在，创建数据库失败
1008：	数据库不存在，删除数据库失败
1050：	数据表已存在，创建数据表失败
1051：	数据表不存在，删除数据表失败
1054：	字段不存在，或程序文件跟数据库有冲突
1060：	字段重复，导致无法插入
1061：	重复键名
1068：	定义了多个主键
1094：	位置线程ID
1146：	数据表缺失，请恢复数据库
1053：	复制过程中主服务器宕机
1062：	主键冲突 Duplicate entry '%s' for key %d

第三部：删除从库中增加的字段，按照以下步骤，让从库跳过这个错误事物
他可以有两种方式跳过，先说的是GTID模式，这一种是用得比较多的，注入空事务跳过：

#找到冲突的GTID号，Executed_Gtid_Set
mysql>show slave status\G
    。
    。
    。
Executed_Gtid_Set: 18d7f02c-c861-11eb-86fd-b4055d9da070:1-68781030
    。
    。
    。
#停止复制
mysql>stop slave;
#然后执行设置一个事务GTID来跳过，因为我们就是卡在这里,所以要跳过68781030这个事务的意思，记住去掉 1-  不然报错
mysql> SET gtid_next = '18d7f02c-c861-11eb-86fd-b4055d9da070:68781030';
#注入空事务
mysql>  BEGIN;COMMIT;
#把GTID设置回自动模式
mysql> set gtid_next = 'AUTOMATIC';
#重新开启复制
mysql> start slave;
#这就可以跳过一个事务了,原理在于通过执行一个空事务代替master传递过来的冲突事务.

操作如下图：

Sharding sphere实现读写分离+分库分表

第一步：数据库的主从配置

1、Master(主)数据库节点的配置，进入/etc/my.cnf中(Master节点服务器上)：

2、Slave(从)数据库节点配置，进入/etc/my.cnf (Slave节点服务器上)：

3、在Master节点授权Slave节点,拥有同步权限(在Master节点服务器上执行)：

4、在Slave节点上，关联Master节点(在Slave节点服务器上执行)：

第二步：项目搭建，并引入相关jar包，增加相关配置，注意有大坑

还有想实现分库分表，都需要提前创建好库表，想实现动态创建，我查了一下，追溯了源码，没找到好的方式，如果有好的方式请留言告知！

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读