监听bin_log日志达到数据的一致性

利用RabbitMQ通过监听bin_log日志达到数据的一致性

一、配置信息

spring:
  #redis
  redis:
    database: 0
    host: 192.168.11.93
    port: 6379
    #timeout: 43200
    timeout: 86400
    jedis:
      pool:
        max-active: 8
        max-wait: -1
        max-idle: 8
        min-idle: 0
    password: root
  rabbitmq:
    host: ${RABBIT_MQ_HOST:192.168.11.93}
    port: ${RABBIT_MQ_PORT:5672}
    virtualHost: /
    username: guest
    password: guest
  datasource:
    master:
      url: jdbc:mysql://192.168.11.246/tb_books?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
      username: root
      password: 123456
      #连接池的配置信息
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.jdbc.Driver
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,slf4j
      initialSize: 10
      minIdle: 10
      maxActive: 100
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
binlog:
  columns: # 订阅binlog数据库连接信息，ip，端口，用户密码（用户必须要有权限）
    host: 192.168.11.93
    port: 3306
    username: root
    passwd: 123456
  db: tb_books # 监听数据库
  table: tb_goods #监听表结构

二、bin_log监听配置信息

参数取值配置文件中的数据

1、监听配置信息

/**
 * 监听配置信息
 *
 * @author zrj
 * @since 2021/7/27
 **/
@Data
@Component
public class BinLogConstants {

  @Value("${binlog.columns.host}")
  private String host;

  @Value("${binlog.columns.port}")
  private int port;

  @Value("${binlog.columns.username}")
  private String username;

  @Value("${binlog.columns.passwd}")
  private String passwd;

  @Value("${binlog.db}")
  private String db;

  @Value("${binlog.table}")
  private String table;

  public static final int consumerThreads = 5;

  public static final long queueSleep = 1000;

}

2、bin_log对象

/**
 * binlog对象
 *
 * @author zrj
 * @since 2021/7/26
 **/
@Data
public class BinLogItem implements Serializable {

  private static final long serialVersionUID = 5503152746318421290L;

  private String dbTable;
  private EventType eventType;
  private Long timestamp = null;
  private Long serverId = null;
  // 存储字段-之前的值之后的值
  private Map<String, Serializable> before = null;
  private Map<String, Serializable> after = null;
  // 存储字段--类型
  private Map<String, Colum> colums = null;

  /**
   * 新增或者删除操作数据格式化
   */
  public static BinLogItem itemFromInsertOrDeleted(Serializable[] row, Map<String, Colum> columMap, EventType eventType) {
    if (null == row || null == columMap) {
      return null;
    }
    if (row.length != columMap.size()) {
      return null;
    }
    // 初始化Item
    BinLogItem item = new BinLogItem();
    item.eventType = eventType;
    item.colums = columMap;
    item.before = Maps.newHashMap();
    item.after = Maps.newHashMap();

    Map<String, Serializable> beOrAf = Maps.newHashMap();

    columMap.entrySet().forEach(entry -> {
      String key = entry.getKey();
      Colum colum = entry.getValue();
      beOrAf.put(key, row[colum.inx]);
    });

    // 写操作放after，删操作放before
    if (isWrite(eventType)) {
      item.after = beOrAf;
    }
    if (isDelete(eventType)) {
      item.before = beOrAf;
    }

    return item;
  }

  /**
   * 更新操作数据格式化
   */
  public static BinLogItem itemFromUpdate(Map.Entry<Serializable[], Serializable[]> mapEntry, Map<String, Colum> columMap, EventType eventType) {
    if (null == mapEntry || null == columMap) {
      return null;
    }
    // 初始化Item
    BinLogItem item = new BinLogItem();
    item.eventType = eventType;
    item.colums = columMap;
    item.before = Maps.newHashMap();
    item.after = Maps.newHashMap();

    Map<String, Serializable> be = Maps.newHashMap();
    Map<String, Serializable> af = Maps.newHashMap();

    columMap.entrySet().forEach(entry -> {
      String key = entry.getKey();
      Colum colum = entry.getValue();
      be.put(key, mapEntry.getKey()[colum.inx]);

      af.put(key, mapEntry.getValue()[colum.inx]);
    });

    item.before = be;
    item.after = af;
    return item;
  }


}

