【从后端日志文件中过滤出sql语句】

本文主要是介绍【从后端日志文件中过滤出sql语句】，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

why?

为什么会有这种需求？，mysql数据不小心被删了完全可以从备份数据恢复，或者从binlog中恢复，但是如果前面这两种方法没办法处理（没有备份数据库文件、没有binlog日志😨），如果后端日志里面有sql语句的话理论上是可以提取出来做恢复的。

思路

• 分析日志文件结构
• 根据关键信息把日志文件一条一条的读出来存储在数组里面
• 根据关键字匹配出你需要的日志记录
• 处理匹配出来的日志
• 把日志处理成sql文件存储下来

日志文件的格式

const fs = require('fs');
const path = require('path');const logDir = './info';
const keywords = ['INSERT INTO life', '100209102']; // 要匹配的关键字数组 匹配出来的数据必须同时满足匹配的关键字
const outputFilePath = path.join(__dirname, 'filtered_logs.sql');
const prefixToRemove = '可执行sql='; // 删除多余的日志前缀信息
const datePattern = /\d{4}-\d{2}-\d{2}T\d{2}:\d{2}(:\d{2}(\.\d+)?)?/g;
const englishSemicolon = ';';const logStartPattern = /(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d+)/g;const processFile = (filePath) => {const readStream = fs.createReadStream(filePath, { highWaterMark: 64 * 1024 });let lastLogEntry = '';readStream.on('data', (chunk) => {const data = lastLogEntry + chunk.toString();const logEntries = data.split(logStartPattern);lastLogEntry = logEntries.pop();for (const logEntry of logEntries) {if (keywords.every(keyword => logEntry.includes(keyword))) {const trimmedLogEntry = trimLogEntry(logEntry);const formattedLogEntry = formatDateInLogEntry(trimmedLogEntry);const logEntryWithSemicolon = `${formattedLogEntry}${englishSemicolon}`;appendToFile(logEntryWithSemicolon);}}});readStream.on('end', () => {if (lastLogEntry && keywords.every(keyword => lastLogEntry.includes(keyword))) {const trimmedLogEntry = trimLogEntry(lastLogEntry);const formattedLogEntry = formatDateInLogEntry(trimmedLogEntry);const logEntryWithSemicolon = `${formattedLogEntry}${englishSemicolon}`;appendToFile(logEntryWithSemicolon);}});readStream.on('error', (err) => {console.error(`读取文件 ${filePath} 失败:`, err);});
};const trimLogEntry = (logEntry) => {const prefixIndex = logEntry.indexOf(prefixToRemove);if (prefixIndex !== -1) {return logEntry.slice(prefixIndex + prefixToRemove.length);}return logEntry;
};const formatDateInLogEntry = (logEntry) => {return logEntry.replace(datePattern, (match) => `'${match}'`);
};const appendToFile = (logEntry) => {fs.appendFile(outputFilePath, logEntry + '\n', (err) => {if (err) {console.error('写入文件失败:', err);}});
};fs.readdir(logDir, (err, files) => {if (err) {console.error('读取文件夹失败:', err);return;}files.filter(file => file.endsWith('.log')).forEach(file => {const filePath = path.join(logDir, file);console.log('读取文件：', filePath)processFile(filePath);});
});process.on('exit', () => {console.log(`筛选日志完成,已写入 ${outputFilePath}`);
});

结果

处理出来的sql文件做了精简 仅供参考

INSERT INTO life (id， type, title ) VALUES (1， 2, '100209102', );

这篇关于【从后端日志文件中过滤出sql语句】的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---