演练从binlog中恢复删除的数据

尝试从mysqlbinlog中找到被删除的数据，并重新生成insert脚本

首先，通过mysqlbinlog命令，还原mysql-bin日志为可视化sql

mysqlbinlog --base64-output=decode-rows -v --start-datetime="2019-10-15 16:00:00" --stop-datetime="2019-10-15 19:10:00" /var/lib/mysql/mysql-bin.000003 >/data/bin.sql

分析还原后的sql，确认DELETE的代码结构

### DELETE FROM `sl_ctp_msg`.`t_sms_message`
### WHERE
###   @1=7
###   @2='7120190805140953251350'
###   @3='15013898488'
###   @4='131'
###   @5='ctp_login'
###   @6='{"code":"123445"}'
###   @7='您的验证码为：123445，验证码30分钟内有效，请勿转发给其他人。'
###   @8=3
###   @9=NULL
###   @10=1564985401
###   @11=NULL
###   @12=NULL
###   @13=4
###   @14='{"taskId":null,"overage":0,"mobileCount":0,"status":-106,"desc":"短信签名错误"}'
###   @15='test'
###   @16='172.29.59.80'
###   @17=NULL
###   @18=1564985401
###   @19=1564985478
###   @20=2

书写java类，解析日志结构，还原为insert语句

package mysql.binlog;

import lombok.Cleanup;

import java.io.*;

/**
 * @author: shen.liang
 * @since: 2019/10/15
 */
public class RollbackDelete {

    private static final String DELETE_START = "### DELETE FROM";
    private static final String COLUMN_VALUE = "###   @";

    /**
     * @param args
     * @Description TODO
     * mysqlbinlog --base64-output=decode-rows -v --start-datetime="2019-10-15 16:00:00" --stop-datetime="2019-10-15 19:10:00" /var/lib/mysql/mysql-bin.000003 >/data/bin.sql
     * 先通过mysqlbinlog命令，从binlog中还原执行过的SQL，然后找到被误删除的数据的执行处，解析log结构，生成还原的INSERT语句
     * @author shen.liang
     * @since 2019/10/15 19:56
     **/
    public static void main(String[] args) {

        File binLogSql = new File("d:/binlog.sql");
        File rollbackSql = new File("d:/rollback.sql");
        try {
            @Cleanup FileInputStream fis = new FileInputStream(binLogSql);
            @Cleanup InputStreamReader reader = new InputStreamReader(fis);
            @Cleanup BufferedReader br = new BufferedReader(reader);

            FileOutputStream fos = new FileOutputStream(rollbackSql);
            OutputStreamWriter writer = new OutputStreamWriter(fos);
            BufferedWriter bw = new BufferedWriter(writer);


            String line = null;
            String insertSql = null;
            while ((line = br.readLine()) != null) {
                if (insertSql == null) {//未初始insert语句，还在寻找delete的过程
                    if (line.startsWith("### DELETE FROM")) {//找到了delete
                        insertSql = line.replace("### DELETE FROM", "INSERT INTO ");//开始拼装insert
                    }
                } else {
                    if (line.startsWith("### WHERE")) {// WHERE关键字，转换VALUES关键字
                        insertSql += "  VALUES( ";
                    } else if (line.startsWith("###   @")) {//值，按顺序写到values后面
                        insertSql += line.replace(line.substring(0, line.indexOf("=") + 1), "") + ",";
                    } else {//都不匹配，到结尾了，结束insert sql
                        insertSql = insertSql.substring(0, insertSql.length() - 1);
                        insertSql += ");";
                        System.out.println(insertSql);
                        bw.write(insertSql);
                        bw.newLine();
                        bw.flush();
                        insertSql = null;
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}