SpringBoot整合Debezium实现对MySQL实时监控

一、简介(Debezium)

官网：Debezium 点击跳转

原文：Debezium is a set of distributed services to capture changes in your databases so that your applications can see those changes and respond to them. Debezium records all row-level changes within each database table in a change event stream, and applications simply read these streams to see the change events in the same order in which they occurred.

译文：Debezium 是一组分布式服务，用于捕获数据库中的更改，以便您的应用程序可以看到这些更改并做出响应。Debezium 将每个数据库表中的所有行级更改记录在更改事件流中，应用程序只需读取这些流即可按照它们发生的相同顺序查看更改事件。

二、效果演示

通过Navicat修改数据库中数据时，能够查看控制台输出;

三、技术架构

1. Java 11(最低要求)
2. SpringBoot 2.2.6.RELEASE
3. MySQL 5.7.29 (需开启binlog)

四、环境搭建

1. MySQL选用Docker搭建

    docker pull mysql:5.7.29
    //端口映射为 13306 需要注意
    docker run -itd --name mysql -p 13306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7.29
   

2. 数据库与表结构如下图：

   

3. 开启Mysql binlog(损耗1%性能)

    1) 登录MySQL
    >mysql -uroot -p123456 -P 13306
    
    2) 查看binlog (当前binlog为关闭状态，需开启binlog)
    >show variables like '%log_bin%'
    +---------------------------------+-------+
    | Variable_name                   | Value |
    +---------------------------------+-------+
    | log_bin                         | OFF   |
    | log_bin_basename                |       |
    | log_bin_index                   |       |
    | log_bin_trust_function_creators | OFF   |
    | log_bin_use_v1_row_events       | OFF   |
    | sql_log_bin                     | ON    |
    +---------------------------------+-------+
    
    3) 查看容器
    >docker ps
    CONTAINER ID   IMAGE          COMMAND                  CREATED          STATUS          PORTS                                		NAMES
    351861e0869f   mysql:5.7.29   "docker-entrypoint.s…"   36 minutes ago   Up 36 minutes   33060/tcp, 0.0.0.0:13306->3306/tcp   mysql
    
    4) 进入容器
    >docker exec -it 351861e0869f /bin/bash
    
    5) 安装Vim(用于修改配置)
    >apt-get update
    >apt-get install vim
    
    6) 修改配置，开启binlog
    >vim /etc/mysql/my.cnf
    
    7) 加入以下配置
    [mysqld]
    server-id=1
    log-bin=/var/lib/mysql/mysql-bin
    
    8) 重启容器即可
    >docker restart 351861e0869f   
   

五、实现原理

官网教程:嵌入式开发

本文章采用嵌入式开发，即将Debezium引入至SpringBoot项目中进行监控，无需搭建zookeeper，kafka等组件；

Debezium通过监控binlog日志获取改变的数据，并通过回调方法输出内容；

五、核心源码

1. 配置文件application.yml

   database与table为需要监控的数据库与表，格式为数组形式，可根据需求自行多级配置
   

   server:
     port: 8888
   debezium:
     database:
       - name: qm
         enabled: true
         serverId: 1
         host: 127.0.0.1
         port: 13306
         username: root
         password: 123456
         offset-path: debezium/qm/offset.dat
         history-path: debezium/qm/dbhistory.dat
         table:
           - qm.user
   
   

