mysql日志文件binlog分析记录

1、binlog转sql
使用mysql自带的binlog工具

mysqlbinlog  --base64-output=decode-rows -v /var/lib/mysql/binlog.000208 --result-file=/var/lib/mysql/binsql000208.sql

2、筛选统计操作

      

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Queue;
import java.util.concurrent.ConcurrentLinkedQueue;


public class TxtReadWrite {

    static String inFileName = "D:\\docker\\mysql\\data\\binsql000050.sql";
    static String outFileName = "D:\\test\\binsql46-50.txt";
    static Queue<String> Line_Queue = new ConcurrentLinkedQueue<>();
    static BufferedWriter writer;

    static {
        try {
            writer = new BufferedWriter(new FileWriter(outFileName,true));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args) throws IOException, ParseException {
//        筛选update和delete操作
        readAndWrite();
//        统计每个表的操作数量
        countOperation();
//        统计已有thread_id
        countThreadId();
//        每两个小时统计一次操作次数
        countAsHour();
//          统计每个thread-id更改每个表的次数
        countIdAndTable();
    }
    public static void countIdAndTable() throws IOException {
        String inFileForCount =  "D:\\test\\binsql46-50.txt";//文件内只有删除、更新
        FileReader fileReader = new FileReader(inFileForCount);
        BufferedReader bufferedReader = new BufferedReader(fileReader);

        String line = bufferedReader.readLine();
        Map<String,Integer> map = new HashMap();


        String threadId = "";
        String tablename ="";
        while (line != null) {
            if(line.contains("thread_id")){
                threadId = line.substring(line.indexOf("thread_id="), line.lastIndexOf("exec_time"));

            }else if(line.contains("`.`")){
                tablename = line.substring(line.indexOf("`"), line.lastIndexOf("`"));
                String str =  threadId+"  "+tablename;
                Integer i = map.get(str)==null?0:map.get(str);
                i+=1;
                map.put(str,i);
            }
            line = bufferedReader.readLine();
        }

        System.out.println(map.toString());

    }
    public static void countAsHour() throws IOException, ParseException {
        String inFileForCount =  "D:\\test\\binsql46-50.txt";//文件内只有删除、更新
        FileReader fileReader = new FileReader(inFileForCount);
        BufferedReader bufferedReader = new BufferedReader(fileReader);

        String line = bufferedReader.readLine();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd HH:mm:ss");

        String beginStr = "20220326  12:00:00";
        Date beginTime = simpleDateFormat.parse(beginStr);
        Map<String,Integer> m= new HashMap();
        Date current;
        while (line != null) {
            if(line.contains("thread_id")){
                String substring = "20"+line.substring(1, 16);
                current= simpleDateFormat.parse(substring);
                System.out.println(substring);
                if (current.getTime()+ (8 * 60 * 60 * 1000)<=beginTime.getTime()){
                    String s = simpleDateFormat.format(beginTime.getTime());

                    Integer i = m.get(s)==null?0:m.get(s);
                    i+=1;
                    m.put(s,i);
                    line = bufferedReader.readLine();

                }else{
                    beginTime= new Date(beginTime.getTime() + (2 * 60 * 60 * 1000));
                    String s = simpleDateFormat.format(beginTime);
                    m.put(s,0);

                    System.out.println(beginTime);

                    continue;
                }
            }else{
                line = bufferedReader.readLine();
            }
        }

        System.out.println(m.toString());

    }
    public static void countThreadId() throws IOException {
        String inFileForCount =  "D:\\test\\binsql46-50.txt";//文件内只有删除、更新
        FileReader fileReader = new FileReader(inFileForCount);
        BufferedReader bufferedReader = new BufferedReader(fileReader);

        String line = bufferedReader.readLine();
        HashSet set = new HashSet();
        while (line != null) {
            if(line.contains("thread_id")){
                String substring = line.substring(line.indexOf("thread_id="), line.lastIndexOf("exec_time"));
                set.add(substring);

            }
            line = bufferedReader.readLine();
        }

        System.out.println(set.size());
        for (Object o : set) {
            System.out.println(o+",");
        }
    }

        public static void countOperation() throws IOException {
        String inFileForCount =  "D:\\test\\binsql46-50.txt";//文件内只有删除、更新
        FileReader fileReader = new FileReader(inFileForCount);
        BufferedReader bufferedReader = new BufferedReader(fileReader);

        String line = bufferedReader.readLine();
        HashMap<String, Integer> count = new HashMap<>();
        while (line != null) {
            if(line.contains("`")){
                String substring = line.substring(line.indexOf("`"), line.lastIndexOf("`"));
                Integer i = count.get(substring)==null?0:count.get(substring);
                i +=1;
                count.put(substring,i);
            }
            line = bufferedReader.readLine();
        }

        System.out.println(count.toString());

    }


    public static void readAndWrite() throws IOException {

//        读取
        FileReader fileReader = new FileReader(inFileName);
        BufferedReader bufferedReader = new BufferedReader(fileReader);

        String line = bufferedReader.readLine();

        while (line != null) {
            if (line.contains("thread_id=")) {
//                String peek = Line_Queue.peek();//获取队列头 ；不移除
                if(Line_Queue.size()==1){
                    Line_Queue.clear();
                }else{
                    executeQueue();
                }
                Line_Queue.add(line);
                line = bufferedReader.readLine();
                continue;
            }
            if (line.startsWith("###")) {
                if (line.contains(" UPDATE ")||line.contains(" DELETE ")) {
                    Line_Queue.add(line);
                    line = bufferedReader.readLine();
                    while(line != null&&line.startsWith("###")) {
                        Line_Queue.add(line);
                        line = bufferedReader.readLine();
                    }
                }
            }

            line = bufferedReader.readLine();

        }

        bufferedReader.close();
        fileReader.close();


    }

    public static void executeQueue() throws IOException {

//        写入
        int size = Line_Queue.size();

        for (int i = 0; i < size; i++) {
            String line = Line_Queue.poll();
            writer.write(line);
            writer.newLine();//写换行符
//            this.execute(sql);
        }
        writer.flush();

    }
}