3、监听工具、字段属性对象

（1）、监听工具

/**
 * 监听工具
 *
 * @author zrj
 * @since 2021/7/27
 **/
@Slf4j
@Component
public class BinLogUtils {

  private static BinLogUtils binLogUtils;

  @PostConstruct
  public void init() {
    binLogUtils = this;
//    binLogUtils.searchStoreLogoExtMapper = this.searchStoreLogoExtMapper;
  }

  /**
   * 拼接dbTable
   */
  public static String getdbTable(String db, String table) {
    return db + "-" + table;
  }

  /**
   * 获取columns集合
   */
  public static Map<String, Colum> getColMap(Conf conf, String db, String table) throws ClassNotFoundException {
    try {
      Class.forName("com.mysql.jdbc.Driver");
      // 保存当前注册的表的colum信息
      String url = "jdbc:mysql://192.168.11.246/tb_books?user=root&password=jtWYnRUCBY4fYHKh&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
      Connection connection = DriverManager
          .getConnection(url);
      // 执行sql
      String preSql = "SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? and TABLE_NAME = ?";
      PreparedStatement ps = connection.prepareStatement(preSql);
      ps.setString(1, db);
      ps.setString(2, table);
      ResultSet rs = ps.executeQuery();
      Map<String, Colum> map = new HashMap<>(rs.getRow());
      while (rs.next()) {
        String schema = rs.getString("TABLE_SCHEMA");
        String tableName = rs.getString("TABLE_NAME");
        String column = rs.getString("COLUMN_NAME");
        int idx = rs.getInt("ORDINAL_POSITION");
        String dataType = rs.getString("DATA_TYPE");
        if (column != null && idx >= 1) {
          map.put(column, new Colum(schema, tableName, idx - 1, column, dataType)); // sql的位置从1开始
        }
      }
      ps.close();
      rs.close();
      return map;
    } catch (SQLException e) {
      log.error("load db conf error, db_table={}:{} ", db, table, e);
    }
    return null;
  }

  /**
   * 根据DBTable获取table
   *
   * @param dbTable
   * @return java.lang.String
   */
  public static String getTable(String dbTable) {
    if (StrUtil.isEmpty(dbTable)) {
      return "";
    }
    String[] split = dbTable.split("-");
    if (split.length == 2) {
      return split[1];
    }
    return "";
  }

  /**
   * 将逗号拼接字符串转List
   *
   * @param str
   * @return
   */
  public static List<String> getListByStr(String str) {
    if (StrUtil.isEmpty(str)) {
      return Lists.newArrayList();
    }

    return Arrays.asList(str.split(","));
  }

  /**
   * 根据操作类型获取对应集合
   *
   * @param binLogItem
   * @return
   */
  public static Map<String, Serializable> getOptMap(BinLogItem binLogItem) {
    // 获取操作类型
    EventType eventType = binLogItem.getEventType();
    if (isWrite(eventType) || isUpdate(eventType)) {
      return binLogItem.getAfter();
    }
    if (isDelete(eventType)) {
      return binLogItem.getBefore();
    }
    return null;
  }

  /**
   * 获取操作类型
   *
   * @param binLogItem
   * @return
   */
  public static Integer getOptType(BinLogItem binLogItem) {
    // 获取操作类型
    EventType eventType = binLogItem.getEventType();
    if (isWrite(eventType)) {
      return 1;
    }
    if (isUpdate(eventType)) {
      return 2;
    }
    if (isDelete(eventType)) {
      return 3;
    }
    return null;
  }


  /**
   * 根据storeId获取imgUrl
   */
  public static String getImgUrl(Long storeId) {

    if (storeId == null) {
      return "";
    }

    return "";
  }

  /**
   * 格式化date
   *
   * @param date
   * @return java.util.Date
   */
  public static Date getDateFormat(Date date) {
    if (date == null) {
      return null;
    }
    String dateFormat = "yyyy-MM-dd HH:mm:ss";
    String strDate = DateUtil.format(date, dateFormat);
    if (StrUtil.isEmpty(strDate)) {
      return null;
    }

    Date formatDate = DateUtil.parse(strDate, dateFormat);
    return formatDate;
  }
}

