Mysql基于binlog日志恢复数据

1.Linux安装mysql

https://blog.csdn.net/qq_44981526/article/details/126717005

可能遇到的问题

1.net-tools未安装，执行yum install net-tools

2.远程连接工具连接不上mysql

grant all privileges on *.* to 'root'@'%' identified by '远程连接密码' with grant option
#刷新配置
flush privileges;

2.基于binlog日志恢复数据

1.MySQL Binlog 简介

​ MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

2.Binlog日志的两个最重要的使用场景

1、MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
2、数据恢复：通过使用 mysqlbinlog工具来使恢复数据

3.开启Binlog日志

通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项：

##配置binlog日志路径以及日志文件名前缀、生成的日志格式为：bin-log.000001
log-bin=/var/lib/mysql/bin-log
##注意5.7以及更高版本需要配置本项、自定义，保证唯一性
server-id=1
##binlog格式，有3种statement,row,mixed，区别的话，请自行搜索
binlog-format=mixed
##表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定
sync-binlog=1

4.查看Binlog日志状态

show variables like '%log_bin%';

5.查看Binlog文件内容

#进入前面配置配置mysql的binlog日志路径
cd /var/lib/mysql

生成的日志格式为：bin-log.000001，实际生产环境比较复杂会有多个日志文件，根据日期进行筛选

show binlog events in 'bin-log.000001'; 

将binlog文件导出为sql文件

mysqlbinlog bin-log.000003 -d xt_91_propertymodule_monomer> xt_91_propertymodule_monomer.sql

6.通过Binlog恢复数据

已llp库为例，因为误操作或者其他原因，我删除了test表中的一些数据，现在想要恢复它；

将llp库进行删除

1.指定位置恢复

#linux命令行：
#/var/lib/mysql目录执行
mysqlbinlog /var/lib/mysql/bin-log.000001 --start-position=4 --stop-position=78153 | mysql -uroot -proot123456

进入，注意开始和结束位置的合理性

mysqlbinlog binlog命令

/var/lib/mysql/bin-log.000001 binlog日志文件所在路径

–start-position=起始位置值

–stop-position=结束位置的值

可以看到数据恢复了

2.指定时间恢复

#注意起始时间的合理性
mysqlbinlog --start-datetime="2022-10-14 9:00:18" --stop-datetime="2022-10-14 9:30:00"  /var/lib/mysql/bin-log.000001 | mysql -uroot -proot123456

3.生产环境binlog文件解析

​ 我们总是希望简简单单，然后生产环境往往是复复杂杂，比如我所遇到情况是，只提供了binlog文件，在本地进行解析出然后筛选出对应的update和insert的sql语句进行数据还原。

1.这里我将binlog文件存放到mysql的binlog文件目录在目录下

2.在/var/lib/mysql目录下执行如下指令

#/usr/bin/mysqlbinlog 或者 mysqlbinlog 命令
# --no-defaults：忽略默认值
# database指定要备份的库，多个库以空格分隔，也可以指定--all-databases参数来备份全部的数据库
# base64-output base64解码
# mysql-bin.052360 binlog文件
# 将解析后的数据写入到mysql-bin.052360.sql文件中
/usr/bin/mysqlbinlog --no-defaults --database=propertymodule_monomer  --base64-output=decode-rows -v --start-datetime='2019-04-11 00:00:00' --stop-datetime='2022-10-18 18:00:00'  mysql-bin.052360 > mysql-bin.052360.sql

参考：https://www.modb.pro/doc/4560

解析后文件示例：

可以看到，解析出来的sql并不是我们想要的，需要进一步转换。以insert语句为例，主要思路就是根据insert进行拆分，利用正则表达式将数据替换成我们需要的sql语句。具体细节还需要根据实际情况进行调整

insert语句解析

/**
 * 筛选出所有INSERT语句
 */
