MySQL基于ROW格式的数据恢复

139次阅读

共计 24166 个字符，预计需要花费 61 分钟才能阅读完成。

大家都知道 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 -w

use 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.\n”;
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\n\t$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\n\t$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\n\t$ROLLBACK_DML”);

# 检查内容是否为空
my $cmd = “$ROLLBACK_DML | wc -l”;
&mdebug(“check contain dml sql\n\t$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\n\t$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:\n$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\n\t$cmd”);
open ALLTABLE, “$cmd | ” or die “can’t open file:$cmd\n”;

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\n”;

# 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(“\tpos->$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\n\t$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\n\t\tpos=$pos\n\t\tcol=$col\n\t\ttype=$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 .= “;\n”;
print SQLFILE $sqlstr;
&mdebug(“export sql\n\t”.$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 .= “;\n”;
print SQLFILE $sqlstr;
&mdebug(“export sql\n\t”.$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(“\told>$line\n\tnew>$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\n” if ($opt{‘debug’});
}

# —————————————————————————————-
# Func : print error msg and exit
# —————————————————————————————-
sub merror{
my (@msg) = @_;
print “<Error>:@msg\n”;
&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 information

Sample :
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 information

Sample :
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)

往 xuanzhi 库的表 tb1 里插入 2 行数据，记得 binlog 格式要为 ROW：

<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]>

下面运行脚本 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]#

更多的测试，就看同学们了，有测试不当的地方请告诉我，大家一起学习。

总结：一、感谢那些有分享精神的大神们，让我们学到了更多的东西，但开源的脚本需要多测试。

二、误操的情况，时有发生，所以我们要做好备份，做好一些数据恢复的测试。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-05/130924.htm

正文完

星哥说事-微信公众号