MySQL基于ROW格式的数据恢复

谁还不是小白鼠

2016-05-03

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

这脚本含有注释以及使用说明，所以使用起来还是比较简单的，如果你会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

下面主要演示对一个表的增、删、修（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]# 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]# 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]#

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

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

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

mysql mysql update语句

安科网

MySQL基于ROW格式的数据恢复

谁还不是小白鼠

谁还不是小白鼠

相关推荐

MySQL中使用binlog时格式该如何选择

MySQL数据操作-DML语句的使用

详解 MySQL中count函数的正确使用方法

Golang操作MySql数据库的完整步骤记录

MySQL主从复制原理以及需要注意的地方

Mysql联表update数据的示例详解

MySQL数据类型优化原则

专业级的MySQL开发设计规范及SQL编写规范

Mysql 查询JSON结果的相关函数汇总

Mysql 实现字段拼接的三个函数

浅谈MySQL中的自增主键用完了怎么办

MySql索引使用策略分析

mysql 如何动态修改复制过滤器

MySQL ddl语句的使用

mysql 8.0.22 安装配置图文教程

解决Navicat Premium 连接 MySQL 8.0 报错\"1251\"的问题分析

MySQL 基于时间点的快速恢复方案

MySQL外键约束的实例讲解

MySQL用truncate命令快速清空一个数据库中的所有表

修改MySQL8.0 默认的数据目录(快捷操作无配置)

谁还不是小白鼠