public class InsertSqlAnalysis {
    public static final String PATTERN = "yyyy-MM-dd HH:mm:ss";
    public static void main(String[] args) throws IOException {
        List<String> tableNameList = Lists.list(
                "charge_payment_record",
                "charge_payment_record_detail",
                "charge_receivables_account",
                "charge_receipt_invoice",
                "notify",
                "collection_order",
                "charge_receipt_verify_detail",
                "bill_invoice",
                "bill_invoice_detail",
                "cost_detail",
                "charge_notice_detail",
                "charge_notice");
        //String binlogPath = "E:\\opensource\\llp-springboot\\llp-remoteshell\\src\\main\\resources\\mysql-bin.052393.sql";
        //拷贝的文件
        //String destFilePath = "E:\\opensource\\llp-springboot\\llp-remoteshell\\src\\main\\resources\\mysql-bin.052393-NEW.sql";

        String sqlPath = "E:\\opensource\\llp-springboot\\llp-remoteshell\\src\\main\\resources\\binlog";
        String analysissqlPath = "E:\\opensource\\llp-springboot\\llp-remoteshell\\src\\main\\resources\\insertsql\\";
        File file = new File(sqlPath);
        if(file.isDirectory()){
            File[] files = file.listFiles();
            for (File f : files) {
                System.out.println(f.getAbsolutePath());
                String binlogPath = f.getAbsolutePath();
                String destFilePath = analysissqlPath+f.getName();
                analysisSql(tableNameList,binlogPath,destFilePath);
            }
        }
    }

    public static  void analysisSql(List<String> tableNameList,String binlogPath,String destFilePath) throws IOException {
        //创建bufferedReader
        BufferedReader bufferedReader = new BufferedReader(new FileReader(binlogPath));
        BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(destFilePath));
        //读取
        String line; //按行读取, 效率高
        StringBuffer buffer = new StringBuffer();
        //说明
        //1. bufferedReader.readLine() 是按行读取文件
        //2. 当返回null 时，表示文件读取完毕
        while ((line = bufferedReader.readLine()) != null) {
            //System.out.println(line);
            buffer.append(line).append("\n");
        }
        String content = buffer.toString();
        String[] split = content.split("### INSERT");
        for (int i = 0; i < split.length; i++) {
            String str = split[i];
            if (str.contains("INTO `propertymodule_monomer`")) {
                String sql = null;
                //截取INTO-# at
                try {
                    sql = str.substring(str.indexOf("INTO"), str.indexOf("# at"));
                } catch (Exception e) {
                    sql = str.substring(str.indexOf("INTO"), str.lastIndexOf("###"));
                }
                sql = sql.replaceAll("### SET", "VALUES(");
                sql = sql.replaceAll("###|@", "");
                sql = sql.replaceAll("(\\d+)=", ",");
                sql = sql.replaceFirst(",", "");
                Pattern compile = Pattern.compile("_time=(1665(\\d{6}))");
                Matcher matcher = compile.matcher(sql);
                while (matcher.find()) {
                    String time = matcher.group(0).split("=")[1];
                    SimpleDateFormat sdf = new SimpleDateFormat(PATTERN);
                    String date = sdf.format(new Date(Long.parseLong(time) * 1000)); // 时间戳转换日期
                    sql = sql.replaceAll(time, "'"+date+"'");
                }
                compile = Pattern.compile("\\d{20}");
                matcher = compile.matcher(sql);
                while (matcher.find()) {
                    String group = matcher.group(0);
                    sql = sql.replaceAll("\\("+group+"\\)","");
                }
                //start 指定表写出
                if(!CollectionUtils.isEmpty(tableNameList)){
                    writeByTableList(tableNameList,sql,bufferedWriter);
                }else {
                    sql = ("INSERT " + sql + ");").replaceAll("\\s+", " ");
                    bufferedWriter.write(sql);
                    bufferedWriter.newLine();
                    bufferedWriter.flush();
                }
            }
        }
        bufferedReader.close();
        bufferedWriter.close();
    }


    public static void writeByTableList(List<String> tableNameList,String sql,BufferedWriter bufferedWriter) throws IOException {
        String[] splitArray = sql.split("\n");
        for (int j = 0; j < splitArray.length; j++) {
            String[] split1 = splitArray[0].split("\\.");
            String tableName = split1[1].replaceAll("`", "");
            if (tableNameList.contains(tableName)) {
                //System.out.println("INSERT " + substring);
                sql = ("INSERT " + sql + ");").replaceAll("\\s+", " ");
                //每读取一行，就写入
                bufferedWriter.write(sql);
                bufferedWriter.newLine();
                bufferedWriter.flush();
                break;
            }
        }
    }

}

解析示例

