mysql还原单个库或单个表

一.从全备中抽取某个表进行恢复

cat restore_table.pl
#!/usr/bin/perl -w
use strict;
use Getopt::Std;

my %opts;
getopt('ft',\%opts);
my $file=$opts{f};
my $tag=$opts{t};
my $pattern1="Table structure for table `$tag`";
my $pattern2="Dumping data for table `$tag`";

my $pattern3="40000 ALTER TABLE `$tag` DISABLE KEYS";
my $pattern4="40000 ALTER TABLE `$tag` ENABLE KEYS";

my $print=0;
open FD,$file;
while(<FD>){
    my $content=$_;
    $print=1 if $content =~ $pattern1;
    print if $print == 1;
    last if($content =~ $pattern2);
}
$print=0;

while(<FD>){
        my $content=$_;
        $print=1 if $content =~ $pattern3;
        print if $print == 1;
        last if($content =~ $pattern4);
}
close FD

perl restore_table.pl -f /tmp/test.sql -t test > /tmp/test.sql

二.从全备中抽取某个库
1.找出创建库的开始行数和结束行数

grep -n -i 'create database' all.sql > create_database.log &

./read_file.pl all.sql 526312268 576328859 &

#!/usr/bin/perl -w

use strict;
my $in_file = $ARGV[0];
my $out_file = $ARGV[1];
my $start = $ARGV[2];
my $end = $ARGV[3];

open FH, $in_file or die "can't open file $in_file\n";
open O_FH, ">>$out_file" or die "can't open file $out_file\n";
my $index = 0;
while(<FH>){
        $index++;
        my $line = $_;
        last if($index>$end);

        if($index>=$start){
            print O_FH $line;
        }
}
close FH;
close O_FH;

三.从binlog中抽取某个库和表

for i in $(seq 3828 3834); do mysqlbinlog --database=d_auction_gx3 binlog.00$i >> binlog_d_auction_gx3.sql; done
mysqlbinlog binlog.003835 --stop-date='2011-09-02 12:59:59' --database=d_point_gx3 >> binlog_d_point_gx3.sql

1.grep -B3 -w table_name restore.sql egrep -v '^--$' > restore_table.sql

2.emacs restore_table.sql