binlog恢复mysql删除的记录
开启二进制
查看并开启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命令行执行,数据即可恢复。
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 表名
查看指定binlog文件的内容
mysql> show binlog events in ‘mysql-bin.000002’;