[Perl]用snapshot方式备份MYSQL数据库

时间：2006-04-17 来源：huhuegg

文件:	snapshot_blog.rar
大小:	1KB
下载:	下载

#!/usr/bin/perl
#Ver 0.9
#Author Leo Zhang
#Date 20060415 19:55
use strict;
use DBI;
my $dbhostname="222.222.222.222";
my $database="database";
my $db_user="dbuser";
my $db_pass="dbpass";
my $port="3306";
my $dbh;
my $dsn;
my $sth; sub check_snap {
my $lvdisplay=`/usr/sbin/lvdisplay`;
chomp $lvdisplay;
my @lvdisplaytemp=split(/\n/,$lvdisplay);
foreach my $lvdisplaytemp (@lvdisplaytemp) {
  if($lvdisplaytemp =~ /LV[ ]Name/) {
   if($lvdisplaytemp =~ /\/dev\/VolGroup00\/snap/) {
    #print $lvdisplaytemp,"\n";
    `/sbin/fuser -k /snap 2>/dev/null`;
    `/bin/umount /snap `;
    `lvremove -f /dev/VolGroup00/snap`;
   }
  }
}
} sub lock_table {
$sth = $dbh->prepare("DROP TABLE check_readlock");
eval {
$sth->execute();
};
if ($@) {
  #print $@;
  print "DROP TABLE check_readlock false!\n";
} else {
  print "DROP TABLE check_readlock OK\n";
}
$sth = $dbh->prepare("CREATE TABLE `check_readlock` (readlock int(11) NOT NULL default '0')");
eval {
$sth->execute();
};
if ($@) {
  #print $@;
  print "create table check_readlock false!\n";
} else {
  print "create table check_readlock ok\n";
}         $dbh->do("FLUSH TABLES WITH READ LOCK;");
$sth = $dbh->prepare("INSERT INTO check_readlock (readlock) values (1);");
eval {
$sth->execute();
};
if ($@) {
  print "Get Reader Lock!\n";
} else {
  print "Lock tables ok!";
}
}
sub unlock_table {
eval {
        $dbh->do("UNLOCK TABLES;");
};
if ($@) {
  print "UNLOCK TABLES OK\n";
} else {
  $sth = $dbh->prepare("INSERT INTO check_readlock (readlock) values (1);");
        eval {
        $sth->execute();
        };
        if ($@) {
                 print "UNLOCK TABLES false!\n";
        } else {
                print "UNLOCK TABLES ok\n";
        }
} } sub get_time {
my $testtime=`date +%Y-%m-%d" "%H:%M:%S`;
print $testtime;
}
##start##
$dsn="DBI:mysql:$database:$dbhostname:$port";
$dbh=DBI->connect($dsn,$db_user,$db_pass,{RaiseError=>1}); my $hostname=`hostname`;
chomp $hostname;
`mkdir -p /snap`; my $snapdate=`date +%Y%m%d%H%M`;
chomp $snapdate; print "check and clean exist LV snap: ";
&get_time(); &check_snap();
print "clean LV snap ok: ";
&get_time(); print "LOCK TABLES\n";
&lock_table; print "start snapshot: ";
&get_time(); `/usr/sbin/lvcreate -s -L 50G -n snap /dev/VolGroup00/db`;
print "end snapshot: ";
&get_time(); print "UNLOCK TABLES\n";
&unlock_table; `mount -t ext3 -o ro /dev/VolGroup00/snap /snap`;
print "start tar: ";
&get_time(); my $output="/tmp/" . $hostname . "-RecordServer" . $snapdate . ".tar";
print $output,"\n"; `/bin/tar cvf $output /snap/mysql/RecordServer/*`;
print "end tar: ";
&get_time(); `/bin/umount /snap`;
`lvremove -f /dev/VolGroup00/snap`;
print "START NCFTPPUT: ";
&get_time();
`/usr/local/bin/ncftpput -u ftpuser -p ftppass 111.111.111.111 /home/monitor/backup $output`;
print "END NCFTPPUT: ";
&get_time();