mysqlbinlog 恢复数据跳过报错 mysqlbinlog 恢复指定表

MySql使用binlog还原数据

小编平时操作数据库都很谨慎，特别涉及到数据的增删改的时候，都会反复检查一下sql语句的逻辑性是否准确，但是人有失手马有失蹄，终于在某次操作的时候把表给清了，十几万数据没了，出现这种问题第一反应就应该是赶紧找备份，但是发现没有备份，所以只能寄希望于binlog恢复，也总结了此次binlog使用经验，跟大家分享一下

1. 确认是否符合binlog恢复条件

发现误操作了以后，第一时间是停止进行其他操作，这样方便进行数据的定位

1. 确认binlog是开启状态

show variables like '%log_bin%';

2. 查看binlog使用哪种模式

1. Statement模式，此种格式会记录执行的sql，并不会记录每一行的变化，极大的减少了日志量
2. row模式，会记录执行的sql以及每一行的数据变化，如果binlog是这种模式，肯定可以对数据进行恢复
3. Mixed模式，是statement和row模式的混合体，由系统判断使用哪种模式

上述三种格式中如果使用的是row模式，恢复起来就比较容易了，通过如下命令查看

show variables like '%binlog_format%';

3. 查看当前binlog文件

show master status;

上述命令是用来查看当前使用的binlog，也可以使用如下命令查看所有binlog文件

show binary logs;

2. 恢复数据操作

1. 查找要恢复的节点

show binlog events in 'binlog.000003';

上图中标注了几个重要的点要解释一下

1. 第一行标注，1355是开始节点，info中是BEGIN
2. 第二行标注，test.test1表示是test库中的test1表进行的操作
3. 第三行标注，执行的操作类型，此处是写入行操作
4. 第四行标注，1564是此操作的结束节点

可以通过这些节点信息的筛选可以大致确定一些节点信息

如果在上述操作中，不能完全准确的找到节点，需要binlog内容解析出来，进行精确查找

mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-position=1355 --stop-position=3933 --database=test /usr/local/mysql/data/binlog.000003> /root/test1.sql

上述命令简单介绍一下

1. –no-defaults用来设置编码的，在win系统中有时候出现编码不一致的情况，此时如果不加此参数就会出现如下报错

```
  mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
  ```

2. –base64-output=decode-rows -vv这是两个参数，用来生成可视化日志文件的，如果加上这两个参数，就不要生成sql文件，直接生成txt文件，分析执行的数据以便确定节点
3. –start-position=1355 --stop-position=3933 开始节点和结束节点，有时也会使用时间来圈定范围，只需要将节点参数换成时间参数即可–start-datetime=“2023-10-28 05:43:53” --stop-datetime=“2023-10-28 05:50:01”，时间的格式按照上述格式，需要注意的是，如果出现下述错误，需要检查结束节点内是否包含完整的执行日志，如下图结束点为5678才是完整的记录，如果结束点为5521，就会报错，如果在除5678之外的其他节点上，虽然可以生成sql文件，但是执行会有问题

WARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag set. This might be because the last statement was not fully written to the log, or because you are using a --stop-position or --stop-datetime that refers to an event in the middle of a statement. The event(s) from the partial statement have not been written to output.

5. –database=test 选择要恢复的数据库
6. /usr/local/mysql/data/binlog.000003 要使用的binlog文件
7. > /root/test1.sql将内容输出到文件
8. 直接使用命令恢复

./mysqlbinlog --start-position=开始节点 --stop-position=结束节点 --database=库名 /usr/local/mysql/data/binlog.000003 | mysql -h ip地址 -P 端口号 -uroot -p

5. 生成sql文件恢复

1. 生成sql文件

./mysqlbinlog --no-defaults --start-position=1355 --stop-position=3933 --database=test /usr/local/mysql/data/binlog.000003> /root/test1.sql

1. 进入数据库

#连接数据库
mysql -h ip地址 -P 端口号 -uroot -p

1. 执行文件

-- 打开数据库
use 库名称
-- 执行sql文件
source /root/test1.sql

5. 生成文本文件，通过提取数据进行恢复

./mysqlbinlog --no-defaults --base64-output=decode-rows -vv --start-position=1355 --stop-position=3933 --database=test /usr/local/mysql/data/binlog.000003> /root/test1.txt

此处不再赘述怎么提取内容进行操作，我进行操作的时候是因为数据是很长时间存储的，已经无法通过语句还原进行操作了，所以使用了提取内容进行解析，反向生成插入语句的方式，下面简单介绍一下如何反向解析

1. 首先将误操作的内容提取出来，如下图

此处标出了此条记录操作之前的内容，需要对这个内容进行提取

1. 提取内容的方式
   我是使用java进行解析的，原理是先判断此块内容属于误操作内容，对里面的字段通过正则提取，然后拼接成（值1，值2）的字符串存入一个集合，这样所有误操作之前的数据就提取出来了，对集合的内容进行拼接成插入语句的形式，其中insert into 表名（字段名1，字段名2）属于固定模板，所以我开始直接每一条数据拼接为一条语句，这样拼接了十几万的插入语句，效率不尽人意，所以更改了策略，每五百条拼接为一条插入语句，这样执行起来效率就特别快了，等有时间我做一个可以解析增删改生成反向语句的小工具，有需要的小伙伴可以@我哦，因为时间原因，将我操作的简易版代码先给小伙伴们参考一下
   代码如下

/**
     * 此处传入的文件是从binlog里面导出的txt文件，导出语句加了--base64-output=decode-rows -vv的哦
     * @param file
     * @throws IOException
     */
    public static void run(File file) throws IOException {
        BufferedReader bf = new BufferedReader(new FileReader(file));
        String line = null;
        int i = 0;
        List<String> list = new ArrayList<>();
        for (int j = 0; j < 2193510; j++) {
            line = bf.readLine();
            if ("### DELETE FROM `test`.`test1`".equals(line)) {
                bf.readLine();

                Matcher p1 = Pattern.compile("(?<=(@1=)).*(?=( /\\* ))").matcher(bf.readLine());
                p1.find();
                String value1 = p1.group();

                Matcher p2 = Pattern.compile("(?<=(@2=)).*(?=( /\\* ))").matcher(bf.readLine());
                p2.find();
                String value2 = p2.group();

                list.add("(" + value1 + "," + value2 + ")");
                i++;
            }
        }
        List<String> sql = new ArrayList<>();
        //批量操作每条语句设置插入的条数
        int batch = 500;
        //起始下标
        int start = 0;
        while(start < list.size()){
            if ((start+batch)<list.size()){
                String pinjie = pinjie(list.subList(start, start + batch));
                sql.add(pinjie);
            } else {
                String pinjie = pinjie(list.subList(start,list.size()));
                sql.add(pinjie);
            }
            start += batch;
        }
        File file1 = new File("D:/insert/file.txt");
        file1.createNewFile();
        BufferedWriter bw = new BufferedWriter(new FileWriter(file1));
        for (String s : sql) {
            bw.write(s);
            bw.newLine();
        }
        bw.flush();
        bw.close();
        bf.close();

        System.out.println(i);
    }

    public static String spliceSql(List<String> list){
        String sql = "insert into `test`.`test1`(id,name) values";
        for (int i = 0; i < list.size(); i++) {
            sql += list.get(i);
            if (i < list.size()-1){
                sql += ",";
            } else {
                sql += ";";
            }
        }
        return sql;
    }