Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2353672
  • 博文数量: 276
  • 博客积分: 5998
  • 博客等级: 大校
  • 技术积分: 5175
  • 用 户 组: 普通用户
  • 注册时间: 2010-12-24 14:43
文章分类

全部博文(276)

文章存档

2014年(25)

2013年(11)

2012年(69)

2011年(167)

2010年(4)

分类: Mysql/postgreSQL

2012-02-08 10:11:12

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

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

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

  1. /usr/local/mysql/bin/mysqldump -u -p test > /tmp/test.sql
  2. mysql> use test;
  3. mysql> select count(*) from test;
  4. ----------
  5. | count(*) |
  6. ----------
  7. | 1568394 |
  8. ----------
  9. 1 row in set (0.00 sec)
  10.  
  11. mysql> drop table test;
  12. Query OK, 0 rows affected (0.06 sec)
  13.  
  14. mysql> quit

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

  1. perl restore_table.pl -f /tmp/test.sql -t test > /tmp/test.sql
  2. /usr/local/mysql/bin/mysql -u -p -D test < /tmp/test.sql
  3. mysql> use test;
  4. Database changed
  5. mysql> select count(*) from test;
  6. ----------
  7. | count(*) |
  8. ----------
  9. | 1568394 |
  10. ----------
  11. 1 row in set (0.00 sec)

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

  1. cat restore_table.pl
  2. #!/usr/bin/perl -w
  3. use strict;
  4. use Getopt::Std;
  5.  
  6. my %opts;
  7. getopt('ft',\%opts);
  8. my $file=$opts{f};
  9. my $tag=$opts{t};
  10. my $pattern1="Table structure for table `$tag`";
  11. my $pattern2="Dumping data for table `$tag`";
  12.  
  13. my $pattern3="40000 ALTER TABLE `$tag` DISABLE KEYS";
  14. my $pattern4="40000 ALTER TABLE `$tag` ENABLE KEYS";
  15.  
  16. my $print=0;
  17. open FD,$file;
  18. while(<FD>){
  19.     my $content=$_;
  20.     $print=1 if $content =~ $pattern1;
  21.     print if $print == 1;
  22.     last if($content =~ $pattern2);
  23. }
  24. $print=0;
  25.  
  26. while(<FD>){
  27.         my $content=$_;
  28.         $print=1 if $content =~ $pattern3;
  29.         print if $print == 1;
  30.         last if($content =~ $pattern4);
  31. }
  32.  
  33. 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.

  1. mysqlbinlog --database=db_name --start-position=102655866 mysql1-bin.000312 > restore.sql
  2. 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.

  1. grep -B3 -w table_name restore.sql egrep -v '^--$' > restore_table.sql
  2. emacs restore_table.sql
  3. 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.


  1. mysqlbinlog mysql-bin.012001>a.sql

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

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


阅读(4281) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~