springboot 监听mysql binlog

gradle 依赖

    implementation 'com.github.shyiko:mysql-binlog-connector-java:0.21.0'
    implementation 'mysql:mysql-connector-java:8.0.21'

元数据

@Component
public class BingLogMetadata {

    private String driver = "com.mysql.cj.jdbc.Driver";

    public static Map<String,Map<Integer,String>> database(BinLogConstants binLog) throws Exception{
        Map<String,Map<Integer,String>> metadata = new ConcurrentHashMap<>();
        List<String> table = binLog.getTables();
        if(Tool.isNotNull(table)){
            Map<String,List<String>> group = new ConcurrentHashMap<>();
            for (int i = 0; i < table.size(); i++) {
                String key = table.get(i);
                String[] split = key.split("\\"+Tool.POINT);
                if(null == split || split.length != 2){
                    throw new Exception("BinLog配置同步,类型错误 [库名.表名]。请正确配置:"+key);
                }
                String database = split[0];
                String tableName = split[1];
                List<String> list = group.get(database);
                if(null == list){
                    group.put(database, list = new ArrayList());
                }
                list.add(tableName);
            }
            Iterator<Map.Entry<String, List<String>>> iterator = group.entrySet().iterator();
            while (iterator.hasNext()){
                Map.Entry<String, List<String>> next = iterator.next();
                String key = next.getKey();
                List<String> value = next.getValue();
                Properties props = new Properties();
                props.setProperty("user", binLog.getUsername());
                props.setProperty("password", binLog.getPasswd());
                props.setProperty("remarks", "true");
                props.setProperty("useInformationSchema", "true");
                String url = "jdbc:mysql://"+binLog.getHost()+":"+binLog.getPort()+"/"+key+"?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8";
                Connection connection = DriverManager.getConnection(url, props);
                DatabaseMetaData metaData = connection.getMetaData();
                ResultSet tableRs = metaData.getTables(connection.getCatalog(), connection.getCatalog(), "%", new String[]{"TABLE"});
                while (tableRs.next()) {
                    String tableName = tableRs.getString("TABLE_NAME");
                    if(value.contains(tableName)){
                        Map<Integer,String> map = new HashMap<>();
                        metadata.put(key+"."+tableName,map);
                        ResultSet columnRs = metaData.getColumns(connection.getCatalog(), connection.getCatalog(), tableName, "%");
                        int i = 0;
                        while (columnRs.next()) {
                            map.put(Integer.valueOf(i),columnRs.getString("COLUMN_NAME"));
                            i++;
                        }
                    }
                }
            }
        }
        return metadata;
    }

}

配置类

@Data
@Component
@ConfigurationProperties(prefix = "binlog.datasource")
public class BinLogConstants {

    private String host;

    private int port;


    private String username;

    private String passwd;

    private String table;
    private List<String> tables;


    private Integer serverId;

    public static final int consumerThreads = 5;

    public static final long queueSleep = 1000;

    public List<String> getTables() {
        if (StringUtils.hasText(table)){
            tables = Arrays.asList(table.split(","));
        }
        return tables;
    }
}

工具类

@Getter
@AllArgsConstructor
public enum Event {
    UPDATE("UPDATE"),DELETE("DELETE"),WRITE("WRITE");
    private String key;
}

public class Tool {

    public final static String COMMA = ",";
    public final static String POINT = ".";

    public static boolean isNotNull(List records){
        return (null != records && records.size() > 0);
    }
    public static boolean isNull(List records){
        return !isNotNull(records);
    }

}

核心监听

@Slf4j
@Component
@Order(1)
public class BinaryLogClientRunner implements CommandLineRunner {
    @Autowired
    private BinLogConstants binLogConstants;

    private static volatile Map<String, TableData> map = new ConcurrentHashMap<>();

