mysql备份脚本_MySQL

mysql 备份脚本..写的很一般..高手见了不要笑俺..

#!/usr/bin/perl -w

use warnings;

use strict;

use DBI;

my $backup_dir = "/root/perl/backup";

my $backup_log = "$backup_dir/backup_log";

my $db_name = "mysql";

my $db_host = "localhost";

my $db_user = "root";

my $db_passwd = "redhat";

my $db_port = "3306";

my $mysql_bin_dir = "/usr/local/mysql/bin";

my $ftp_user = "ftp";

my $ftp_passwd = "redhat";

my $ftp_host = "192.168.1.100";

my $ftp_dir ="pub";

my @backup_db_list = qw(mysql test);

my ($sec,$min,$hour,$day,$mon,$year)=localtime;

$year += 1900;

$mon += 1;

my $time_now = "$year-$mon-$day";

my @backup_list;

mkdir "$backup_dir",0755 if !-e "$backup_dir";

open LOG,">>$backup_log";

print LOG "----------------------/nStart: $time_now/n";

my $dsn = "DBI:mysql:$db_name:$db_host:$db_port";

my %conn_attrs = (

PrintError => 1,

RaiseError => 1,

AutoCommit => 1,);

my $dbh=DBI->connect($dsn,$db_user,$db_passwd,/%conn_attrs) or print LOG "Could not connect mysql server:".DBI->errstr."/n";

foreach my $db (@backup_db_list){

mkdir "$backup_dir/$db",0755 if !-e "$backup_dir/$db";

chdir "$backup_dir/$db";

my $backup_db_name = "${db}_${time_now}/.sql";

!system "$mysql_bin_dir/mysqldump -h $db_host -u $db_user -p$db_passwd -P $db_port -B $db > $backup_db_name" or print LOG "mysqldump error:$!/n";

system "(tar -zcvf $backup_db_name/.tar/.gz $backup_db_name) 2>&1 >/dev/null" if -e $backup_db_name;

unlink "$backup_db_name" if -e "$backup_db_name/.tar/.gz";

push (@backup_list,"$backup_dir/$db/$backup_db_name/.tar/.gz");

my @all_backup_db_list = <*.*.tar.gz>;

foreach my $backup_file (@all_backup_db_list){

unlink $backup_file if (time() - (stat($backup_file))[9] > (60*60*24*5));

}

}

$dbh->disconnect();

print "@backup_list/n";

my $ftp = Net::FTP->new($ftp_host,debug => 0) or print LOG "Count not connect ftp:$ftp_host/n";

$ftp->login($ftp_user,$ftp_passwd) or print LOG "Could not login ftp:$ftp_host/n";

$ftp->binary();

$ftp->cwd($ftp_dir);

foreach my $upload_backdb_file (@backup_list){

$ftp->put($upload_backdb_file) or print LOG "put $upload_backdb_file faild!/n";

}

print LOG "------------------------/n---------END----------/n";

$ftp->quit;

close(LOG);

本文出自 “为生活喝彩” 博客