MySQLBinlog在Windows/Linux环境下恢复数据实战

起因

因业务需要，从Oracle中导出数据到MySQL中，使用的是Navicat的导出结构与数据，结果导出的SQL中包含了DROP TABLE IF EXISTS语句，使得本应该是增量更新的数据，被导入的数据覆盖，使得原来MySQL中标数据丢失

思路

因为是DROP表，故要使用binlog是无法获取删除的数据，只得通过从建表以来或上一次备份数据库的记录后再查询后续的插入记录来恢复

• 获取并创建已有的表结构
• 恢复已有的备份数据
• 使用binlog查找删除期间的SQL更新记录（包含INSERT、UPDATE、DELETE）
• 使用Linux下的cat函数转换成可执行的SQL
• 执行SQL语句

操作

1、查看是否开启Binlog

登录MySQL管理工具，可使用Navicat或命令行，查询以下语句

show variables like '%log_bin%';

导到结果

若log_bin的value值为OFF,很遗憾，此篇文章帮助不了你

2、查找log文件位置

如不出意外log文件位于MySQL数据目录（第一步的位置）下，D:\Program Files\MySQL\Data，命名规则为.000000结尾

3、使用binlog导出成日志文件

mysqlbinlg.exe一般位于C:\Program Files\MySQL\MySQL Server 8.0\bin\ 下，若无法在cmd中直接使用，请到该目录下执行cmd使用

mysqlbinlog --base64-output=decode-rows -v --database=spider --stop-datetime="2023-04-19 17:43:20" --stop-datetime="2023-04-19 18:43:20" D:\0L4ETRPJSA1OYXA-bin.000068 > D:\back_log.sql

4、日志导出为可识别的SQL

默认导出的日志为不好识别的SQL语句，需要进行转换才能使用

此处需要使用linux环境下的cat命令截取其中的CURD语句，故需要上传文件到linux系统中，并执行

cat back_log.sql | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@6.*),/\1;/g' | sed 's/@[1-9]=//g' | sed 's/@[1-9][0-9]=//g' > insert.sql

经过转换的SQL仍然不可直接执行，因为缺少完成的部分，比如括号和逗号

故此时需要使用批量修改方法，添加分隔符，这里仅演示INSERT语句

import java.io.File;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class SQLHandler {
    public final static String[] tables = new String[]{"data_air_press"};//用来过滤指定表

    public static void main(String[] args) {
        //读取SQL记录
        try {
            String file = "G:\\恢复\\format\\insert68.sql";
            List<String> lines = Files.readAllLines(Paths.get(file), StandardCharsets.UTF_8);
            boolean lineFlag = false;
            List<String> sqlLines = new ArrayList<>();
            StringBuilder stringBuffer = new StringBuilder();
            for (String line : lines) {
                if (line.startsWith("INSERT")) {
                    //需要以在数据中
                    String tableName = line.substring(line.indexOf(".`") + 2, line.lastIndexOf("`"));
                    if (!Arrays.asList(tables).contains(tableName)) {
                        stringBuffer = new StringBuilder();
                        lineFlag = false;
                        continue;
                    }
                    if (!ValidateUtil.isEmpty(stringBuffer.toString())) {
                        sqlLines.add(stringBuffer.toString());
                    }

                    stringBuffer = new StringBuilder();
                    lineFlag = true;
                    stringBuffer.append(line);
                } else if ("UPDATE".equals(line) || "SELECT".equals(line)) {
                    lineFlag = false;
                    stringBuffer = new StringBuilder();
                }
                //
                if (lineFlag && "SET".equals(line)) {
                    stringBuffer.append(" VALUES(");
                }
                if (lineFlag && line.startsWith(" ")) {
                    stringBuffer.append(line).append(",");
                }
            }
           StringBuffer finalBuffer = new StringBuffer();
            sqlLines.forEach(item -> {
                String sqlLine = item.substring(0, item.length() - 1) + ");";
                finalBuffer.append(sqlLine).append("\n");
            });
            //写入父级目录
            File f = new File(file);
            if (f.exists()){
                String p = f.getParentFile().getAbsoluteFile()+File.separator+"filter2";
                if (!new File(p).exists()){
                    new File(p).mkdirs();
                }
                Files.write(Paths.get(p+File.separator+f.getName()), finalBuffer.toString().getBytes());
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

5、使用工具执行SQL恢复数据

经过第4步执行的SQL补齐结果如上所示，使用navicat或其他工具导入到数据库即可，记得确认数据库名称和表名，建议如源表在使用中，可导入到临时表，再通过同步工具恢复