MySQL基于ROW格式的数据恢复
生活随笔
收集整理的這篇文章主要介紹了
MySQL基于ROW格式的数据恢复
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
?? 大家都知道MySQL Binlog 有三種格式，分別是Statement、Row、Mixd。Statement記錄了用戶執(zhí)行的原始SQL，而Row則是記錄了行的修改情況，在MySQL 5.6以上的版本默認是Mixd格式，但為了保證復(fù)制數(shù)據(jù)的完整性，建議生產(chǎn)環(huán)境都使用Row格式，就前面所說的Row記錄的是行數(shù)據(jù)的修改情況，而不是原始SQL。那么線上或者測試環(huán)境誤操刪除或者更新幾條數(shù)據(jù)后，又想恢復(fù)，那怎么辦呢？下面演示基于Binlog格式為Row的誤操后數(shù)據(jù)恢復(fù)，那么怎么把Binlog解析出來生成反向的原始SQL呢？下面我們一起來學習。
?
下面我們使用 binlog-rollback.pl 對數(shù)據(jù)進行恢復(fù)演示。（這腳本的作者不知道是誰，Github上也沒找到這個腳本，所以無法標明出處），腳本是用Perl語言寫的，非常好用的一個腳本，當然你也可以用Shell或者Python腳本來實現(xiàn)，下面是腳本的代碼：
#!/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.\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反轉(zhuǎn),并且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獲得列順序及對應(yīng)的列名 # 特殊列類型value做特別處理 # 逆序 # # 注意: # 表結(jié)構(gòu)與現(xiàn)在的表結(jié)構(gòu)必須相同[謹記] # 由于row模式是冪等的，并且恢復(fù)是一次性，所以只提取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 infoGetOptions(\%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 optionsif ($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過濾# 指定數(shù)據(jù)庫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");# 檢查內(nèi)容是否為空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 colforeach 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");# 獲取表結(jié)構(gòu)及列順序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|DELETEmy $sqltype;# WHERE|SETmy $sqlarea;my ($tbname, $sqlstr) = ('', '');my ($notignore, $isareabegin) = (0,0);# output sql fileopen SQLFILE, ">> $outfile" or die "Can't open sql file:$outfile";# binlog fileopen BINLOG, "$binlogfile |" or die "Can't open file: $binlogfile";while (my $line = <BINLOG>){chomp($line);if ($line =~ /^(INSERT|UPDATE|DELETE)/){# export sqlif ($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 tableif(&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 sqlif ($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: 只有第一行不執(zhí)行G, 將hold space中的內(nèi)容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 strmy $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 valueif (($val eq 'NULL') && ($sqlarea eq $SQLAREA_SET)){$newline .= qq{ $cname IS NULL};}else{# timestamp: record secondsif ($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;# 指定了數(shù)據(jù)庫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' -ishell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --debugshell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -h '192.168.1.2' -u 'user' -p 'pwd' -P 3307shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107 --stop-position=10000shell> 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' ========================================================================================== EOFexit; }1; View Code這腳本含有注釋以及使用說明，所以使用起來還是比較簡單的，如果你會Perl語言，相信也很容易看懂代碼。binlog-rollback.pl的使用參數(shù)如下：
[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' -ishell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --debugshell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -h '192.168.1.2' -u 'user' -p 'pwd' -P 3307shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107shell> perl binlog-rollback.pl -f 'mysql-bin.000001' -o '/tmp/t.sql' -u 'user' -p 'pwd' --start-position=107 --stop-position=10000shell> 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的用戶，主要是為了獲取表結(jié)構(gòu)。下面我們測試一個普通用戶并給予SELECT權(quán)限即可，默認是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行數(shù)據(jù)，記得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表插入兩行數(shù)據(jù)：
<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操作都查出來了，在后面會演示找單個庫或者表所產(chǎn)生的反向SQL。
?
下面模擬運維人員、開發(fā)人員或者DBA誤操刪除數(shù)據(jù)，分別在不同的庫刪除一條記錄：
<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]>這個時候發(fā)現(xiàn)自己刪除錯了，需要恢復(fù)，剛好這些數(shù)據(jù)不在最新的備份里，正常的恢復(fù)方法有兩種：
一、是基于最新的完整備份+binlog進行數(shù)據(jù)恢復(fù)了，這時需要把備份導回去，還要找出Binlog DELETE前的pos位置，再進行binlog恢復(fù)，恢復(fù)完后再把記錄恢復(fù)到誤操的環(huán)境上。如果表很大，這時間要很久。
二、因為Binlog格式為ROW時，記錄了行的修改，所以DELETE是可以看到所有列的值的，把binlog解析出來，找到被DELETE的記錄，通過各種處理再恢復(fù)回去，但binlog不能基于一個庫或表級別的解析，只能整個binlog解析再進行操作。
以上的方法都比較消耗時間，當然使用binlog-rollback.pl腳本有點類似第二種方法，但是binlog-rollback.pl可以指定庫或表進行反向解析，還可以指定POS點，效率相當更高一些。
?
下面我們運行 binlog-rollback.pl 腳本，生成刪除數(shù)據(jù)語句的反向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條記錄已經(jīng)生成了反向INSERT語句，這樣恢復(fù)就簡單多啦：
INSERT INTO `test`.`user` SET `id`=1, `name`='user1', `age`=20; INSERT INTO `xuanzhi`.`tb1` SET `id`=2, `name`='bb';?
下面我們模擬修改數(shù)據(jù)的時候，誤修改了，如下：
<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]>這個時候發(fā)現(xiàn)修改錯數(shù)據(jù)了，需要還原，同樣可以使用腳本binlog-rollback.pl 進行對所在Binlog的DML生成反向的SQL，進行恢復(fù)：
[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';?
下面進行指定庫的反向解析，參數(shù)為（-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。
?
下面進行指定庫下某個表的反向解析，參數(shù)為：-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]>這個時候應(yīng)該如果只指定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的數(shù)據(jù)，所有這個文件就對應(yīng)生成一條tb2的INSERT記錄
下面進行POS點生成反向SQL：（--start-position=? --stop-position=）
從上面的binlog可以看到開始的--start-position=1557 結(jié)束的--stop-position=1981，這一段binlog里做了UPDATE `test`.`user`?...?和 DELETE FROM `xuanzhi`.`tb2` ... 的操作，那么用binlog-rollback.pl應(yīng)該會生成一個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]#更多的測試，就看同學們了，有測試不當?shù)牡胤秸埜嬖V我，大家一起學習。
?
?
總結(jié)： 一、感謝那些有分享精神的大神們，讓我們學到了更多的東西，但開源的腳本需要多測試。
??????? ?二、誤操的情況，時有發(fā)生，所以我們要做好備份，做好一些數(shù)據(jù)恢復(fù)的測試。
???????? 三、該腳本在處理比較在的binlog時，會經(jīng)常出現(xiàn)些小問題
?
?
| ? 作者：陸炫志 出處：xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111 您的支持是對博主最大的鼓勵，感謝您的認真閱讀。本文版權(quán)歸作者所有，歡迎轉(zhuǎn)載，但請保留該聲明。 ? |
轉(zhuǎn)載于:https://www.cnblogs.com/xuanzhi201111/p/5284084.html
總結(jié)
以上是生活随笔為你收集整理的MySQL基于ROW格式的数据恢复的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Android AsyncTask分析
- 下一篇： IOS之@property 的理解