INSERT INTO `propertymodule_monomer`.`bill_invoice` VALUES( 674559275489910784 ,'2022-10-13 10:19:50' ,'2022-10-13 10:19:50' ,20210513 ,554620341981417472 ,'221013101743001' ,'林同棪国际工程咨询（中国）有限公司' ,'915000006219140088' ,'重庆市渝北区芙蓉路6号' ,'023-67033597' ,'招商银行重庆北部新区支行' ,'123902061610901' ,NULL ,NULL ,NULL ,NULL ,'开票类型确认' ,NULL ,'WAIT_HANDLE' ,554620341981417474 ,NULL ,'674556062691004416' ,'221013100457001' ,'苏玫丹' ,'郭崇志' ,'蒋丹丽' ,NULL ,NULL ,NULL ,'重庆北部新区白杨路40号' ,'中信银行北部新区支行' ,NULL ,20210513 ,'重庆渝高物业管理有限责任公司' ,'023-63061583' ,'91500000202897821P' ,31934 ,36085 ,4151 ,'22101300674559275494105088' ,NULL ,NULL ,568035974438395904 ,'GOLD_TAX_PLATE' ,'661102111560' ,0 ,NULL ,NULL ,NULL ,NULL ,2021090303 ,552535579246596096 ,0 ,NULL ,'MONEYAFTERINVOICE' );
INSERT INTO `propertymodule_monomer`.`bill_invoice` VALUES( 674559275531853824 ,'2022-10-13 10:19:50' ,'2022-10-13 10:19:50' ,20210513 ,554620341981417472 ,'221013101743002' ,'林同棪国际工程咨询（中国）有限公司' ,'915000006219140088' ,'重庆市渝北区芙蓉路6号' ,'023-67033597' ,'招商银行重庆北部新区支行' ,'123902061610901' ,NULL ,NULL ,NULL ,NULL ,'开票类型确认' ,NULL ,'WAIT_HANDLE' ,554620341981417474 ,NULL ,'674556062691004416' ,'221013100457001' ,'苏玫丹' ,'郭崇志' ,'蒋丹丽' ,NULL ,NULL ,NULL ,'重庆北部新区白杨路40号' ,'中信银行北部新区支行' ,NULL ,20210513 ,'重庆渝高物业管理有限责任公司' ,'023-63061583' ,'91500000202897821P' ,21359 ,22000 ,641 ,'22101300674559275531853825' ,NULL ,NULL ,568035974438395904 ,'GOLD_TAX_PLATE' ,'661102111560' ,0 ,NULL ,NULL ,NULL ,NULL ,2021090303 ,552535579246596096 ,0 ,NULL ,'MONEYAFTERINVOICE' );

update语句解析

@SpringBootTest
public class UpdateSqlAnalysisOne {
    public static final String PATTERN = "yyyy-MM-dd HH:mm:ss";

    @Autowired
    private FileMapper fileMapper;

