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