数据库监控监听

嘿嘿 又知道多一点点 说来有些惭愧 现在才发现，都怪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