CDC 之 MySQL binlog订阅 一

Java订阅MySQL日志的方法

在Java中订阅MySQL日志有几种常见方法，下面我将介绍几种主要方式并提供代码示例。

1. 使用MySQL Binlog Connector订阅二进制日志(Binlog)

这是最常用的方法，可以实时获取数据库变更。

添加依赖(Maven)

<dependency>
    <groupId>com.github.shyiko</groupId>
    <artifactId>mysql-binlog-connector-java</artifactId>
    <version>0.25.3</version>
</dependency>

代码示例

import com.github.shyiko.mysql.binlog.BinaryLogClient;
import com.github.shyiko.mysql.binlog.event.*;

import java.io.IOException;

public class MySQLBinlogSubscriber {

    public static void main(String[] args) {
        BinaryLogClient client = new BinaryLogClient(
            "localhost",  // MySQL服务器地址
            3306,         // MySQL端口
            "username",   // 用户名
            "password"    // 密码
        );

        // 设置服务器ID(必须唯一)
        client.setServerId(123456);
        
        // 可选：从特定位置开始读取
        // client.setBinlogFilename("binlog.000001");
        // client.setBinlogPosition(4);

        client.registerEventListener(event -> {
            EventData data = event.getData();
            
            if (data instanceof TableMapEventData) {
                TableMapEventData tableMap = (TableMapEventData) data;
                System.out.println("Table: " + tableMap.getDatabase() + "." + tableMap.getTable());
            } else if (data instanceof WriteRowsEventData) {
                WriteRowsEventData writeRows = (WriteRowsEventData) data;
                System.out.println("Insert: " + writeRows.getRows());
            } else if (data instanceof UpdateRowsEventData) {
                UpdateRowsEventData updateRows = (UpdateRowsEventData) data;
                System.out.println("Update: " + updateRows.getRows());
            } else if (data instanceof DeleteRowsEventData) {
                DeleteRowsEventData deleteRows = (DeleteRowsEventData) data;
                System.out.println("Delete: " + deleteRows.getRows());
            } else if (data instanceof QueryEventData) {
                QueryEventData queryData = (QueryEventData) data;
                System.out.println("Query: " + queryData.getSql());
            }
        });

        try {
            client.connect();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

2. 使用JDBC监听查询日志(需要MySQL配置)

这种方法需要先在MySQL中启用通用查询日志。

MySQL配置(my.cnf/my.ini)

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mysql-general.log

Java代码监听日志文件变化

import org.apache.commons.io.input.Tailer;
import org.apache.commons.io.input.TailerListenerAdapter;

import java.io.File;

public class MySQLQueryLogSubscriber {

    public static void main(String[] args) {
        File logFile = new File("/var/log/mysql/mysql-general.log");
        
        TailerListenerAdapter listener = new TailerListenerAdapter() {
            @Override
            public void handle(String line) {
                System.out.println("New log entry: " + line);
                // 这里可以解析SQL语句
            }
        };
        
        Tailer tailer = new Tailer(logFile, listener, 1000, true);
        Thread thread = new Thread(tailer);
        thread.setDaemon(true);
        thread.start();
        
        // 保持程序运行
        try {
            Thread.sleep(Long.MAX_VALUE);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
}

3. 使用Canal订阅MySQL Binlog(阿里开源方案)

Canal是阿里巴巴开源的一个基于MySQL数据库增量日志解析的组件。

添加依赖

<dependency>
    <groupId>com.alibaba.otter</groupId>
    <artifactId>canal.client</artifactId>
    <version>1.1.6</version>
</dependency>

代码示例

import com.alibaba.otter.canal.client.CanalConnector;
import com.alibaba.otter.canal.client.CanalConnectors;
import com.alibaba.otter.canal.protocol.Message;
import com.alibaba.otter.canal.protocol.CanalEntry.*;

import java.net.InetSocketAddress;
import java.util.List;

public class CanalClientExample {

    public static void main(String[] args) {
        // 创建链接
        CanalConnector connector = CanalConnectors.newSingleConnector(
            new InetSocketAddress("127.0.0.1", 11111), 
            "example", "", "");
        
        try {
            connector.connect();
            connector.subscribe(".*\\..*");
            connector.rollback();
            
            while (true) {
                Message message = connector.getWithoutAck(100); // 获取指定数量的数据
                long batchId = message.getId();
                List<CanalEntry.Entry> entries = message.getEntries();
                
                if (batchId != -1 && entries.size() > 0) {
                    for (CanalEntry.Entry entry : entries) {
                        if (entry.getEntryType() == EntryType.ROWDATA) {
                            RowChange rowChange = RowChange.parseFrom(entry.getStoreValue());
                            EventType eventType = rowChange.getEventType();
                            
                            System.out.println("Event type: " + eventType);
                            for (RowData rowData : rowChange.getRowDatasList()) {
                                if (eventType == EventType.INSERT) {
                                    System.out.println("Insert: " + rowData.getAfterColumnsList());
                                } else if (eventType == EventType.UPDATE) {
                                    System.out.println("Update before: " + rowData.getBeforeColumnsList());
                                    System.out.println("Update after: " + rowData.getAfterColumnsList());
                                } else if (eventType == EventType.DELETE) {
                                    System.out.println("Delete: " + rowData.getBeforeColumnsList());
                                }
                            }
                        }
                    }
                }
                connector.ack(batchId); // 确认提交
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            connector.disconnect();
        }
    }
}

注意事项

1. 权限要求：订阅Binlog需要MySQL用户具有REPLICATION SLAVE和REPLICATION CLIENT权限

   GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username'@'%';
   FLUSH PRIVILEGES;
   

2. MySQL配置：确保MySQL配置中启用了Binlog

   [mysqld]
   log-bin=mysql-bin
   binlog-format=ROW
   server_id=1
   

3. 性能考虑：高频率的日志订阅可能对数据库性能有影响，生产环境需要谨慎评估

4. 断连处理：实际应用中需要添加重连机制和错误处理

以上方法中，Binlog Connector方式最为常用和灵活，适合大多数需要实时监听MySQL变更的场景。