mysql 表备份_实现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 结果输出到一个文件，然后在在执行那个文件。