208 lines
6.6 KiB
Perl
208 lines
6.6 KiB
Perl
#!/usr/bin/perl
|
|
|
|
BEGIN { use lib '/usr/home/cfg' ; require push_inc ; }
|
|
|
|
use CGI::Carp qw(fatalsToBrowser);
|
|
use strict;
|
|
use DBI;
|
|
use Date::Calc qw(:all);
|
|
use Date::Manip;
|
|
|
|
#-------------------------------------------------------------------------------
|
|
|
|
# my @ARGV = split(/\&/, $ENV{'QUERY_STRING'});
|
|
my $debug = $ARGV[0] ;
|
|
|
|
# perl /usr/lib/cgi-bin/aisa/scripts/cron/mysql_backup.pl 1
|
|
|
|
#-------------------------------------------------------------------------------
|
|
|
|
my ($now_sec,$now_min,$now_hour,$now_dd,$now_mm,$now_year,$now_wday,$now_yday,$isdst)=localtime();
|
|
|
|
$now_sec = sprintf("%02d", $now_sec) ;
|
|
$now_min = sprintf("%02d", $now_min) ;
|
|
$now_hour = sprintf("%02d", $now_hour) ;
|
|
|
|
$now_year += 1900;
|
|
|
|
$now_dd = sprintf("%02s", $now_dd) ;
|
|
$now_mm = sprintf("%02s", $now_mm + 1) ;
|
|
my $now_yy = sprintf("%02s", $now_year - 2000) ;
|
|
|
|
exit if $now_hour > 21 || $now_hour < 8 ;
|
|
|
|
# my ($bkyear,$bkmonth,$bkday) = Add_Delta_Days($now_year,$now_mm,$now_dd,-7) ;
|
|
|
|
# $bkmonth = sprintf("%02s", $bkmonth) ;
|
|
# $bkday = sprintf("%02s", $bkday) ;
|
|
|
|
my $err = '' ;
|
|
|
|
my $date1 = &ParseDate("$now_mm/$now_dd/$now_yy");
|
|
|
|
my $date2 = &DateCalc($date1,"- 7 days",\$err);
|
|
|
|
my $bkyear = substr($date2,0,4);
|
|
my $bkmonth = substr($date2,4,2);
|
|
my $bkday = substr($date2,6,2);
|
|
|
|
#-------------------------------------------------------------------------------
|
|
|
|
my $path = "/home/mysqlbak";
|
|
my $ext = "bz2";
|
|
|
|
# if ($debug) { print "Content-type: text/html\n\n"; }
|
|
|
|
&flush_old($path,$ext);
|
|
&backup_db('ais') ; # || $debug == 1 ;
|
|
# &restore_db('d_ais',"$path/$bak_name") if $now_hour == 0 ; # || $debug == 1 ;
|
|
&backup_db('sss') ;
|
|
# &restore_db('d_sss',"$path/$bak_name") if $now_hour == 0 || $debug == 1 ;
|
|
|
|
# &backup_db('sfm') if $now_hour == 0 || $debug == 1 ;
|
|
# &backup_db('sfg') if $now_hour == 0 || $debug == 1 ;
|
|
# &backup_db('rim') if $now_hour == 0 || $debug == 1 ;
|
|
|
|
# - - - - - - - - - - - - - - - - -
|
|
if ($debug) { print "Done."; }
|
|
# - - - - - - - - - - - - - - - - -
|
|
|
|
sub backup_db {
|
|
|
|
# my ($db,$dbhost,$dbuser,$dbpass) = @_ ;
|
|
my ($dbsrv) = @_ ;
|
|
|
|
my ($db,$dbhost,$dbuser,$dbpass) = &ops_db_credentials($dbsrv);
|
|
|
|
my ($dbh,$sth,$query);
|
|
|
|
my $dsn = "DBI:mysql:$db;host=$dbhost";
|
|
$dbh = DBI->connect($dsn,$dbuser,$dbpass,{PrintError => 0, RaiseError => 1});
|
|
$query = qq^SHOW DATABASES^;
|
|
$sth = $dbh->prepare($query);
|
|
$sth->execute();
|
|
|
|
while(my $ref = $sth->fetchrow_array()) {
|
|
|
|
print "\n $ref -> $dsn\n" if $debug ;
|
|
|
|
unless ($ref eq $db) { next ; }
|
|
# my $bak_name = "$ref.$date.$now_hour$now_min.$ext" ;
|
|
our $bak_name = "$ref.$now_year-$now_mm-$now_dd.$now_hour$now_min.$ext" ;
|
|
my @bp = `mysqldump --host=$dbhost --user=$dbuser --password=$dbpass --add-drop-table $ref | bzip2 -1 > $path/$bak_name`;
|
|
# my @bp = `mysqldump -h "$dbhost" -u "$dbuser" -p "$dbpass" --add-drop-table $ref | bzip2 -1 > $path/$bak_name`;
|
|
# &common_send_smtp_mail('','k@re8it.com','','',"K8 MySQL Backup : $bak_name","<tr><td> $path/$bak_name </td></tr>",'html','',$path,$bak_name,'zip') if $now_hour == 2 ;
|
|
}
|
|
|
|
$sth->finish();
|
|
$dbh->disconnect();
|
|
|
|
} # - - - - - - - - - - - - - - - - -
|
|
|
|
# sub restore_db {
|
|
|
|
# my ($dbsrv,$mysqlbakfile) = @_ ;
|
|
|
|
# my ($db,$dbhost,$dbuser,$dbpass) = &ops_db_credentials($dbsrv);
|
|
|
|
# system `bunzip2 -c $mysqlbakfile | mysql -f -u$dbuser -p'$dbpass' -h$dbhost $db`;
|
|
|
|
# # system `mysql -f -u$dbuser -p'$dbpass' -h$dbhost $db < $mysqlbakfile` ;
|
|
|
|
# } # - - - - - - - - - - - - - - - - -
|
|
|
|
sub flush_old {
|
|
|
|
my ($path,$ext) = @_;
|
|
opendir BP_DIR,"$path" or die "Cannot open $path: $!\n";
|
|
my @old_backups = grep { /\.$ext$/ } readdir BP_DIR;
|
|
closedir BP_DIR;
|
|
|
|
for (@old_backups) {
|
|
my ($db_bk_name,$db_bk_date,$db_bk_time,$db_bk_ext) = split(/\./);
|
|
$db_bk_date =~ s/\-//g;
|
|
my $bk_7_days_back_date = "$bkyear$bkmonth$bkday";
|
|
# print "<br>1. $_ -> $db_bk_date = $bkyear$bkmonth$bkday" ;
|
|
if ($db_bk_date > $bk_7_days_back_date) { next; }
|
|
# print "<br>2. $_ -> $db_bk_date = $bkyear$bkmonth$bkday" ;
|
|
my @args = ("rm","-f","$path/$_");
|
|
system(@args);
|
|
}
|
|
|
|
} # - - - - - - - - - - - - - - - - -
|
|
|
|
use common ;
|
|
use today ;
|
|
use db ;
|
|
|
|
# # my $db = "itv_admin_db";
|
|
# # my $dbhost = "localhost";
|
|
# # my $dbuser = "itv_admin_user";
|
|
# # my $dbpass = "!Ja16Q7P0X3SVTWr";
|
|
# # my $date = "`date +%Y-%m-%d`";
|
|
# # my $path = "/home/mysqlbak";
|
|
# # my $ext = "bz2";
|
|
|
|
# my $db = "aisa_admin_db";
|
|
# my $dbhost = "localhost";
|
|
# my $dbuser = "aisa_admin_user";
|
|
# my $dbpass = "s5pCDPUZo18oip29!";
|
|
# my $date = "`date +%Y-%m-%d`";
|
|
# my $path = "/home/mysqlbak";
|
|
# my $ext = "bz2";
|
|
|
|
# if ($debug) { print "Content-type: text/html\n\n"; }
|
|
|
|
# flush_old($path,$ext);
|
|
# my($dbh,$sth,$query);
|
|
|
|
# my $dsn = "DBI:mysql:$db;host=$dbhost";
|
|
# $dbh = DBI->connect($dsn,$dbuser,$dbpass,{PrintError => 0, RaiseError => 1}) or die "Unable to connect, $DBI::errstr";
|
|
# $query = qq^SHOW DATABASES^;
|
|
# $sth = $dbh->prepare($query);
|
|
# $sth->execute();
|
|
|
|
# while(my $ref = $sth->fetchrow_array()) {
|
|
# # unless ($ref eq 'itv_admin_db') { next ; }
|
|
# unless ($ref eq 'aisa_admin_db') { next ; }
|
|
# if ($debug) { print "ref=$ref \n"; }
|
|
# my @bp = `mysqldump --user=$dbuser --password=$dbpass --add-drop-table $ref | bzip2 -1 > $path/$ref.$date.$now_hour$now_min.$ext`;
|
|
# }
|
|
|
|
# $sth->finish();
|
|
# $dbh->disconnect();
|
|
|
|
# my $dow = Day_of_Week($now_year,$now_mm,$now_dd);
|
|
# # our $ftp_file_name = "itv_admin_db.$dow.bz2" ;
|
|
# # &uploader_filmfreight("$path/itv_admin_db.$now_year-$now_mm-$now_dd.$now_hour$now_min.$ext","/mysqlbak",$ftp_file_name,'n') ;
|
|
# our $ftp_file_name = "aisa_admin_db.$dow.bz2" ;
|
|
# &uploader_filmfreight("$path/aisa_admin_db.$now_year-$now_mm-$now_dd.$now_hour$now_min.$ext","/mysqlbak",$ftp_file_name,'n') ;
|
|
|
|
# # - - - - - - - - - - - - - - - - -
|
|
# if ($debug) { print "Done."; }
|
|
# # - - - - - - - - - - - - - - - - -
|
|
|
|
# sub flush_old {
|
|
# my ($path,$ext) = @_;
|
|
# opendir BP_DIR,"$path" or die "Cannot open $path: $!\n";
|
|
# my @old_backups = grep { /\.$ext$/ } readdir BP_DIR;
|
|
# closedir BP_DIR;
|
|
|
|
# for (@old_backups) {
|
|
# my ($db_bk_name,$db_bk_date,$db_bk_time,$db_bk_ext) = split(/\./);
|
|
# $db_bk_date =~ s/\-//g;
|
|
# my $bk_7_days_back_date = "$bkyear$bkmonth$bkday";
|
|
# # print "<br>1. $_ -> $db_bk_date = $bkyear$bkmonth$bkday" ;
|
|
# if ($db_bk_date > $bk_7_days_back_date) { next; }
|
|
# # print "<br>2. $_ -> $db_bk_date = $bkyear$bkmonth$bkday" ;
|
|
# my @args = ("rm","-f","$path/$_");
|
|
# system(@args);
|
|
# }
|
|
# }
|
|
|
|
# # - - - - - - - - - - - - - - - - -
|
|
|
|
# use common ;
|
|
# use uploader ;
|
|
|
|
1; |