（2）、字段属性

/**
 * 字段属性对象
 *
 * @author zrj
 * @since 2021/7/27
 **/
@Data
public class Colum {

  public int inx;
  public String colName; // 列名
  public String dataType; // 类型
  public String schema; // 数据库
  public String table; // 表

  public Colum(String schema, String table, int idx, String colName, String dataType) {
    this.schema = schema;
    this.table = table;
    this.colName = colName;
    this.dataType = dataType;
    this.inx = idx;
  }

}

（3）、数据库配置

/**
 * 数据库配置
 *
 * @author zrj
 * @since 2021/7/27
 **/
@Data
@AllArgsConstructor
public class Conf {

  private String host;
  private int port;
  private String username;
  private String passwd;

}

三、监听器

/**
 * BinLogListener监听器
 *
 * @author zrj
 * @since 2021/7/26
 **/
@FunctionalInterface
public interface BinLogListener {

  void onEvent(BinLogItem item);

}
/**
 * 数据库监听器
 *
 * @author zrj
 * @since 2021/7/26
 **/
@Slf4j
public class MysqlBinLogListener implements BinaryLogClient.EventListener {

  @Option(name = "-binlog-consume_threads", usage = "the thread num of consumer")
  private int consumerThreads = BinLogConstants.consumerThreads;

  private BinaryLogClient parseClient;

  private BlockingQueue<BinLogItem> queue;
  private final ExecutorService consumer;

  // 存放每张数据表对应的listener
  private Multimap<String, BinLogListener> listeners;

  private Conf conf;
  private Map<String, Map<String, Colum>> dbTableCols;
  private String dbTable;

  /**
   * 监听器初始化
   *
   * @param conf
   */
  public MysqlBinLogListener(Conf conf) {
    BinaryLogClient client = new BinaryLogClient(conf.getHost(), conf.getPort(), conf.getUsername(), conf.getPasswd());
    EventDeserializer eventDeserializer = new EventDeserializer();
    //eventDeserializer.setCompatibilityMode(//序列化
    //        EventDeserializer.CompatibilityMode.DATE_AND_TIME_AS_LONG,
    //        EventDeserializer.CompatibilityMode.CHAR_AND_BINARY_AS_BYTE_ARRAY
    //);
    client.setEventDeserializer(eventDeserializer);
    this.parseClient = client;
    this.queue = new ArrayBlockingQueue<>(1024);
    this.conf = conf;
    this.listeners = ArrayListMultimap.create();
    this.dbTableCols = new ConcurrentHashMap<>();
    this.consumer = Executors.newFixedThreadPool(consumerThreads);
  }

  /**
   * 监听处理
   *
   * @param event
   */
  @Override
  public void onEvent(Event event) {
    EventType eventType = event.getHeader().getEventType();

    if (eventType == EventType.TABLE_MAP) {
      TableMapEventData tableData = event.getData();
      String db = tableData.getDatabase();
      String table = tableData.getTable();
      dbTable = getdbTable(db, table);
    }

    // 只处理添加删除更新三种操作
    if (isWrite(eventType) || isUpdate(eventType) || isDelete(eventType)) {
      if (isWrite(eventType)) {
        WriteRowsEventData data = event.getData();
        for (Serializable[] row : data.getRows()) {
          if (dbTableCols.containsKey(dbTable)) {
            BinLogItem item = BinLogItem.itemFromInsertOrDeleted(row, dbTableCols.get(dbTable), eventType);
            item.setDbTable(dbTable);
            queue.add(item);
          }
        }
      }
      if (isUpdate(eventType)) {
        UpdateRowsEventData data = event.getData();
        for (Map.Entry<Serializable[], Serializable[]> row : data.getRows()) {
          if (dbTableCols.containsKey(dbTable)) {
            BinLogItem item = BinLogItem.itemFromUpdate(row, dbTableCols.get(dbTable), eventType);
            item.setDbTable(dbTable);
            queue.add(item);
          }
        }

      }
      if (isDelete(eventType)) {
        DeleteRowsEventData data = event.getData();
        for (Serializable[] row : data.getRows()) {
          if (dbTableCols.containsKey(dbTable)) {
            BinLogItem item = BinLogItem.itemFromInsertOrDeleted(row, dbTableCols.get(dbTable), eventType);
            item.setDbTable(dbTable);
            queue.add(item);
          }
        }
      }
    }
  }

