使用說明:
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/

原始碼如下:

點選(此處)摺疊或開啟

#!/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
# %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 ;
}

從mysqldump全備獲取指定庫的sql

相關文章