binlog数据监听实战

宫保鸡Z丁

已于 2023-03-16 10:25:54 修改

阅读量197

点赞数

文章标签： java servlet jvm mysql

于 2023-03-16 09:53:58 首次发布

本文链接：https://blog.csdn.net/Yheaw/article/details/129579586

版权

1.服务设计

引入开源包依赖

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

创建监听从库账户

-- 添加账户
CREATE USER '账号'@'2.7.4.5' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'1.1.1.1';   
flush privileges;

使用rocketmq做事件队列，缓存mysql事物，供程序有序消费生成业务数据。

服务运行问题：

2023-03-16 03:52:25.808  INFO 11691 --- [        Timer-0] org.srm.binlog.app.config.BinlogMonitor  : connectMysqlBinLog end
Exception in thread "Timer-0" java.lang.OutOfMemoryError: Java heap space
        at com.github.shyiko.mysql.binlog.io.ByteArrayInputStream.read(ByteArrayInputStream.java:88)
        at com.github.shyiko.mysql.binlog.event.deserialization.AbstractRowsEventDataDeserializer.deserializeBlob(AbstractRowsEventDataDeserializer.java:383)
        at com.github.shyiko.mysql.binlog.event.deserialization.AbstractRowsEventDataDeserializer.deserializeCell(AbstractRowsEventDataDeserializer.java:186)
        at com.github.shyiko.mysql.binlog.event.deserialization.AbstractRowsEventDataDeserializer.deserializeRow(AbstractRowsEventDataDeserializer.java:138)
        at com.github.shyiko.mysql.binlog.event.deserialization.UpdateRowsEventDataDeserializer.deserializeRows(UpdateRowsEventDataDeserializer.java:71)
        at com.github.shyiko.mysql.binlog.event.deserialization.UpdateRowsEventDataDeserializer.deserialize(UpdateRowsEventDataDeserializer.java:58)
        at com.github.shyiko.mysql.binlog.event.deserialization.UpdateRowsEventDataDeserializer.deserialize(UpdateRowsEventDataDeserializer.java:33)
        at com.github.shyiko.mysql.binlog.event.deserialization.EventDeserializer.deserializeEventData(EventDeserializer.java:294)
        at com.github.shyiko.mysql.binlog.event.deserialization.EventDeserializer.nextEvent(EventDeserializer.java:223)
        at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:984)
        at com.github.shyiko.mysql.binlog.BinaryLogClient.connectWithTimeout(BinaryLogClient.java:517)
        at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:490)
        at org.srm.binlog.app.config.BinlogMonitor$1.run(BinlogMonitor.java:63)
        at java.util.TimerThread.mainLoop(Timer.java:555)

原因分析：

mysql一个event上百兆数据，java程序binlog同步过程中，造成内存溢出。dba查看mysql配置，查看binlog-row-event-max-size参数为默认值8k，服务内存1G，应该不会发生内存溢出。待查。。

解决方法：

暂时提高程序内存配置，观察运行情况。