  /**
   * 注册监听
   *
   * @param db       数据库
   * @param table    操作表
   * @param listener 监听器
   * @throws Exception
   */
  public void regListener(String db, String table, BinLogListener listener) throws Exception {
    String dbTable = getdbTable(db, table);
    // 获取字段集合
    Map<String, Colum> cols = getColMap(conf, db, table);
    // 保存字段信息
    dbTableCols.put(dbTable, cols);
    // 保存当前注册的listener
    listeners.put(dbTable, listener);
  }

  /**
   * 开启多线程消费
   *
   * @throws IOException
   */
  public void parse() throws IOException {
    parseClient.registerEventListener(this);

    for (int i = 0; i < consumerThreads; i++) {
      consumer.submit(() -> {
        while (true) {
          if (queue.size() > 0) {
            try {
              BinLogItem item = queue.take();
              String dbtable = item.getDbTable();
              listeners.get(dbtable).forEach(binLogListener -> binLogListener.onEvent(item));
            } catch (InterruptedException e) {
              e.printStackTrace();
            }
          }
          Thread.sleep(BinLogConstants.queueSleep);
        }
      });
    }
    parseClient.connect();
  }

}

1、监听器注册监听

1、通过bin_log日志的新增、修改、删除等操作来进行监听并对RabbitMQ进行处理（bin_log日志的增删改查是可配置项）
2、通过将key键值向RabbitMQ进行注册，产生生产者，通过多线程的方式来进行注册，并消费。

/**
 * 监听器
 * SpringBoot启动成功后的执行业务线程操作
 * CommandLineRunner去实现此操作
 * 在有多个可被执行的业务时，通过使用 @Order 注解，设置各个线程的启动顺序（value值由小到大表示启动顺序）。
 * 多个实现CommandLineRunner接口的类必须要设置启动顺序，不让程序启动会报错！
 *
 * @author zrj
 * @since 2021/7/27
 **/
@Slf4j
@Component
@Order(value = 1)
public class TourBinLogListener implements CommandLineRunner {

  @Autowired
  private BinLogConstants binLogConstants;

  @Autowired
  private RabbitTemplate rabbitTemplate;

  @Override
  public void run(String... args) throws Exception {
    log.info("初始化配置信息：" + binLogConstants.toString());

    // 初始化配置信息
    Conf conf = new Conf(binLogConstants.getHost(), binLogConstants.getPort(), binLogConstants.getUsername(), binLogConstants.getPasswd());

    // 初始化监听器
    MysqlBinLogListener mysqlBinLogListener = new MysqlBinLogListener(conf);

    // 获取table集合
    List<String> tableList = BinLogUtils.getListByStr(binLogConstants.getTable());
    if (CollectionUtil.isEmpty(tableList)) {
      return;
    }
    // 注册监听
    tableList.forEach(table -> {
      log.info("注册监听信息，注册DB：" + binLogConstants.getDb() + "，注册表：" + table);
      try {
        mysqlBinLogListener.regListener(binLogConstants.getDb(), table, item -> {
          System.out.println(item.getAfter());
          rabbitTemplate.convertAndSend("bin_log", "mysql.bin.log", item.getAfter());
          log.info("监听逻辑处理");
        });
      } catch (Exception e) {
        log.error("BinLog监听异常：" + e);
      }
    });
    // 多线程消费
    mysqlBinLogListener.parse();
  }
}

四、RabbitMQ配置信息

1、依赖导入

 <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.0</version>
        <relativePath/> <!-- lookup parent from repository -->
 </parent>
