MySQL binlog反解析

反解析delete语句

背景：delete table忘了加条件导致整张表被删除

恢复方式：直接从binlog里反解析delete语句为insert进行恢复

导出删指定表的DELETE语句：

# mysqlbinlog  -vv -d dbname mysql-bin.000048 | awk '/DELETE FROM/ && (/dbname.tbname/ || /`dbname.`tbname`/){

    while(1){

        print $0;

        getline;

        if($0 !~ /^###/){

            break;

        };

    }

}' > dbname.tbname.delete.txt

去掉binlog每行开头的#

# sed -i 's/^### //g' dbname.tbname.delete.txt

语句转换 DELETE --> INSERT

# sed -i "s/^DELETE FROM/INSERT INTO/g" dbname.tbname.delete.txt

# sed -i "s/^WHERE/VALUES(/g" dbname.tbname.delete.txt

# sed -i '/@13=.*/a );' dbname.tbname.delete.txt

字段处理

# cat dbname.tbname.delete.txt | awk -F"=|/*" '{

    if($0 ~ /^INSERT|^VALUES|^);/){

        print $0;

    }else{

        printf $2",";

    };

}' > dbname.tbname.insert.sql

去掉values()最后一个逗号

# sed -i "s/,);$/);/g" dbname.tbname.insert.sql

至此就将delete语句反解析为insert语句了

重新导入数据库