实现mysql单表备份的脚本

作用：

　　备份ＤＢ中的数据到单表，以防止增加表后少备份了。 

#!/usr/bin/perl
#　mysql meg
#　wubx
use strict;
use DBI;
my $host='172.16.100.1';
my $port='3306';
my $user='root;
my $passwd='';
my $dsn="dbi:mysql:hostname=$host:port=$port";
my $backup="/backup";
my $dbh= DBI->connect($dsn,$user,'');
unless ($dbh)
{
        print "Connect error!\n";

}
my ($sec,$min,$hour,$mday,$mon,$year,$wday)=localtime(time);
$mon = $mon+1;
$year = $year+1900;
my $terday = sprintf("%04d-%02d-%02d",$year,$mon,$mday);
print "cd $backup\n";
my $sql="show databases";
my $sth= $dbh->prepare("$sql");
$sth->execute;
while (my @row=$sth->fetchrow_array())
{
        print "mkdir $row[0]_$terday && cd $row[0]_$terday\n";
        $dbh->do("use $row[0]");
        my $show_table="show tables";
        my $sth_table= $dbh->prepare("$show_table");
        $sth_table->execute;
        while ( my @rr = $sth_table->fetchrow_array())
        {
                print "mysqldump --opt $row[0] $rr[0]|gzip>$row[0]_$rr[0].sql.gz\n";
        }
        print "cd ..\n";
}
$dbh->disconnect();

调用方法用crontab 结果输出到一个文件，然后在在执行那个文件。