<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.5.4</version>
            <scope>compile</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/args4j/args4j -->
        <dependency>
            <groupId>args4j</groupId>
            <artifactId>args4j</artifactId>
            <version>2.33</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.google.guava/guava -->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>28.1-jre</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.github.shyiko/mysql-binlog-connector-java -->
        <dependency>
            <groupId>com.github.shyiko</groupId>
            <artifactId>mysql-binlog-connector-java</artifactId>
            <version>0.17.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.google.code.gson/gson -->
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.8.5</version>
        </dependency>
    </dependencies>

2、配置信息，消费者（通过redis清楚缓存）

@Configuration
public class RabbitMqConfiguration {
  @Bean(name = "exchange")
  public Declarable baseExchange() {
    return new TopicExchange("bin_log", true, false);
  }
}

/**
 * 消费者
 */
@Service
public class RabbitMqCosm {

  @Autowired
  private RedisTemplate redisTemplate;

  @RabbitListener(bindings = @QueueBinding(
      value = @Queue(value = "bin_log", durable = "false", autoDelete = "true"),
      exchange = @Exchange(value = "bin_log", type = ExchangeTypes.TOPIC),
      key = "mysql.bin.log"))
  public void binLog(LinkedHashMap map) {
    // 删除缓存操作
    redisTemplate.delete("selectBorrowList::"+map.get("prison_house_id"));
    System.out.println(map);
  }
}

五、Redis配置信息

注意：启动类以及监听类需要打开监听配置（本地缓存、Redis选其一。本人是用Redis进行缓存处理）

1、Redis监听类

@Configuration
@EnableCaching // 配置开启
public class RedisConfig extends CachingConfigurerSupport {

  @Bean
  public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory connectionFactory) {
    // 创建 RedisTemplate 对象
    RedisTemplate<String, Object> template = new RedisTemplate<>();
    // 设置连接工厂
    template.setConnectionFactory(connectionFactory);
    // 创建 JSON 序列化工具
    GenericJackson2JsonRedisSerializer jsonRedisSerializer = new GenericJackson2JsonRedisSerializer();
    // 设置 key 的序列化
    template.setKeySerializer(RedisSerializer.string());
    template.setHashKeySerializer(RedisSerializer.string());
    // 设置 value 的序列化
    template.setValueSerializer(jsonRedisSerializer);
    template.setHashValueSerializer(jsonRedisSerializer);
    // 返回
    return template;
  }

  @Bean
  public CacheManager cacheManager(RedisConnectionFactory factory) {
    RedisCacheConfiguration config = RedisCacheConfiguration.defaultCacheConfig()
            .entryTtl(Duration.ofSeconds(60000000)) // 60s缓存失效
            // 设置key的序列化方式
            .serializeKeysWith(RedisSerializationContext.SerializationPair.fromSerializer(keySerializer()))
            // 设置value的序列化方式
            .serializeValuesWith(RedisSerializationContext.SerializationPair.fromSerializer(valueSerializer()))
            // 不缓存null值
            .disableCachingNullValues();

          RedisCacheManager redisCacheManager = RedisCacheManager.builder(factory)
                  .cacheDefaults(config)
                  .transactionAware()
                  .build();

    return redisCacheManager;
  }

  // key键序列化方式
  private RedisSerializer<String> keySerializer() {
    return new StringRedisSerializer();
  }

  // value值序列化方式
  private GenericJackson2JsonRedisSerializer valueSerializer(){
    return new GenericJackson2JsonRedisSerializer();
  }
}

2、本地缓存监听类

@Configuration
@EnableCaching
public class CacheConfig {
    @Bean
    public CacheManager cacheManager() {
        CaffeineCacheManager cacheManager = new CaffeineCacheManager();
        Caffeine<Object, Object> caffeine = Caffeine.newBuilder()
                //配置缓存失效策略
                .expireAfterWrite(10, TimeUnit.MINUTES)
                .maximumSize(5000);
        cacheManager.setCaffeine(caffeine);
        return cacheManager;
    }
}

总结：通过监听bin_log日志的增删改查对RabbitMQ生产者的产生，并通过多线程对RabbitMQ进行消费，在消费中对指定变更过得数据进行是否变更的校验，若有变更则清理缓存，未变更则保持不变。由此来达到数据库与缓存中数据的一致性。

若有不解请参考：https://blog.youkuaiyun.com/m0_37583655/article/details/119148470