Sharding-Jdbc学习笔记二之读写分离项目配置篇

mysql配置安装及数据库准备参考 上一篇

2. 项目配置

参考手册

2.1 application.yml中配置

使用druid连接池， 经过测试， 如果要配置druid的filter还是保持和原来一样的配置将这部分信息配置在spring.datasource.druid下

现在使用了shardingsphere，所以其他连接具体信息需要配置在spring.shardingsphere.datasource下

#######################################################读写分离配置###############################################################
 
# mysql master连接信息
master.mysql.host: localhost
master.mysql.port: 3306
master.mysql.db: boot-quick
master.mysql.username: root
master.mysql.password: 123456
 
# mysql slave0连接信息
slave0.mysql.host: localhost
slave0.mysql.port: 3307
slave0.mysql.db: boot-quick
slave0.mysql.username: root
slave0.mysql.password: 123456
 
# mysql slave0连接信息
slave1.mysql.host: localhost
slave1.mysql.port: 3308
slave1.mysql.db: boot-quick
slave1.mysql.username: root
slave1.mysql.password: 123456
 
 
spring:
  main:
    allow-bean-definition-overriding: true
 
  # 经测试filter需要配置再datasource.druid下，而每个连接的信息还是得配置在sharding-sphere下的datasource每个自己的
  datasource:
    druid:
      filter:
        stat:
          enabled: true
          log-slow-sql: true
          slow-sql-millis: 3000
        wall:
          enabled: true # 开启WallFilter
          db-type: mysql
      ## 开启内置监控界面 访问路径: /context-path/druid/index.html
      stat-view-servlet:
        enabled: true
        url-pattern: /druid/*
        reset-enable: true
        login-username: admin
        login-password: 123456
 
  shardingsphere:
    datasource:
      names: master,slave0,slave1
      # 配置数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://${master.mysql.host}:${master.mysql.port}/${master.mysql.db}?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&tinyInt1isBit=false
        username: ${master.mysql.username}
        password: ${master.mysql.password}
        initial-size: 5
        asyncInit: true
        max-active: 30
        min-idle: 10
        keep-alive: true
        max-wait: 60000
        use-unfair-lock: true
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 600000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: true
        test-on-return: false
        poolPreparedStatements: false
        max-open-prepared-statements: 20
      # 配置数据源
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://${slave0.mysql.host}:${slave0.mysql.port}/${slave0.mysql.db}?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&tinyInt1isBit=false
        username: ${slave0.mysql.username}
        password: ${slave0.mysql.password}
        initial-size: 5
        asyncInit: true
        max-active: 30
        min-idle: 10
        keep-alive: true
        max-wait: 60000
        use-unfair-lock: true
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 600000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: true
        test-on-return: false
        poolPreparedStatements: false
        max-open-prepared-statements: 20
      # 配置数据源
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://${slave1.mysql.host}:${slave1.mysql.port}/${slave1.mysql.db}?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&tinyInt1isBit=false
        username: ${slave1.mysql.username}
        password: ${slave1.mysql.password}
        initial-size: 5
        asyncInit: true
        max-active: 30
        min-idle: 10
        keep-alive: true
        max-wait: 60000
        use-unfair-lock: true
        time-between-eviction-runs-millis: 60000
        min-evictable-idle-time-millis: 600000
        validation-query: SELECT 1
        test-while-idle: true
        test-on-borrow: true
        test-on-return: false
        poolPreparedStatements: false
        max-open-prepared-statements: 20
 
    # 配置主从规则
    masterslave:
      # 从库负载均衡算法
      load-balance-algorithm-type: round_robin
      # 读写分离数据源名称
      name: ms
      # 主库数据源名称， 从上面配置的数据源中选择
      master-data-source-name: master
      # 从库数据源名称列表， 从上面配置的数据源中选择
      slave-data-source-names: slave0,slave1
 
    props:
      #  显示sql具体信息
      sql.show: true
 

3. 项目测试

项目搭建以及crud代码和单表操作没有一点区别，因此测试代码省略

• 插入测试从从库查询， 插入则操作主库

  

  

• 列表查询测试, 查询从从库查询数据

  

4. 错误汇总

1. 在master删除的数据，在slave节点不存在

   Could not execute Delete_rows event on table boot-quick.auth_user; Can't find record in 'auth_user', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log WIN-NQ0GCAPFCPP-bin.000017, end_log_pos 2234
   

   临时解决,

   按照上面的错误先在主库查看binlog日志后面要同步的数量

   SHOW BINLOG EVENTS in 'WIN-NQ0GCAPFCPP-bin.000017' from 2997;
   

   然后 进入到slave，先停下slave,然后跳过错误数量， 再重启slave

   stop slave;
   set global sql_slave_skip_counter=1;
   start slave;
   

   永久解决， 根据错误代码(上面那段错误就包含了Error_code: 1032)，在从库配置文件中跳过对应的错误

   slave-skip-errors=1032
   

5. 主从同步原理

• 主节点

  1、当主节点上进行 insert、update、delete 操作时，会按照时间先后顺序写入到 binlog 中；
  2、当从节点连接到主节点时，主节点会创建一个叫做 binlog dump 的线程；

  3、一个主节点有多少个从节点，就会创建多少个 binlog dump 线程；

  4、当主节点的binlog 发生变化的时候，也就是进行了更改操作，binlog dump 线程就会通知从节点 (Push模式)，并将相应的 binlog 内容发送给从节点；

• 从节点

  当开启主从同步的时候，从节点会创建两个线程用来完成数据同步的工作。

  I/O线程： 此线程连接到主节点，主节点上的 binlog dump 线程会将 binlog 的内容发送给此线程。此线程接收到 binlog 内容后，再将内容写入到本地的 relay log。

  SQL线程： 该线程读取 I/O 线程写入的 relay log，并且根据 relay log 的内容对从数据库做对应的操作。

项目通用配置和项目错误汇总

• 默认连接未配置，刚开始使用的版本没有这个问题，后来升到了4.1.1有这个问题

  Action:
  
  Consider the following:
      If you want an embedded database (H2, HSQL or Derby), please put it on the classpath.
      If you have database settings to be loaded from a particular profile you may need to activate it (no profiles are currently active).
  

  则需要在主启动类上排除当前项目所依赖的连接池的自动配置类，如使用的druid

  @SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
  

• datasource已定义

  Description:
  
  The bean 'dataSource', defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [com/alibaba/druid/spring/boot/autoconfigure/DruidDataSourceAutoConfigure.class] and overriding is disabled.
  
  Action:
  
  Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true
  

  则按照上面的提示，加上配置

  spring:
    main:
      allow-bean-definition-overriding: true