    @Override
    public void run(String... args) throws Exception {
        String host = binLogConstants.getHost();
        int port = binLogConstants.getPort();
        String username = binLogConstants.getUsername();
        String passwd = binLogConstants.getPasswd();
        Integer serverId = binLogConstants.getServerId();
        List<String> tableList = binLogConstants.getTables();
//        // 数据库下的表字段
        Map<String, Map<Integer, String>> metadata = BingLogMetadata.database(binLogConstants);
        /**
         * BinaryLogClient 类是 MySQL 提供的一个 Java 客户端，用于监听 MySQL 数据库的二进制日志（Binary Log），并实现实时的数据同步。
         * BinaryLogClient 库底层使用了 MySQL 原生协议和通信机制，能够准确地捕获到 MySQL 数据库中的变化事件，
         * 并将这些事件以异步回调的方式传递给应用程序进行处理
         */
        BinaryLogClient client = new BinaryLogClient(host, port, username, passwd);
        /**
         * EventDeserializer 是一个接口，主要用于将 Event 数据从序列化格式反序列化为对象格式
         */
        // 数据序列化为java 对象
        EventDeserializer eventDeserializer = new EventDeserializer();
        eventDeserializer.setCompatibilityMode(
                // 此模式下，时间戳表示为自 Unix 纪元（1970 年 1 月 1 日 UTC）以来的毫秒数。这与其他兼容模式不同，后者可能使用不同的格式来表示时间戳
                EventDeserializer.CompatibilityMode.DATE_AND_TIME_AS_LONG,
                // 在此模式下，通过使用字节数组来表示字符和二进制数据，同时保留其编码和格式信息。这使得在读取时更容易处理这些数据，并将它们重新还原为原始格式
                EventDeserializer.CompatibilityMode.CHAR_AND_BINARY_AS_BYTE_ARRAY
        );
        client.setEventDeserializer(eventDeserializer);
        // 在BinaryLogClient中，serverId属性是一个客户端与MySQL实例之间的唯一标识符
        // 如果有多个客户端同时订阅同一个MySQL实例上的二进制日志，那么每个客户端的serverId应该是唯一的，以避免冲突
        client.setServerId(serverId);

        // 监听db 库中的数据变化
        client.registerEventListener(event -> {
            EventHeader header = event.getHeader();
            EventType eventType = header.getEventType();
            TableData tableData = null;
            /**
             * TableMapEventData，通过它可以获取操作的数据库名称、表名称以及表的id。之所以我们要监听这个事件，
             * 是因为之后监听的实际操作中返回数据中包含了表的id，而没有表名等信息，
             * 所以如果我们想知道具体的操作是在哪一张表的话，就要先维护一个id与表的对应关系
             */
            if (eventType == EventType.TABLE_MAP) {
                TableMapEventData eventData = event.getData();
                long tableId = eventData.getTableId();
                String database = eventData.getDatabase();//库名
                String table = eventData.getTable();//表名称
                StringBuilder builder = new StringBuilder();
                builder.append(database).append(Tool.POINT).append(table);
                if (tableList.contains(builder.toString())) {
                    map.put(String.valueOf(tableId), TableData.builder().database(database).table(table).build());
                }
            }
            List<JSONObject> lists = null;
            if (EventType.isWrite(eventType)) {
                WriteRowsEventData data = event.getData();
                if (null != (tableData = isListener(data.getTableId(), tableList))) {
                    log.info("--------------新增--------------");
                    List<Serializable[]> rows = data.getRows();
                    lists = parseListenerList(rows, tableData, metadata, Event.WRITE);
                }
            } else if (EventType.isUpdate(eventType)) {
                UpdateRowsEventData data = event.getData();
                if (null != (tableData = isListener(data.getTableId(), tableList))) {
                    log.info("--------------修改--------------");
                    List<Map.Entry<Serializable[], Serializable[]>> rows = data.getRows();
                    lists = parseListener(rows, tableData, metadata, Event.UPDATE);
                }
            } else if (EventType.isDelete(eventType)) {
                DeleteRowsEventData data = event.getData();
                if (null != (tableData = isListener(data.getTableId(), tableList))) {
                    log.info("--------------删除--------------");
                    List<Serializable[]> rows = data.getRows();
                    lists = parseListenerList(rows, tableData, metadata, Event.DELETE);
                }
            }
            if (!CollectionUtils.isEmpty(lists)) {
                lists.stream().forEach(e -> log.info(e.toString()));
            }
        });
        try {
            client.connect();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Data
    @Builder
    @AllArgsConstructor
    public static class TableData {
        private String database;
        private String table;
        private String databaseTable;
    }

    private TableData isListener(long tableId, List<String> tableList) {
        TableData tableData = map.get(String.valueOf(tableId));
        if (null == tableData || Tool.isNull(tableList))
            return null;
        String database = tableData.getDatabase();
        String table = tableData.getTable();
        StringBuilder builder = new StringBuilder();
        builder.append(database).append(Tool.POINT).append(table);
        if (tableList.contains(builder.toString())) {
            tableData.setDatabaseTable(builder.toString());
            return tableData;
        }
        return null;
    }

    private List<JSONObject> parseListenerList(List<Serializable[]> rows, TableData tableData, Map<String, Map<Integer, String>> metadata, Event event) {
        Map<Integer, String> map = metadata.get(tableData.getDatabaseTable());
        if (CollectionUtils.isEmpty(map)) {
            return new ArrayList<>(0);
        }
        List<JSONObject> lists = new ArrayList<>(rows.size());
        for (int i = 0; i < rows.size(); i++) {
            Serializable[] serializables = rows.get(i);
            JSONObject resultObject = new JSONObject();
            for (int j = 0; j < serializables.length; j++) {
                Serializable serializable = serializables[j];
                if (null != serializable) {
                    Object value = null;
                    Object valueObject = serializable;
                    if (null != valueObject) {
                        Class<?> aClass = valueObject.getClass();
                        if (null != aClass && aClass.getName().equals("[B")) {
                            value = new String((byte[]) valueObject);
                        } else {
                            value = valueObject;
                        }
                        resultObject.put(map.get(Integer.valueOf(j)), value);
                    }
                }
            }
            resultObject.put("binlog_event", event.getKey());
            resultObject.put("binlog_table_Name", tableData.getTable());
            lists.add(resultObject);
        }
        return lists;
    }

    private List<JSONObject> parseListener(List<Map.Entry<Serializable[], Serializable[]>> rows, TableData tableData, Map<String, Map<Integer, String>> metadata, Event event) {
        Map<Integer, String> map = metadata.get(tableData.getDatabaseTable());
        if (CollectionUtils.isEmpty(map)) {
            return new ArrayList<>(0);
        }

        List<JSONObject> lists = new ArrayList<>(rows.size());
        for (Map.Entry<Serializable[], Serializable[]> row : rows) {
            List<Serializable> entriesBefore = Arrays.asList(row.getKey());
            List<Serializable> entriesAfter = Arrays.asList(row.getValue());
            // before
            JSONObject dataObjectBefore = getDataObject(entriesBefore, map);
            // after
            JSONObject dataObject = getDataObject(entriesAfter, map);
            dataObject.put("before", dataObjectBefore);
            dataObject.put("binlog_event", event.getKey());
            dataObject.put("binlog_table_Name", tableData.getTable());
            lists.add(dataObject);
        }
        return lists;
    }

    private JSONObject getDataObject(List message, Map<Integer, String> metadata) {
        JSONObject resultObject = new JSONObject();
        for (int i = 0; i < message.size(); i++) {
            Object value = null;
            Object valueObject = message.get(i);
            String key = metadata.get(Integer.valueOf(i));
            if (null != valueObject) {
                Class<?> aClass = valueObject.getClass();
                if (null != aClass && aClass.getName().equals("[B")) {
                    value = new String((byte[]) valueObject);
                } else {
                    value = valueObject;
                }

                if (key.equals("create_time") || key.equals("update_time")) {
                    value = (long) value - 8 * 60 * 60 * 1000;
                }

                resultObject.put(key, value);
            }
        }
        return resultObject;
    }
}