实现mysql单表备份的脚本

作用:

  备份DB中的数据到单表,以防止增加表后少备份了。 

#!/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 结果输出到一个文件,然后在在执行那个文件。

 

技术相关:

评论

呵,这个还不能直接用于生产环境。:)
只是提供一个Perl的基本模型。

用shell也挺简单
#/bin/bash
DATE=`date +%Y-%m-%d`
MYSQL="mysql -uroot -h xxx "
for db in `$MYSQL -e "show databases"|sed '1d'`
do
mkdir -p $db_$DATE
cd $db_$DATE
for table in `$MYSQL $db -e "show tables"|sed '1d'`
do
mysqldump --opt $db $table |gzip >$db_$table.sql.gz
done
done

perl不会,shell能懂点,好久不写,手真生,学习了