Mysql用全备恢复指定表

2009年05月27日　作者： 大头刚　
一、从全备中提取某一个表的信息，并进行恢复

恢复使用Mysqldump工具备份的数据，有个不方便的地方，就是在恢复的时候不能指定恢复到表，例如我备份了整个数据库，有N个表，但是我只是一个表需要恢复，如果我把这个数据库都恢复的话，岂不耗时耗力。

基于这个原因，和同事一起用perl写了个指定恢复到表的脚本。举例说明：
先把test库全库备份到指定文件，在把test表drop。

/usr/local/mysql/bin/mysqldump -u  -p test > /tmp/test.sql
mysql> use test;
mysql> select count(*) from test;
----------
| count(*) |
----------
|  1568394 |
----------
1 row in set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.06 sec)

mysql> quit

从全库的备份中挖出test表的备份，在将其恢复。

perl restore_table.pl -f /tmp/test.sql -t test > /tmp/test.sql
/usr/local/mysql/bin/mysql -u  -p -D test < /tmp/test.sql
mysql> use test;
Database changed
mysql> select count(*) from test;
----------
| count(*) |
----------
|  1568394 |
----------
1 row in set (0.00 sec)

OK，表已经恢复了。贴下这个脚本的代码：

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

声明一下，此脚本未经过严格测试，使用出现任何问题本人不负责任。

二、从binlog中提取某一个表的信息，并进行恢复

The second step was to restore the data from the time of the backup (which was about 10 hours ago) up to the point of the crash. The binary log was already spread across two files at that time. So I had to extract all the data manipulating statements for the database holding the crashed table from those two binlog files to a text file.

mysqlbinlog --database=db_name --start-position=102655866 mysql1-bin.000312 > restore.sql
mysqlbinlog --database=db_name mysql1-bin.000313 >> restore.sql

    The start-position is of course the position of the binlog at the time of the backup. Now I could do a search for all statements affecting the crashed table and feed them to mysql again.

grep -B3 -w table_name restore.sql egrep -v '^--$' > restore_table.sql
emacs restore_table.sql
mysql db_name < restore_table.sql

    As I knew that all those statements didn't contain any newlines I used a simple approach with grep (the -B3 giving me the lines with the meta information just before the actual statement), quickly checked the resulting file in a text editor (where I deleted the ALTER TABLE statement, too, to not have the crash happen again) and ran the queries.
    That's it. The table was now in exactly the same state as it was before the crash.

mysqlbinlog mysql-bin.012001>a.sql

grep -B3 -w tblauction a.sql >re.sql

就查找出了关于表tblauction的相关DML操作语句