恢复使用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 binarylog was already spread across two files at that time. So I had toextract all the data manipulating statements for the database holdingthe 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 thetime 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 metainformation just before the actual statement), quickly checked theresulting file in a text editor (where I deleted the ALTER TABLEstatement, 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操作语句