【从后端日志文件中过滤出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}`);
});
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

结果

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

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