binlog恢复mysql删除的记录

  sre

开启二进制
查看并开启show variables like ‘%log_bin%’;
如果没有开启,设置加入参数log-bin=mysql-binlog
日志格式为ROW
binlog_format=ROW
获取二进制日志内容
mysqlbinlog –start-datetime=’2016-12-20 09:27:54′ –stop-datetime=’2016-12-20 09:30:40′ -vv MySQL-binlog.000001 >/root/delete.txt — 制定库名的,-d db1

cat delete.txt |sed -n ‘/###/p’ |sed ‘s/### //;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/;s/WHERE/SELECT/;s/@[1-9]=//;s/@[1-9]=//’ |sed ‘/-1/s/,/;/ >>insert.sql

————————-误update数据恢复

1。根据set值查找日志路径
/opt/local/mysql/bin/mysqlbinlog –no-defaults -v -v –base64-output=DECODE-ROWS mysql-bin.000002|grep -B 15 ‘myy’|more /*/;
这个用3的参数。
2.创建文本文件
touch update.txt
3。输出日志到文本文件中
/opt/local/mysql/bin/mysqlbinlog –no-defaults -v -v –base64-output=DECODE-ROWS mysql-bin.000002|sed -n ‘/# at 5980/,/COMMIT/p’>/opt/update.txt

注:at 5980 为update日志开始位置。这个需要先找到日志中误update的日志内容分析。

4.将binlog转换成SQL语句。
sed ‘/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}’ /opt/update.txt|sed -r ‘/WHERE/{:a;N;/@3/!ba;s/### @2.*//g}’|sed ‘s/### //g;s/\/\*.*/,/g’|sed ‘/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g’|sed ‘/^$/d’>recover.sql

注:a.@3为最后一个变量。如果有20个变量,则此处为@20。
b.### @2 此处#与@之前空格数为3个,不能多不能少。
5。将@变量更换为相应字段名
sed -i ‘s/@1/id/g;s/@2/name/g;s/@3/age/g’ recover.sql
6。将set后面where前面最后一个变量后的逗号去掉。
sed -i -r ‘s/(age=.*),/\1/g’ recover.sql
7。将recover.sql在mysql命令行执行,数据即可恢复。

4 thoughts on - binlog恢复mysql删除的记录

  • 1、1.binlog必须开启,并且必须是ROW模式。如果这个前提不满足,那么下面这种方法恢复不了数据。
    查看binlog是否开启 show variables like ‘%log_bin%’
    查看binlog的模式 show variables like ‘%binlog_format%’
    2、查看binlog文件的更新时间,确认删除数据日志存在于哪个binlog中。

    3、找到binlog中删除语句放入新建delete.txt文本中。
    /opt/local/mysql/bin/mysqlbinlog –no-defaults –base64-output=decode-rows -v -v mysql-bin.000083|sed -n ‘/### DELETE FROM `cop`.`sys_dept_post_detail`/,/COMMIT/p’>/opt/delete.txt
    4、将delete语句反转为insert语句,输出到sql文本中。
    cat /opt/delete.txt|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-6]=//g’>/opt/insert.sql

    注意:命令中的@3需要根据当前表字段的数量进行修改,例如你要恢复的表字段有20个,则此处要改为@20;
    5、将生成的insert.sql导入数据库中。

  • 1.DELETE
     ・DML语言
     ・可以回退
     ・可以有条件的删除
    DELETE FROM 表名
      WHERE 条件
    2.TRUNCATE TABLE
     ・DDL语言
     ・无法回退
     ・默认所有的表内容都删除
     ・删除速度比delete快。
    TRUNCATE TABLE 表名

LEAVE A COMMENT

Captcha Code