    @Test
    public void test() throws Exception {
        //List<String> tableNameList = Lists.list(
        //        //"charge_payment_record",
        //        //"charge_payment_record_detail",
        //        //"charge_receivables_account",
        //        //"charge_receipt_invoice",
        //        //"notify",
        //        //"collection_order",
        //        //"charge_receipt_verify_detail",
        //        //"bill_invoice",
        //        //"bill_invoice_detail",
        //        //"cost_detail",
        //        //"charge_notice",
        //        "charge_notice_detail"
        //       );
        List<String> tableNameList   = Lists.list("bill_invoice");

        String binlogPath = "E:\\opensource\\llp-springboot\\llp-remoteshell\\src\\main\\resources\\binlog";
        String analysisUpdateSqlPath = "E:\\opensource\\llp-springboot\\llp-remoteshell\\src\\main\\resources\\updatesql\\";
        BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(analysisUpdateSqlPath+"bill_invoice.sql"));
        File file = new File(binlogPath);
        if(file.isDirectory()){
            File[] files = file.listFiles();
            for (File f : files) {
                System.out.println(f.getAbsolutePath());
                String sqlPath = f.getAbsolutePath();
                //创建bufferedReader
                BufferedReader bufferedReader = new BufferedReader(new FileReader(sqlPath));
                //读取
                String line; //按行读取, 效率高
                StringBuffer buffer = new StringBuffer();
                //说明
                //1. bufferedReader.readLine() 是按行读取文件
                //2. 当返回null 时，表示文件读取完毕
                while ((line = bufferedReader.readLine()) != null) {
                    //System.out.println(line);
                    buffer.append(line).append("\n");
                }
                String content = buffer.toString();
                String[] split = content.split("### UPDATE `");
                for (int i = 0; i < split.length; i++) {
                    String str = split[i];
                    if (str.contains("WHERE") && str.contains("SET")) {
                        String table = "`" + str.substring(0, str.indexOf("WHERE")).replaceAll("###", "");
                        String tableName = table.substring(table.indexOf(".") + 2, table.lastIndexOf("`"));
                        if(!tableNameList.contains(tableName)){
                            continue;
                        }
                        Map<Integer, String> tableNameMap = getColumnNameByTableName(tableName);
                        String substring = null;
                        try {
                            substring = str.substring(0, str.indexOf("# at"));
                        } catch (Exception e) {
                            substring = str.substring(0, str.lastIndexOf("###"));
                        }
                        substring = substring.substring(substring.indexOf("### SET"));
                        substring = substring.replaceAll("###", "");
                        String[] splitarry = substring.split("@(\\d+)=");
                        StringBuilder stringBuilder = new StringBuilder();
                        for (int j = 1; j < splitarry.length; j++) {
                            if (!"SET".equals(splitarry[j].trim())) {
                                String col = tableNameMap.get(j) + "=" + splitarry[j].trim();
                                stringBuilder.append(" ").append(col).append(",").append("\n");
                            }
                        }
                        stringBuilder.deleteCharAt(stringBuilder.toString().lastIndexOf(","))
                                .append(" WHERE ")
                                .append(tableNameMap.get(1))
                                .append("=")
                                .append(splitarry[1].trim())
                                .append(";");
                        String sql = "UPDATE " + table + "SET\n" + stringBuilder.toString();
                        Pattern compile = Pattern.compile("_time=(1665(\\d{6}))");
                        Matcher matcher = compile.matcher(sql);
                        while (matcher.find()) {
                            String time = matcher.group(0).split("=")[1];
                            SimpleDateFormat sdf = new SimpleDateFormat(PATTERN);
                            String date = sdf.format(new Date(Long.parseLong(time) * 1000)); // 时间戳转换日期
                            sql = sql.replaceAll(time, "'"+date+"'");
                        }
                        compile = Pattern.compile("\\d{20}");
                        matcher = compile.matcher(sql);
                        while (matcher.find()) {
                            String group = matcher.group(0);
                            sql = sql.replaceAll("\\("+group+"\\)","");
                        }
                        //插入一个换行
                        bufferedWriter.write(sql.replaceAll("\n",""));
                        bufferedWriter.newLine();
                        bufferedWriter.flush();
                    }
                }
                bufferedReader.close();
            }
        }
        bufferedWriter.close();
    }




    public Map<Integer, String> getColumnNameByTableName(String tableName) throws SQLException, ClassNotFoundException {
        Map<Integer, String> map = new HashMap<>();
        List<String> columnNameByTableName = fileMapper.getColumnNameByTableName(tableName);
        for (int i = 0; i < columnNameByTableName.size(); i++) {
            map.put(i + 1, columnNameByTableName.get(i));
        }
        return map;
    }

}

@Repository
public interface FileMapper {

  List<String> getColumnNameByTableName(@Param("tableName") String tableName);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.llp.dao.FileMapper">
   <select id="getColumnNameByTableName" resultType="java.lang.String">
      SELECT COLUMN_NAME
      FROM information_schema.`COLUMNS`
      WHERE table_schema = (SELECT DATABASE())
        AND TABLE_NAME = #{tableName}
   </select>
</mapper>

解析示例

UPDATE `propertymodule_monomer`.`charge_notice` SET id=674186699265839104, create_time='2022-10-12 09:39:53', update_time='2022-10-13 10:31:18', version=5, customer_id=20210513, data_customer_id=520298495891066887, data_customer_name='重庆华藤友康体育产业有限公司', notice_time='2022-10-12 09:40:40', notice_id=526914842146951168, notice_name='陈思忆', invoice_amount=1765685, invoice_open_kind='INVOICEBEFOREMONEY', invoice_open_type='ZZSPTFP_DZ', invoice_status='B_PARTIALBILLING', urge_flag=0, next_send_date=NULL, send_num=NULL, notice_no='221012093714001', notice_date='2022:10:01', status='NOTIFIED', obs_id=674187553418862592, pdf_obs_id=674187558028402688, premises_id=520298495882678273, premises_name='体育中心', receivable_amount=1986242, remission_amount=0, general_deduction_amount=0, meters_deduction_amount=0, settle_status='A_SETTLED', tail_difference_amount=0, write_off_amount=1765685, arrears_amount=0, negative_amount=-220557 , write_off_date='2022-10-13 10:28:41', payee_id=20210513, payee_name='重庆渝高物业管理有限责任公司', notice_type='PERIODICITY', receivables_account_id=NULL WHERE id=674186699265839104;