MySQL基于ROW格式的数据恢复

大家都知道MySQL Binlog 有三种格式，分别是Statement、Row、Mixd。Statement记录了用户执行的原始SQL，而Row则是记录了行的修改情况，在MySQL 5.6以上的版本默认是Mixd格式，但为了保证复制数据的完整性，建议生产环境都使用Row格式，就前面所说的Row记录的是行数据的修改情况，而不是原始SQL。那么线上或者测试环境误操删除或者更新几条数据后，又想恢复，那怎么办呢？下面演示基于Binlog格式为Row的误操后数据恢复，那么怎么把Binlog解析出来生成反向的原始SQL呢？下面我们一起来学习。下面我们使用 binlog-rollback.pl 对数据进行恢复演示。（这脚本的作者不知道是谁，Github上也没找到这个脚本，所以无法标明出处），脚本是用Perl语言写的，非常好用的一个脚本，当然你也可以用Shell或者Python脚本来实现，下面是脚本的代码：#！/usr/lib/perl -wuse strict;
use warnings;use Class::Struct;
use Getopt::Long qw（:config no_ignore_case）; # GetOption
# register handler system signals
use sigtrap "handler", &sig_int, "normal-signals";# catch signal
sub sig_int（）{
my （$signals） = @_;
print STDERR "# Caught SIG$signals. ";
exit 1;
}my %opt;
my $srcfile;
my $host = "127.0.0.1";
my $port = 3306;
my （$user,$pwd）;
my （$MYSQL, $MYSQLBINLOG, $ROLLBACK_DML）;
my $outfile = "/dev/null";
my （%do_dbs,%do_tbs）;# tbname=>tbcol, tbcol: @n=>colname,type
my %tbcol_pos;my $SPLITER_COL = ",";
my $SQLTYPE_IST = "INSERT";
my $SQLTYPE_UPD = "UPDATE";
my $SQLTYPE_DEL = "DELETE";
my $SQLAREA_WHERE = "WHERE";
my $SQLAREA_SET = "SET";my $PRE_FUNCT = "========================== ";# =========================================================
# 基于row模式的binlog，生成DML（insert/update/delete）的rollback语句
# 通过mysqlbinlog -v 解析binlog生成可读的sql文件
# 提取需要处理的有效sql
# "### "开头的行.如果输入的start-position位于某个event group中间，则会导致"无法识别event"错误
#
# 将INSERT/UPDATE/DELETE 的sql反转,并且1个完整sql只能占1行
# INSERT: INSERT INTO => DELETE FROM, SET => WHERE
# UPDATE: WHERE => SET, SET => WHERE
# DELETE: DELETE FROM => INSERT INTO, WHERE => SET
# 用列名替换位置@{1,2,3}
# 通过desc table获得列顺序及对应的列名
# 特殊列类型value做特别处理
# 逆序
#
# 注意:
# 表结构与现在的表结构必须相同[谨记]
# 由于row模式是幂等的，并且恢复是一次性，所以只提取sql，不提取BEGIN/COMMIT
# 只能对INSERT/UPDATE/DELETE进行处理
# ========================================================
sub main{ # get input option
&get_options（）; #
&init_tbcol（）; #
&do_binlog_rollback（）;
}&main（）;
# ----------------------------------------------------------------------------------------
# Func : get options and set option flag
# ----------------------------------------------------------------------------------------
sub get_options{
#Get options info
GetOptions（\%opt,
"help", # OUT : print help info
"f|srcfile=s", # IN : binlog file
"o|outfile=s", # out : output sql file
"h|host=s", # IN : host
"u|user=s", # IN : user
"p|password=s", # IN : password
"P|port=i", # IN : port
"start-datetime=s", # IN : start datetime
"stop-datetime=s", # IN : stop datetime
"start-position=i", # IN : start position
"stop-position=i", # IN : stop position
"d|database=s", # IN : database, split comma
"T|table=s", # IN : table, split comma
"i|ignore", # IN : ignore binlog check ddl and so on
"debug", # IN : print debug information
） or print_usage（）; if （！scalar（%opt）） {
&print_usage（）;
} # Handle for options
if （$opt{"f"}）{
$srcfile = $opt{"f"};
}else{
&merror（"please input binlog file"）;
} $opt{"h"} and $host = $opt{"h"};
$opt{"u"} and $user = $opt{"u"};
$opt{"p"} and $pwd = $opt{"p"};
$opt{"P"} and $port = $opt{"P"};
if （$opt{"o"}） {
$outfile = $opt{"o"};
# 清空 outfile
`echo "" > $outfile`;
} #
$MYSQL = qq{mysql -h$host -u$user -p"$pwd" -P$port};
&mdebug（"get_options::MYSQL $MYSQL"）; # 提取binlog,不需要显示列定义信息，用-v，而不用-vv
$MYSQLBINLOG = qq{mysqlbinlog -v};
$MYSQLBINLOG .= " --start-position=".$opt{"start-position"} if $opt{"start-position"};
$MYSQLBINLOG .= " --stop-position=".$opt{"stop-position"} if $opt{"stop-postion"};
$MYSQLBINLOG .= " --start-datetime="".$opt{"start-datetime"}.""" if $opt{"start-datetime"};
$MYSQLBINLOG .= " --stop-datetime="$opt{"stop-datetime"}"" if $opt{"stop-datetime"};
$MYSQLBINLOG .= " $srcfile";
&mdebug（"get_options::MYSQLBINLOG $MYSQLBINLOG"）; # 检查binlog中是否含有 ddl sql: CREATE|ALTER|DROP|RENAME
&check_binlog（） unless （$opt{"i"}）; # 不使用mysqlbinlog过滤，USE dbname;方式可能会漏掉某些sql，所以不在mysqlbinlog过滤
# 指定数据库
if （$opt{"d"}）{
my @dbs = split（/,/,$opt{"d"}）;
foreach my $db （@dbs）{
$do_dbs{$db}=1;
}
} # 指定表
if （$opt{"T"}）{
my @tbs = split（/,/,$opt{"T"}）;
foreach my $tb （@tbs）{
$do_tbs{$tb}=1;
}
} # 提取有效DML SQL
$ROLLBACK_DML = $MYSQLBINLOG." | grep "^### "";
# 去掉注释: "### " -> ""
# 删除首尾空格
$ROLLBACK_DML .= " | sed "s/###\s*//g;s/\s*$//g"";
&mdebug（"rollback dml $ROLLBACK_DML"）;

# 检查内容是否为空
my $cmd = "$ROLLBACK_DML | wc -l";
&mdebug（"check contain dml sql $cmd"）;
my $size = `$cmd`;
chomp（$size）;
unless （$size >0）{
&merror（"binlog DML is empty:$ROLLBACK_DML"）;
};}
# ----------------------------------------------------------------------------------------
# Func : check binlog contain DDL
# ----------------------------------------------------------------------------------------
sub check_binlog{
&mdebug（"$PRE_FUNCT check_binlog"）;
my $cmd = "$MYSQLBINLOG ";
$cmd .= " | grep -E -i "^（CREATE|ALTER|DROP|RENAME）" ";
&mdebug（"check binlog has DDL cmd $cmd"）;
my $ddlcnt = `$cmd`;
chomp（$ddlcnt）; my $ddlnum = `$cmd | wc -l`;
chomp（$ddlnum）;
my $res = 0;
if （$ddlnum>0）{
# 在ddl sql前面加上前缀<DDL>
$ddlcnt = `echo "$ddlcnt" | sed "s/^/<DDL>/g"`;
&merror（"binlog contain $ddlnum DDL:$MYSQLBINLOG. ddl sql: $ddlcnt"）;
} return $res;
}
# ----------------------------------------------------------------------------------------
# Func : init all table column order
# if input --database --table params, only get set table column order
# ----------------------------------------------------------------------------------------
sub init_tbcol{
&mdebug（"$PRE_FUNCT init_tbcol"）;
# 提取DML语句
my $cmd .= "$ROLLBACK_DML | grep -E "^（INSERT|UPDATE|DELETE）"";
# 提取表名，并去重
#$cmd .= " | awk "{if （$1 ~ "^UPDATE"） {print $2}else {print $3}}" | uniq ";
$cmd .= " | awk "{if （$1 ~ "^UPDATE"） {print $2}else {print $3}}" | sort | uniq ";
&mdebug（"get table name cmd $cmd"）;
open ALLTABLE, "$cmd | " or die "can"t open file:$cmd "; while （my $tbname = <ALLTABLE>）{
chomp（$tbname）;
#if （exists $tbcol_pos{$tbname}）{
# next;
#}
&init_one_tbcol（$tbname） unless （&ignore_tb（$tbname））;

}
close ALLTABLE or die "can"t close file:$cmd "; # init tb col
foreach my $tb （keys %tbcol_pos）{
&mdebug（"tbname->$tb"）;
my %colpos = %{$tbcol_pos{$tb}};
foreach my $pos （keys %colpos）{
my $col = $colpos{$pos};
my （$cname,$ctype） = split（/$SPLITER_COL/, $col）;
&mdebug（" pos->$pos,cname->$cname,ctype->$ctype"）;
}
}
};
# ----------------------------------------------------------------------------------------
# Func : init one table column order
# ----------------------------------------------------------------------------------------
sub init_one_tbcol{
my $tbname = shift;
&mdebug（"$PRE_FUNCT init_one_tbcol"）;
# 获取表结构及列顺序
my $cmd = $MYSQL." --skip-column-names --silent -e "desc $tbname"";
# 提取列名，并拼接
$cmd .= " | awk -F"\t" "{print NR"$SPLITER_COL`"$1"`$SPLITER_COL"$2}"";
&mdebug（"get table column infor cmd $cmd"）;
open TBCOL,"$cmd | " or die "can"t open desc $tbname;"; my %colpos;
while （my $line = <TBCOL>）{
chomp（$line）;
my （$pos,$col,$coltype） = split（/$SPLITER_COL/,$line）;
&mdebug（"linesss=$line pos=$pos col=$col type=$coltype"）;
$colpos{$pos} = $col.$SPLITER_COL.$coltype;
}
close TBCOL or die "can"t colse desc $tbname"; $tbcol_pos{$tbname} = \%colpos;
}
# ----------------------------------------------------------------------------------------
# Func : rollback sql: INSERT/UPDATE/DELETE
# ----------------------------------------------------------------------------------------
sub do_binlog_rollback{
my $binlogfile = "$ROLLBACK_DML ";
&mdebug（"$PRE_FUNCT do_binlog_rollback"）; # INSERT|UPDATE|DELETE
my $sqltype;
# WHERE|SET
my $sqlarea;

my （$tbname, $sqlstr） = （"", ""）;
my （$notignore, $isareabegin） = （0,0）; # output sql file
open SQLFILE, ">> $outfile" or die "Can"t open sql file:$outfile"; # binlog file
open BINLOG, "$binlogfile |" or die "Can"t open file: $binlogfile";
while （my $line = <BINLOG>）{
chomp（$line）;
if （$line =~ /^（INSERT|UPDATE|DELETE）/）{
# export sql
if （$sqlstr ne ""）{
$sqlstr .= "; ";
print SQLFILE $sqlstr;
&mdebug（"export sql ".$sqlstr）;
$sqlstr = "";
} if （$line =~ /^INSERT/）{
$sqltype = $SQLTYPE_IST;
$tbname = `echo "$line" | awk "{print $3}"`;
chomp（$tbname）;
$sqlstr = qq{DELETE FROM $tbname};
}elsif （$line =~ /^UPDATE/）{
$sqltype = $SQLTYPE_UPD;
$tbname = `echo "$line" | awk "{print $2}"`;
chomp（$tbname）;
$sqlstr = qq{UPDATE $tbname};
}elsif （$line =~ /^DELETE/）{
$sqltype = $SQLTYPE_DEL;
$tbname = `echo "$line" | awk "{print $3}"`;
chomp（$tbname）;
$sqlstr = qq{INSERT INTO $tbname};
} # check ignore table
if（&ignore_tb（$tbname））{
$notignore = 0;
&mdebug（"<BINLOG>#IGNORE#:line:".$line）;
$sqlstr = "";
}else{
$notignore = 1;
&mdebug（"<BINLOG>#DO#:line:".$line）;
}
}else {
if（$notignore）{
&merror（"can"t get tbname"） unless （defined（$tbname））;
if （$line =~ /^WHERE/）{
$sqlarea = $SQLAREA_WHERE;
$sqlstr .= qq{ SET};
$isareabegin = 1;
}elsif （$line =~ /^SET/）{
$sqlarea = $SQLAREA_SET;
$sqlstr .= qq{ WHERE};
$isareabegin = 1;
}elsif （$line =~ /^@/）{
$sqlstr .= &deal_col_value（$tbname, $sqltype, $sqlarea, $isareabegin, $line）;
$isareabegin = 0;
}else{
&mdebug（"::unknown sql:".$line）;
}
}
}
}
# export last sql
if （$sqlstr ne ""）{
$sqlstr .= "; ";
print SQLFILE $sqlstr;
&mdebug（"export sql ".$sqlstr）;
}

close BINLOG or die "Can"t close binlog file: $binlogfile"; close SQLFILE or die "Can"t close out sql file: $outfile"; # 逆序
# 1！G: 只有第一行不执行G, 将hold space中的内容append回到pattern space
# h: 将pattern space 拷贝到hold space
# $！d: 除最后一行都删除
my $invert = "sed -i "1！G;h;$！d" $outfile";
my $res = `$invert`;
&mdebug（"inverter order sqlfile :$invert"）;
}# ----------------------------------------------------------------------------------------
# Func : transfer column pos to name
# deal column value
#
# &deal_col_value（$tbname, $sqltype, $sqlarea, $isareabegin, $line）;
# ----------------------------------------------------------------------------------------
sub deal_col_value（$$$$$）{
my （$tbname, $sqltype, $sqlarea, $isareabegin, $line） = @_;
&mdebug（"$PRE_FUNCT deal_col_value"）;
&mdebug（"input:tbname->$tbname,type->$sqltype,area->$sqlarea,areabegin->$isareabegin,line->$line"）;
my @vals = split（/=/, $line）;
my $pos = substr（$vals[0],1）;
my $valstartpos = length（$pos）+2;
my $val = substr（$line,$valstartpos）;
my %tbcol = %{$tbcol_pos{$tbname}};
my （$cname,$ctype） = split（/$SPLITER_COL/,$tbcol{$pos}）;
&merror（"can"t get $tbname column $cname type"） unless （defined（$cname） || defined（$ctype））;
&mdebug（"column infor:cname->$cname,type->$ctype"）; # join str
my $joinstr;
if （$isareabegin）{
$joinstr = " ";
}else{
# WHERE 被替换为 SET, 使用 , 连接
if （$sqlarea eq $SQLAREA_WHERE）{
$joinstr = ", ";
# SET 被替换为 WHERE 使用 AND 连接
}elsif （$sqlarea eq $SQLAREA_SET）{
$joinstr = " AND ";
}else{
&merror（"！！！！！！The scripts error"）;
}
}

#
my $newline = $joinstr; # NULL value
if （（$val eq "NULL"） && （$sqlarea eq $SQLAREA_SET））{
$newline .= qq{ $cname IS NULL};
}else{
# timestamp: record seconds
if （$ctype eq "timestamp"）{
$newline .= qq{$cname=from_unixtime（$val）};
# datetime: @n=yyyy-mm-dd hh::ii::ss
}elsif （$ctype eq "datetime"）{
$newline .= qq{$cname="$val"};
}else{
$newline .= qq{$cname=$val};
}
}
&mdebug（" old>$line new>$newline"）;

return $newline;
}# ----------------------------------------------------------------------------------------
# Func : check is ignore table
# params: IN table full name # format:`dbname`.`tbname`
# RETURN：
# 0 not ignore
# 1 ignore
# ----------------------------------------------------------------------------------------
sub ignore_tb（$）{
my $fullname = shift;
# 删除`
$fullname =~ s/`//g;
my （$dbname,$tbname） = split（/./,$fullname）;
my $res = 0;

# 指定了数据库
if （$opt{"d"}）{
# 与指定库相同
if （$do_dbs{$dbname}）{
# 指定表
if （$opt{"T"}）{
# 与指定表不同
unless （$do_tbs{$tbname}）{
$res = 1;
}
}
# 与指定库不同
}else{
$res = 1;
}
}
#&mdebug（"Table check ignore:$fullname->$res"）;
return $res;
}
# ----------------------------------------------------------------------------------------
# Func : print debug msg
# ----------------------------------------------------------------------------------------
sub mdebug{
my （@msg） = @_;
print "<DEBUG>@msg " if （$opt{"debug"}）;
}
# ----------------------------------------------------------------------------------------
# Func : print error msg and exit
# ----------------------------------------------------------------------------------------
sub merror{
my （@msg） = @_;
print "<Error>:@msg ";
&print_usage（）;
exit（1）;
}# ----------------------------------------------------------------------------------------
# Func : print usage
# ----------------------------------------------------------------------------------------
sub print_usage{
print <<EOF;
==========================================================================================
Command line options :
--help # OUT : print help info
-f, --srcfile # IN : binlog file. [required]
-o, --outfile # OUT : output sql file. [required]
-h, --host # IN : host. default "127.0.0.1"
-u, --user # IN : user. [required]
-p, --password # IN : password. [required]
-P, --port # IN : port. default "3306"
--start-datetime # IN : start datetime
--stop-datetime # IN : stop datetime
--start-position # IN : start position
--stop-position # IN : stop position
-d, --database # IN : database, split comma
-T, --table # IN : table, split comma. [required] set -d
-i, --ignore # IN : ignore binlog check contain DDL（CREATE|ALTER|DROP|RENAME）
--debug # IN : print debug informationSample :
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd"
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" -i
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" --debug
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -h "192.168.1.2" -u "user" -p "pwd" -P 3307
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" --start-position=107
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" --start-position=107 --stop-position=10000
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" -d "db1,db2"
shell> perl binlog-rollback.pl -f "mysql-bin.0000*" -o "/tmp/t.sql" -u "user" -p "pwd" -d "db1,db2" -T "tb1,tb2"
==========================================================================================
EOF
exit;
}
1;这脚本含有注释以及使用说明，所以使用起来还是比较简单的，如果你会Perl语言，相信也很容易看懂代码。binlog-rollback.pl的使用参数如下： [root@localhost mysql3306]# perl binlog-rollback.pl
==========================================================================================
Command line options :
--help # OUT : print help info
-f, --srcfile # IN : binlog file. [required]
-o, --outfile # OUT : output sql file. [required]
-h, --host # IN : host. default "127.0.0.1"
-u, --user # IN : user. [required]
-p, --password # IN : password. [required]
-P, --port # IN : port. default "3306"
--start-datetime # IN : start datetime
--stop-datetime # IN : stop datetime
--start-position # IN : start position
--stop-position # IN : stop position
-d, --database # IN : database, split comma
-T, --table # IN : table, split comma. [required] set -d
-i, --ignore # IN : ignore binlog check contain DDL（CREATE|ALTER|DROP|RENAME）
--debug # IN : print debug informationSample :
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd"
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" -i
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" --debug
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -h "192.168.1.2" -u "user" -p "pwd" -P 3307
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" --start-position=107
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" --start-position=107 --stop-position=10000
shell> perl binlog-rollback.pl -f "mysql-bin.000001" -o "/tmp/t.sql" -u "user" -p "pwd" -d "db1,db2"
shell> perl binlog-rollback.pl -f "mysql-bin.0000*" -o "/tmp/t.sql" -u "user" -p "pwd" -d "db1,db2" -T "tb1,tb2"
==========================================================================================
[root@localhost mysql3306]#下面主要演示对一个表的增、删、修（Insert/Delete/Update）操作，基于Binlog为Row格式的反向解析。细心看脚本的朋友都能看到这个脚本需要提供一个连接MySQL的用户，主要是为了获取表结构。下面我们测试一个普通用户并给予SELECT权限即可，默认是host是127.0.0.1，这个可以修改脚本，我这里按脚本默认的：<Test>[（none）]> GRANT SELECT ON *.* TO "recovery"@"127.0.0.1" identified by "123456";
Query OK, 0 rows affected （0.08 sec）<Test>[（none）]> flush privileges;
Query OK, 0 rows affected （0.04 sec）<Test>[（none）]>往xuanzhi库的表tb1里插入2行数据，记得binlog格式要为ROW：<Test>[xuanzhi]> show global variables like "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set （0.00 sec）<Test>[xuanzhi]> insert into xuanzhi.tb1 select 1,"aa";
Query OK, 1 row affected （0.01 sec）
Records: 1 Duplicates: 0 Warnings: 0<Test>[xuanzhi]> insert into xuanzhi.tb1 select 2,"cc";
Query OK, 1 row affected （0.01 sec）
Records: 1 Duplicates: 0 Warnings: 0<Test>[xuanzhi]> select * from xuanzhi.tb1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | cc |
+------+------+
2 rows in set （0.00 sec）<Test>[xuanzhi]>为了看到运行脚本在不指定库看到的效果，我这里再往test库的user表插入两行数据：<Test>[xuanzhi]> insert into test.user select 1,"user1",20;
Query OK, 1 row affected （0.03 sec）
Records: 1 Duplicates: 0 Warnings: 0<Test>[xuanzhi]> insert into test.user select 2,"user2",30;
Query OK, 1 row affected （0.01 sec）
Records: 1 Duplicates: 0 Warnings: 0<Test>[xuanzhi]>查看此时的此时处于那个binlog：<Test>[xuanzhi]> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| localhost-bin.000023 | 936 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set （0.00 sec）<Test>[xuanzhi]>下面运行脚本 binlog-rollback.pl ，不指定任何库和表的情况下，这时表把binlog里所有DML操作都生成反向的SQL（最新的DML会生成在输入文件的最前面）：[root@localhost mysql3306]# perl binlog-rollback.pl -f "localhost-bin.000023" -o "/data/t.sql" -u "recovery" -p "123456"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]#我们查看输出的文件：/data/t.sql[root@localhost mysql3306]# cat /data/t.sql
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`="user2" AND `age`=30;
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`="user1" AND `age`=20;
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`="bb";
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`="aa";可以看到，INSERT操作的反向操作就是DELETE，这里把所有库的DML操作都查出来了，在后面会演示找单个库或者表所产生的反向SQL。下面模拟运维人员、开发人员或者DBA误操删除数据，分别在不同的库删除一条记录：<Test>[xuanzhi]> delete from xuanzhi.tb1 where id=2;
Query OK, 1 row affected （0.06 sec）<Test>[xuanzhi]> delete from test.user where id=1;
Query OK, 1 row affected （0.00 sec）<Test>[xuanzhi]> 这个时候发现自己删除错了，需要恢复，刚好这些数据不在最新的备份里，正常的恢复方法有两种：一、是基于最新的完整备份+binlog进行数据恢复了，这时需要把备份导回去，还要找出Binlog DELETE前的pos位置，再进行binlog恢复，恢复完后再把记录恢复到误操的环境上。如果表很大，这时间要很久。
二、因为Binlog格式为ROW时，记录了行的修改，所以DELETE是可以看到所有列的值的，把binlog解析出来，找到被DELETE的记录，通过各种处理再恢复回去，但binlog不能基于一个库或表级别的解析，只能整个binlog解析再进行操作。以上的方法都比较消耗时间，当然使用binlog-rollback.pl脚本有点类似第二种方法，但是binlog-rollback.pl可以指定库或表进行反向解析，还可以指定POS点，效率相当更高一些。下面我们运行 binlog-rollback.pl 脚本，生成删除数据语句的反向SQL：[root@localhost mysql3306]# perl binlog-rollback.pl -f "localhost-bin.000023" -o "/data/t.sql" -u "recovery" -p "123456"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]#再次查看输出文件： [root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `test`.`user` SET `id`=1, `name`="user1", `age`=20;
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`="bb";
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`="user2" AND `age`=30;
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`="user1" AND `age`=20;
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`="bb";
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`="aa";[root@localhost mysql3306]#刚刚DELETE的2条记录已经生成了反向INSERT语句，这样恢复就简单多啦：INSERT INTO `test`.`user` SET `id`=1, `name`="user1", `age`=20;
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`="bb";下面我们模拟修改数据的时候，误修改了，如下：<Test>[xuanzhi]> select * from xuanzhi.tb1;
+------+------+
| id | name |
+------+------+
| 1 | aa |
+------+------+
1 row in set （0.00 sec）<Test>[xuanzhi]> select * from test.user;
+------+-------+------+
| id | name | age |
+------+-------+------+
| 2 | user2 | 30 |
+------+-------+------+
1 row in set （0.00 sec）<Test>[xuanzhi]> update xuanzhi.tb1 set name = "MySQL" where id=1;
Query OK, 1 row affected （0.00 sec）
Rows matched: 1 Changed: 1 Warnings: 0<Test>[xuanzhi]> update test.user set age = 20 where id = 2;
Query OK, 1 row affected （0.01 sec）
Rows matched: 1 Changed: 1 Warnings: 0<Test>[xuanzhi]>这个时候发现修改错数据了，需要还原，同样可以使用脚本binlog-rollback.pl 进行对所在Binlog的DML生成反向的SQL，进行恢复：[root@localhost mysql3306]# perl binlog-rollback.pl -f "localhost-bin.000023" -o "/data/t.sql" -u "recovery" -p "123456"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]#再查看输出文件：[root@localhost mysql3306]# cat /data/t.sql
UPDATE `test`.`user` SET `id`=2, `name`="user2", `age`=30 WHERE `id`=2 AND `name`="user2" AND `age`=20;
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`="aa" WHERE `id`=1 AND `name`="MySQL";
INSERT INTO `test`.`user` SET `id`=1, `name`="user1", `age`=20;
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`="bb";
DELETE FROM `test`.`user` WHERE `id`=2 AND `name`="user2" AND `age`=30;
DELETE FROM `test`.`user` WHERE `id`=1 AND `name`="user1" AND `age`=20;
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`="bb";
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`="aa";[root@localhost mysql3306]#可以看到生成了反向的UPDATE语句：UPDATE `test`.`user` SET `id`=2, `name`="user2", `age`=30 WHERE `id`=2 AND `name`="user2" AND `age`=20;
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`="aa" WHERE `id`=1 AND `name`="MySQL";下面进行指定库的反向解析，参数为（-d）[root@localhost mysql3306]# perl binlog-rollback.pl -f "localhost-bin.000023" -o "/data/t.sql" -u "recovery" -p "123456" -d "xuanzhi"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`="aa" WHERE `id`=1 AND `name`="MySQL";
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`="bb";
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`="bb";
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`="aa";[root@localhost mysql3306]#可以看到输入的文件只含xuanzhi库的所有DML的反向SQL。下面进行指定库下某个表的反向解析，参数为：-T （为了看到效果在xuanzhi库下的tb2表删除一些记录）：<Test>[xuanzhi]> select * from tb2;
+------+------+
| id | name |
+------+------+
| 1 | aa |
| 2 | bb |
| 3 | cc |
+------+------+
3 rows in set （0.04 sec）<Test>[xuanzhi]> delete from xuanzhi.tb2 where id <2;
Query OK, 1 row affected （0.02 sec）<Test>[xuanzhi]>这个时候应该如果只指定xuanzhi库,那么tb1和tb2的DML操作的反向操作都会记录下来：[root@localhost mysql3306]# perl binlog-rollback.pl -f "localhost-bin.000023" -o "/data/t.sql" -u "recovery" -p "123456" -d "xuanzhi"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `xuanzhi`.`tb2` SET `id`=1, `name`="aa";
UPDATE `xuanzhi`.`tb1` SET `id`=1, `name`="aa" WHERE `id`=1 AND `name`="MySQL";
INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`="bb";
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=2 AND `name`="bb";
DELETE FROM `xuanzhi`.`tb1` WHERE `id`=1 AND `name`="aa";[root@localhost mysql3306]#指定单个表tb2：[root@localhost mysql3306]# perl binlog-rollback.pl -f "localhost-bin.000023" -o "/data/t.sql" -u "recovery" -p "123456" -d "xuanzhi" -T "tb2"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `xuanzhi`.`tb2` SET `id`=1, `name`="aa";[root@localhost mysql3306]#因为上面删除了一条tb2的数据，所有这个文件就对应生成一条tb2的INSERT记录
下面进行POS点生成反向SQL：（--start-position= --stop-position=）从上面的binlog可以看到开始的--start-position=1557 结束的--stop-position=1981，这一段binlog里做了UPDATE `test`.`user` ... 和 DELETE FROM `xuanzhi`.`tb2` ... 的操作，那么用binlog-rollback.pl应该会生成一个UPDATE和一个INSERT语句[root@localhost mysql3306]# perl binlog-rollback.pl -f "localhost-bin.000023" -o "/data/t.sql" -u "recovery" -p "123456" --start-position=1557 --stop-position=1981
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost mysql3306]# cat /data/t.sql
INSERT INTO `xuanzhi`.`tb2` SET `id`=1, `name`="aa";
UPDATE `test`.`user` SET `id`=2, `name`="user2", `age`=30 WHERE `id`=2 AND `name`="user2" AND `age`=20;[root@localhost mysql3306]#更多的测试，就看同学们了，有测试不当的地方请告诉我，大家一起学习。总结：一、感谢那些有分享精神的大神们，让我们学到了更多的东西，但开源的脚本需要多测试。二、误操的情况，时有发生，所以我们要做好备份，做好一些数据恢复的测试。本文永久更新链接地址