2. 读取配置文件 MysqlConfig.class

   读取yml中配置并生成Properties集合对象
   

   @Configuration
   @ConfigurationProperties(prefix = "debezium")
   public class MysqlConfig {
   
       private List<DatabaseData> database;
   
       public void setDatabase(List<DatabaseData> database) {
           this.database = database;
       }
   
       @Bean
       public List<String> tableList() {
           List<String> list = new ArrayList<>();
           for (DatabaseData dbd : database) {
               for (String str : dbd.getTable()) {
                   list.add(str);
               }
           }
           return list;
       }
   
       @Bean
       public List<Properties> mysqlProperties() {
           List<Properties> list = new ArrayList<>();
   
           String osName = System.getProperty("os.name");
           String path = "/";
           if (osName.startsWith("Windows")) {
               path = "D:/";
           } else if (osName.startsWith("Linux")) {
               path = "/opt/";
           }
   
           for (DatabaseData dbd : database) {
               //保存路径重新赋值
               dbd.setOffsetPath(path + dbd.getOffsetPath());
               dbd.setHistoryPath(path + dbd.getHistoryPath());
   
               Properties props = new Properties();
               props.setProperty("name", dbd.getName());
               props.setProperty("connector.class", "io.debezium.connector.mysql.MySqlConnector");
               props.setProperty("offset.storage", "org.apache.kafka.connect.storage.FileOffsetBackingStore");
               props.setProperty("offset.storage.file.filename", dbd.getOffsetPath());
               props.setProperty("offset.flush.interval.ms", "600000");
               props.setProperty("database.hostname", dbd.getHost());
               props.setProperty("database.port", dbd.getPort());
               props.setProperty("database.user", dbd.getUsername());
               props.setProperty("database.password", dbd.getPassword());
               props.setProperty("database.server.id", dbd.getServerId());
               props.setProperty("database.server.name", "my_mysql_connector" + dbd.getName());
               props.setProperty("database.history",
                       "io.debezium.relational.history.FileDatabaseHistory");
               props.setProperty("database.history.file.filename", dbd.getHistoryPath());
               String tableList = dbd.getTable().stream().map(item -> item.indexOf("&") > -1 ? item.substring(0, item.indexOf("&")) : item).collect(Collectors.joining(","));
               props.setProperty("table.include.list", tableList);
               list.add(props);
           }
           return list;
       }
   
   }
   

3. MySQL监控 MysqlListener.class

    跟随SpringBoot启动与销毁，读取配置Properties进行监控，触发receiveChangeEvent回调方法
   

@Slf4j
@Component
public class MysqlListener {

    @Resource
    private MessageService messageService;

    private final List<DebeziumEngine<ChangeEvent<String, String>>> engineList = new ArrayList<>();

    private MysqlListener(@Qualifier("mysqlProperties") List<Properties> list) {
        for (Properties props : list) {
            this.engineList.add(DebeziumEngine.create(Json.class)
                    .using(props)
                    .notifying(record -> {
                        receiveChangeEvent(record.value(), props.getProperty("debezium.name"));
                    }).build());
        }
    }

    private void receiveChangeEvent(String value, String name) {
        if (Objects.nonNull(value)) {
            Map<String, Object> payload = JSONUtils.getPayload(value);
            String handleType = JSONUtils.getHandleType(payload);
            if (!("NONE".equals(handleType) || "READ".equals(handleType))) {
                ChangeData changeData = JSONUtils.getChangeData(payload);
                Map<String, Object> data;
                if ("DELETE".equals(handleType)) {
                    data = changeData.getBefore();
                } else {
                    data = changeData.getAfter();
                }
                Message build = Message.builder()
                        .data(data)
                        .dbType("MySQL")
                        .database(String.valueOf(changeData.getSource().get("db")))
                        .table(String.valueOf(changeData.getSource().get("table")))
                        .handleType(handleType)
                        .build();
                log.info("【Debezium-" + name + "】" + build.toString());
                messageService.sendMessage(build);
            }
        }
    }

    @PostConstruct
    private void start() {
        for (DebeziumEngine<ChangeEvent<String, String>> engine : engineList) {
            Executors.newSingleThreadExecutor().execute(engine);
        }
    }

    @PreDestroy
    private void stop() {
        for (DebeziumEngine<ChangeEvent<String, String>> engine : engineList) {
            if (engine != null) {
                try {
                    engine.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

六、开源地址

GitHub:https://github.com/fsyxjwxw/MySQL-Listener 点击跳转