mysql dump 从库_从mysqldump全备获取指定库的sql

下面是编程之家 jb51.cc 通过网络收集整理的代码片段。

编程之家小编现在分享给大家，也给大家做个参考。

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

}

以上是编程之家(jb51.cc)为你收集整理的全部代码内容，希望文章能够帮你解决所遇到的程序开发问题。

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

如您喜欢交流学习经验，点击链接加入交流1群：1065694478(已满)交流2群：163560250