使用mysqlbinlog恢复误删数据案例

1.确保MYSQL里的BINLOG开启、binlog_format为ROW和binlog_row_image为FULL

2.对表tt里进行操作

 

2.1.更新表tt的数据：

mysql> update tt set name='2000' where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.14 sec)

 

2.2.删除tt表里其中一条数据
mysql> delete from tt where id ='4';
Query OK, 1 row affected (0.03 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

 

2.3插入一条数据进tt表
mysql> insert into tt(id,name) values (8,3000);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

被修改的步骤
update
delete
insert

修复步骤与之相反
insert  ==>  delete
delete  ==>  insert
update  ==>  update

=======================================================

3.通过mysqlbinlog工具查找对应的恢复信息
基于开始/结束时间
 mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d 库名 二进制文件

基于pos值
 mysqlbinlog --start-postion=107 --stop-position=1000 -d 库名 二进制文件

3.1导出相关部分
  mysqlbinlog --no-defaults --start-datetime='2019-11-10 21:30:00' --stop-datetime='2019-11-10 22:00:00' -d test --base64-output=decode-rows -v -v /mysqldata/binlog/mysql-bin.000001 | grep -B 15 -A 15 'INSERT' >/mysqldata/rebin/ttinsert.txt

  mysqlbinlog --no-defaults --start-datetime='2019-11-10 21:30:00' --stop-datetime='2019-11-10 22:00:00' -d test --base64-output=decode-rows -v -v /mysqldata/binlog/mysql-bin.000001 | grep -B 15 -A 15 'DELETE FROM' >/mysqldata/rebin/ttdelete.txt
 
  mysqlbinlog --no-defaults --start-datetime='2019-11-10 21:30:00' --stop-datetime='2019-11-10 22:00:00' -d test --base64-output=decode-rows -v -v /mysqldata/binlog/mysql-bin.000001 | grep -B 15 -A 15 'UPDATE' >/mysqldata/rebin/ttupdate.txt
 
3.2相关内容：
cat ttinsert.txt
cat ttdelete.txt
cat ttupdate.txt

 

========================================================
删除对应# at xxxx之前和之后的无关数据

3.3 将ttinsert.txt中的记录转换为SQL语句
cat ttinsert.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/INSERT INTO/DELETE FROM/g;s/SET/WHERE/g;' | sed -r 's/(@2.*),/\1;/g;s/(@1.*),/\1 and/g'|sed 's/@1/id/g;s/@2/name/g;' > ttdelete.sql

3.4 将ttdelete.txt中的记录转换为SQL语句
cat ttdelete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@2.*),/\1;/g' | sed 's/@[1-9]=//g' > ttinsert.sql

3.5 将ttupdate.txt中的记录转换为SQL语句
sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' ttupdate.txt | sed -r '/WHERE/{:a;N;/@2/!ba;s/###   @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' |sed -r 's/(@2.*),/\1/g'| sed '/^$/d'| sed 's/@1/id/g;s/@2/name/g;' > ttupdate.sql
  
===========================================================
4.恢复操作：
修复步骤与之相反
insert  ==>  delete
delete  ==>  insert
update  ==>  update

进入MYSQL修复tt

4.1先备份tt。
create table tt2 select * from tt

 

4.2修复tt

修复插入部分
source /mysqldata/rebin/ttdelete.sql

修复删除部分
source /mysqldata/rebin/ttinsert.sql
mysql> select * from tt;

修复更新部分
source /mysqldata/rebin/ttupdate.sql