数据库监控监听

嘿嘿 又知道多一点点 说来有些惭愧 现在才发现,都怪canal 哈哈

实时监控mysql表除了canal还有什么,还有很多,比如

mysql-binlog-connector-java

GitHub - liufeiit/mysql-binlog-connector-java: mysql-binlog-connector-java

 java库,基于复制协议解析读取mysql二进制日志(实时订阅/消费)

自然canal还有这个client还有下面要提到都是需要开启binlog的show variables like 'log_bin';

log_bin=mysql-bin
binlog-format=ROW#格式 statement row mixed
server-id=1#sql从哪个server写入

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

row:仅保存被修改的细节,每一行数据的变化,被修改行的实际数据,不记录上下文

statement:记录每条会修改数据的sql本身,执行语句 上下文环境

mixed:混合简单的statement 复杂row,对于insert update delete的binlog事件类型=query事件

事件

query与数据库关,begin / drop  table / truncate table

table_map:记录下一个操作对应的表信息

xid:标记事务提交

write_rows插入数据 update_rows delete_rows 具体看代码,很清晰 但是也有点小问题 评论区指出吧大佬们

public static void main(String[] args) throws IOException {

        BinaryLogClient client = new BinaryLogClient("IP", 3306, "账号", "密码");
        EventDeserializer eventDeserializer = new EventDeserializer();
        eventDeserializer.setCompatibilityMode(
                EventDeserializer.CompatibilityMode.DATE_AND_TIME_AS_LONG,
                EventDeserializer.CompatibilityMode.CHAR_AND_BINARY_AS_BYTE_ARRAY
        );
 
        client.setEventDeserializer(eventDeserializer);
 
        //设置需要读取的Binlog的文件以及位置,否则,client会从"头"开始读取Binlog并监听
 
       //client.setBinlogFilename("mysql-bin.000001");
       //client.setBinlogPosition("/data/");
 
        Thread thread = new Thread(() -> {
            client.registerEventListener(event -> {
                final EventData data = event.getData();
                if (data instanceof WriteRowsEventData) {
                    WriteRowsEventData writeRowsEventData = (WriteRowsEventData) data;
                } else if (data instanceof UpdateRowsEventData) {
                    UpdateRowsEventData updateRowsEventData = (UpdateRowsEventData) data;
                } else if (data instanceof DeleteRowsEventData) {
                    DeleteRowsEventData deleteRowsEventData = (DeleteRowsEventData) data;
                } else if (data instanceof QueryEventData) {
                    QueryEventData queryEventData = (QueryEventData) data;
                    String database = queryEventData.getDatabase();
                    if ("jeecg-boot".equals(database)) {
                        //这个地方要dubug具体看一下如何取值
                        String sql = queryEventData.getSql();
                        if (sql != null && sql.length() >= 10) {
                                     
                            String[] s = sql.split(" ");
                            String type = s[0];
                            String tableName = null;
                            type = type.toUpperCase();
                            switch (type) {
                                case "INSERT":
                                    break;
                                case "DELETE":
                                    break;
                                case "UPDATE":
                                    tableName = s[1];
                                    break;
                                default:
                                    break;
                            }
 
                            if (tableName != null) {
                                tableName = tableName.replaceAll("`", "").replaceAll("'", "").replaceAll("\\.", "").toLowerCase();
                                if (TABLE_NAME.contains(tableName)) {

                                }
                            }
                        }
                    }
                }
            });

           client.registerLifecycleListener(new BinaryLogClient.LifecycleListener() {
                @Override
                public void onConnect(BinaryLogClient client) {
                    logger.info("Connected to MySQL server");
                }

                @Override
                public void onCommunicationFailure(BinaryLogClient client, Exception ex) {
                    logger.error("Communication failure with MySQL server", ex);
                }

                @Override
                public void onEventDeserializationFailure(BinaryLogClient client, Exception ex) {
                    logger.error("Event deserialization failure", ex);
                }

                @Override
                public void onDisconnect(BinaryLogClient client) {
                    logger.warn("Disconnected from MySQL server");
                    // 在这里添加重新连接或其他处理逻辑
                }
            });

            try {
                client.connect();
            } catch (IOException e) {
                //
            }
        });
        return thread;
    }
  }

durid

这个引入依赖jar,配置文件替换驱动 基本操作就不说了

  配置文件需要配置filters监控统计拦截filters:stat监控统计 log4j日志记录 wall防御sql注入

使用log4j记录日志,引入log4j的maven坐标

@Configuration
public class DruidConfig(){
   
    @ConfigurationProperties(prefix="spring.datasource")
    @Bean
    public DataSource DruidDatasource(){
      return new DruidDataSource();
    }

    @Bean
    public ServletRegistrationBean servletRegistrationBean(){
      //servlet配置,web.xml对servlet配置
      ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(),"/druid/*");
      Map<String,String> hashMap = new HashMap<>();
      hashMap.put("loginUsername","demo1");
      hashMap.put("loginPassword","123");
      hashMap.put("allow","");
      bean.setInitParameters(hashMap);
      return bean;
    }
   
}

访问http://localhost:8080/druid

https://blog.51cto.com/u_12897/8658359

canal之前写了

debezium

https://www.cnblogs.com/zhangpan1244/p/11329817.html

https://blog.51cto.com/u_16175522/9046382

相关推荐

  1. 数据库监控监听

    2024-05-12 12:34:03       5 阅读
  2. 快速监控 Oracle 数据库

    2024-05-12 12:34:03       25 阅读
  3. 数据库RDS云监控

    2024-05-12 12:34:03       24 阅读
  4. 了解监控易(13):数据库监控-功能模块解析

    2024-05-12 12:34:03       7 阅读
  5. 数据仓库数据质量监控

    2024-05-12 12:34:03       10 阅读

最近更新

  1. .Net Core WebAPI参数的传递方式

    2024-05-12 12:34:03       0 阅读
  2. QT--气泡框的实现

    2024-05-12 12:34:03       1 阅读
  3. LeetCode 968.监控二叉树 (hard)

    2024-05-12 12:34:03       0 阅读
  4. leetcode热题100.完全平方数(动态规划进阶)

    2024-05-12 12:34:03       0 阅读
  5. leetcode328-Odd Even Linked List

    2024-05-12 12:34:03       0 阅读
  6. C 语言设计模式(结构型)

    2024-05-12 12:34:03       0 阅读
  7. v-if 与 v-show(vue3条件渲染)

    2024-05-12 12:34:03       0 阅读
  8. kafka防止消息丢失配置

    2024-05-12 12:34:03       0 阅读

热门阅读

  1. C# 实现加减乘除 (备忘)

    2024-05-12 12:34:03       3 阅读
  2. 计算机视觉教学实训解决方案

    2024-05-12 12:34:03       3 阅读
  3. 1080:余数相同问题

    2024-05-12 12:34:03       4 阅读
  4. [C/C++] -- 适配器模式

    2024-05-12 12:34:03       2 阅读
  5. 整体意义的构成与构建

    2024-05-12 12:34:03       4 阅读
  6. 【负载均衡式在线OJ项目day5】OJ服务模块概要

    2024-05-12 12:34:03       4 阅读
  7. 复习用到知识(asp.net)

    2024-05-12 12:34:03       3 阅读
  8. sass详解与使用

    2024-05-12 12:34:03       3 阅读
  9. Ubuntu设置中午输入法

    2024-05-12 12:34:03       3 阅读
  10. XML 解析器

    2024-05-12 12:34:03       5 阅读