从mysqldump全备获取指定库的sql

使用说明:
mysqldump备份文件为/data/bak_dump.sql
1> 提取数据库db1的sql并保存在当前目录下
perl fetch_db_dumpsql.pl -s=bak_dump.sql -B=db1

2> 提取数据库db1和db2的sql并保存在当前目录下
perl fetch_db_dumpsql.pl -s=xxx.sql -B=db1,db2 

3> 提取数据库db1、db2和db3的sql并保存在/tmp/目录下
perl fetch_db_dumpsql.pl -s=xxx.sql -B=db1,db2,db3 -O=/tmp/

源码如下:

点击(此处)折叠或打开

1. #!/usr/bin/perl -w 
   
   use strict;
   use Getopt::Long qw(:config no_ignore_case);         # use GetOption function
   use Term::ANSIColor;
   
   
   # mysql config
   my $output_path = `pwd`;
   chomp($output_path);
   my $dbstr;
   my $srcfile='';
   
   
   # log file
   my $logfile;
   
   
   # ----------------------------------------------------
   # handle Ctrl+C
   sub catch_zap {
   my $signame = shift;
   print "\nExit Now...\n\n";
   exit;
   }
   $SIG{INT} = \&catch_zap;
   
   
   # ----------------------------------------------------------------------------------------
   # Main()
   # ----------------------------------------------------------------------------------------
   sub main{
   # get input params
   &get_option();
   
   
   &do_fetch();
   }
   
   
   &main();
   
   
   # ----------------------------------------------------------------------------------------
   # Func :  get input params
   # http://perldoc.perl.org/Getopt/Long.html#Command-Line-Options%2c-an-Introduction
   # ----------------------------------------------------------------------------------------
   sub get_option{
   GetOptions('h|help'=> \&help_print,
   's|srcfile=s' => \$srcfile,
   'B|databases=s' => \$dbstr,
   'O|outputdir=s' => \$output_path
   ) or help_print();
   # must set databases
   if (($srcfile eq '') or ($dbstr eq '')){
   &help_print();
   }
   
   
   $logfile = qq{$output_path/fetch.log};
   if (!(-e $logfile)){
     `touch $logfile`;
   }
   
   print "srcfile=$srcfile, databases=$dbstr, output=$output_path \n";
   }
   
   
   # ----------------------------------------------------------------------------------------
   # Func :  fetch dbs's sql
   # ----------------------------------------------------------------------------------------
   sub do_fetch{
   my @dbs = split(',',$dbstr);
   my %dbhash;
   my $spfir = ',';
   my $spsec = ';';
   my $m_srcfile = $srcfile;
   my $sh;
   
   
   # get head,tail info;
   my $headendpos=1;
   $sh = qq{grep -n '/\\\*!40101 SET SQL_MODE=\@OLD_SQL_MODE \\\*/;' $m_srcfile};
   my $res = `$sh`; chomp($res);
   my $tailbgpos = `echo '$res' | cut -d : -f 1`; chomp($tailbgpos);
   
   
   # get all db pos
   $sh = qq{grep -n -i \'^-- Current Database: `\' $m_srcfile };
   open FILE_ALLDB, " $sh | " or die "can't do shell:$sh";
   my ($bgpos,$endpos,$curpos,$curdb,$lastdb,$lastpos);
   my $i=1;
   while (my $line = ){
   # line format "no:USE `dbname`"
   chomp($line);
   my @arline = split(':',$line);
   $curpos = `echo '$line' | awk -F: \'{print \$1}\'`; chomp($curpos);
   $curdb = `echo '$line' | awk -F\\\` \'{print \$2}\'`; chomp($curdb);
   
   # first db
   if ($i > 1){
   $endpos = $curpos - 1;
   $dbhash{$lastdb} .= "$lastpos$spfir$endpos$spsec";
   }elsif ($i == 1){
   $headendpos = $curpos - 1;
   }
   
   $lastdb = $curdb;
   $lastpos = $curpos;
   
   
   $i++;
   }
   close FILE_ALLDB;
   
   
   # LAST DB. endpos = tailbgpos-1;
   $endpos = $tailbgpos - 1;
   $dbhash{$lastdb} .= "$lastpos$spfir$endpos$spsec";
   
   
   # do fetch
   my ($posstrdb, $posstr, @posardb, @posar);
   my $outfile;
   #foreach my $db (keys %dbhash){
   foreach my $db (@dbs){
   # fetch one db sql
   print "============= start fetch $db sqls\n";
   my $posstrdb = $dbhash{$db};
   if (defined($posstrdb)){
   #print "==== $db:$posstrdb\n";
   $outfile = qq{$output_path$db.sql};
   
   
   # head sql
   $sh = qq{sed -n '1,$headendpos p' $m_srcfile > $outfile};
   `$sh`;
   
   
   # db sql
   @posardb = split($spsec,$posstrdb);
   foreach $posstr (@posardb){
   @posar = split($spfir,$posstr);
   $bgpos = $posar[0];
   $endpos = $posar[1];
   $endpos = '$' unless defined($endpos);
   
   
   # fetch sql
   $sh = qq{sed -n '$bgpos,$endpos p' $m_srcfile >> $outfile};
   print colored ["Green "],"$db pos: $bgpos--$endpos";
   print colored ["reset"],"\n";
   `$sh`;
   
   
   #`$sh` or die "Can't fetch $db sqls"; 
   }
   
   
   # tail sql
   
   
   $sh = qq{sed -n '$tailbgpos,\$ p' $m_srcfile >> $outfile};
   `$sh`;
   }else{
   print colored ["red on_yellow"],"database:$db not exist!";
   print colored ["reset"],"\n";
   }
   }
   }
   
   
   # ----------------------------------------------------------------------------------------
   # Func : print help information
   # ----------------------------------------------------------------------------------------
   sub help_print{
   print < =========================================================================================
   Info  :
           Created By babaoqi
   Usage :
   Command line options :
   -h, --help          Print Help Info. 
   -s, --srcfile       src dumpsql file
   -B, --databases     fetch some databases. 
   -O, --outfile       output sql file. default:cur path
   
   
   Sample :
      shell> perl fetch_db_dumpsql.pl -s=xxx.sql -B=db1
      shell> perl fetch_db_dumpsql.pl -s=xxx.sql -B=db1,db2 
      shell> perl fetch_db_dumpsql.pl -s=xxx.sql -B=db1,db2,db3 -O=/tmp/
   =========================================================================================
   EOF
   exit ;
   }
   
   
   
   
   
   
   
2. 
   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26250550/viewspace-1065939/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26250550/viewspace-1065939/