#!/usr/bin/perl -w ######################################################################################## # MySQLDumper CronDump # # 2004-2010 by Steffen Kamper, Daniel Schlichtholz # additional scripting: Detlev Richter, Jonathan Tietz # # for support etc. visit http://forum.mysqldumper.de # # This file is part of MySQLDumper released under the GNU/GPL 2 license # http://www.mysqldumper.net # @package MySQLDumper # @version $Rev: 1371 $ # @author $Author: dsb1971 $ # @lastmodified $Date: 2011-01-24 21:15:21 +0100 (Mo, 24. Jan 2011) $ # @filesource $URL: https://mysqldumper.svn.sourceforge.net/svnroot/mysqldumper/branches/msd1.24.3/msd_cron/crondump.pl $ # ######################################################################################## # Script-Version my $pcd_version='1.24.4'; ######################################################################################## # please enter the absolute path of the config-dir # when calling the script without parameters the default_configfile (mysqldumper.conf.php) will be loaded # e.g. - (zum Beispiel): # my $absolute_path_of_configdir="/home/www/doc/8176/mysqldumper.de/www/mysqldumper/work/config/"; my $absolute_path_of_configdir=""; my $cgibin_path=""; # this is needed for MIME::Lite if it is in cgi-bin my $default_configfile="mysqldumper.conf.php"; ######################################################################################## # nothing to edit below this line !!! ######################################################################################## # import the necessary modules ... use strict; use warnings; use DBI; use File::Find; use File::Basename; use CGI::Carp qw(warningsToBrowser fatalsToBrowser); warningsToBrowser(1); use CGI; use Data::Dumper; use Getopt::Long; ######################################################################################## use vars qw( $pcd_version $dbhost $dbname $dbuser $dbpass $dbport $dbsocket $cron_dbindex @cron_db_array @ftp_server $dbpraefix @cron_dbpraefix_array $compression $backup_path $logdatei $completelogdatei $command_beforedump $command_afterdump $cron_printout $cronmail $cronmail_dump $cronmailto $cronmailto_cc $cronmailfrom $cronftp $mp $multipart_groesse $email_maxsize $auto_delete $max_backup_files $perlspeed $optimize_tables_beforedump $result @key_value $pair $key $value $conffile @confname $logcompression $log_maxsize $complete_log $starttime $Sekunden $Minuten $Stunden $Monatstag $Monat $Jahr $Wochentag $Jahrestag $Sommerzeit $rct $tabelle @tables @tablerecords $dt $sql_create @ergebnis @ar $sql_daten $inhalt $insert $totalrecords $error_message $cfh $oldbar $print_out $msg $dt $ftp $dateistamm $dateiendung $mpdatei $i $BodyNormal $BodyMultipart $BodyToBig $BodyNoAttach $BodyAttachOnly $Body $DoAttach $cmt $part $fpath $fname $fmtime $timenow $daydiff $datei $inh $gz $search $fdbname @str $item %dbanz $anz %db_dat $fieldlist $first_insert $my_comment $sendmail_call $config_read_from $cron_smtp $cron_smtp_port $cron_use_sendmail @ftp_transfer @ftp_timeout @ftp_user @ftp_pass @ftp_dir @ftp_server @ftp_port @ftp_mode @ftp_useSSL $output $query $skip $html_output $datei @trash_files $time_stamp @filearr $sql_file $backupfile $memory_limit $dbh $sth @db_array @dbpraefix_array @cron_command_before_dump @cron_command_after_dump $db_anz $record_count $filesize $status_start $status_end $sql_text $punktzaehler @backupfiles_name @backupfiles_size $mysql_commentstring $character_set $mod_gz $mod_mime $mod_ftp $mod_ftpssl @multipartfiles %db_tables @tablenames $tablename $opttbl $command $current_dir ); $memory_limit=100000; $mysql_commentstring="-- "; $character_set="utf8"; $sql_text=''; $sql_file=''; $punktzaehler=0; @trash_files=(); @filearr=(); $opttbl=0; $dbpraefix=""; $complete_log= 0; $cron_printout = 1; #config file $conffile=""; #return perl version sub GetPerlVersion (){ my $pversion ; if ($^V){ $pversion = sprintf "v%vd", $^V ; # v5.10.1 }else{ $pversion = local $]; } return $pversion; } # import the optional modules ... my $eval_in_died; $mod_gz=0; $mod_ftp=0; $mod_mime=0; $mod_ftpssl=0; push (@INC, "$cgibin_path"); eval { $eval_in_died = 1; require Compress::Zlib; }; if(!$@){ $mod_gz = 1; import Compress::Zlib; } eval { $eval_in_died = 1; require Net::FTP; }; if(!$@){ $mod_ftp = 1; import Net::FTP; } eval { $eval_in_died = 1; require Net::FTPSSL; }; if(!$@){ $mod_ftpssl = 1; import Net::FTPSSL; } eval { $eval_in_died = 1; require MIME::Lite; }; if(!$@){ $mod_mime = 1; import MIME::Lite; } #include config file $conffile=""; #read args from command GetOptions ("config=s" => \$conffile, "html_output=s" => \$html_output); if (!defined $html_output) { $html_output=0; }; # suppress HTML Output #called via browser or cmd if($ENV{'QUERY_STRING'}) { $html_output=1; # turn HTML Output on if called via Browser-Request my $querystring=$ENV{'QUERY_STRING'}; #$querystring=~ s/\?/ /g; @key_value = split(/&/,$querystring); foreach $pair(@key_value) { #$pair =~ tr/+/ /; ($key,$value) = split(/=/,$pair); if($key eq "config") { $value=~ s/\?/ /g; $conffile=$value; $config_read_from="Querystring"; } if($key eq "html_output") { $html_output=$value; }; # overwrite var if given in call } }else{ $config_read_from="shell"; } # Now we know if script was called via HTTP-Requets or from Shell. So output Headers PrintHeader(); # Security: try to detect wether someone tries to include some external configfile die "Hacking attempt - I wont do anything!\nGo away\n\n" if (lc($conffile) =~ m /:/); #try to guess path if $absolute_path_of_configdir is not filled if($absolute_path_of_configdir eq "" || ! -d $absolute_path_of_configdir) { #get full path if ($config_read_from eq "shell") { $i=$0; } else { $i=$ENV{'SCRIPT_FILENAME'}; }; if ($i=~m#^(.*)\\#) { #windows $current_dir = $1; $current_dir =~ s/msd\_cron//g; #set default log-files $logdatei= $current_dir ."work\\log\\mysqldump_perl.log"; $completelogdatei= $current_dir . "work\\log\\mysqldump_perl.complete.log"; $absolute_path_of_configdir = $current_dir ."work\\config\\"; } elsif ($i=~m#^(.*)\/# ) { #*nix $current_dir = $1; $current_dir =~ s/msd\_cron//g; #set default log-files $logdatei= $current_dir ."work/log/mysqldump_perl.log"; $completelogdatei= $current_dir . "work/log/mysqldump_perl.complete.log"; $absolute_path_of_configdir = $current_dir."work/config/"; } #$absolute_path_of_configdir =~ s/msd\_cron//g; $backup_path = $absolute_path_of_configdir; $backup_path =~ s/config/backup/g; #if zlib is available, set default to compress if ($mod_gz){ $logdatei .= ".gz"; $completelogdatei .= ".gz"; $logcompression=1; } } $conffile=trim($conffile); if($conffile eq "") { $conffile=$default_configfile; # no Parameter for configfile given -> use standardfile "mysqldumper.conf.php" $config_read_from="standard configuration"; } # check config-dir $absolute_path_of_configdir=trim($absolute_path_of_configdir); # remove spaces if (!opendir(DIR, $absolute_path_of_configdir)){ err_trap("The config-directory you entered is wrong !\n($absolute_path_of_configdir - $!) \n\nPlease edit $0 and enter the right configuration-path.\n",0,1); } closedir(DIR); #add trailing slash to confdir if(substr($absolute_path_of_configdir,-1) ne "/") { $absolute_path_of_configdir=$absolute_path_of_configdir."/"; } #add conffile extension if (substr($conffile,length($conffile)-5,5) eq '.conf') { $conffile.='.php'; }; if (substr($conffile,length($conffile)-9,9) ne '.conf.php') { $conffile.='.conf.php'; }; # load configuration file $datei=$absolute_path_of_configdir.$conffile; open(CONFIG,"<$datei") or die "\nI couldn't open the configurationfile:".$datei."\nFile not found or not accessible!\n\n"; while (my $line = ) { chomp($line); if ($line ne '' && substr($line,0,1) ne '#') { eval($line); } } close(CONFIG); if ($html_output==1) { $cron_printout=1; }; # overwrite output if HTML-Output is activated # more than one conffile? @confname=split(/\//,$conffile); PrintOut("Configuration '".$conffile."' loaded successfully from ".$config_read_from."."); if($mod_gz==1) { PrintOut("Compression Library loaded successfully..."); } else { $compression=0; PrintOut("Compression Library loading failed - Compression deactivated ..."); } if($mod_ftp==1) { PrintOut("FTP Library loaded successfully..."); } else { $cronftp=0; PrintOut("FTP Library loading failed - FTP deactivated ..."); } if($mod_ftpssl==1) { PrintOut("FTP-SSL Library loaded successfully..."); $cronftp=1; } else { $cronftp=0; PrintOut("FTP-SSL Library loading failed - FTP-SSL deactivated ..."); } if($mod_mime==1) { PrintOut("Mail Library loaded successfully..."); } else { $cronmail=0; PrintOut("Mail Library loading failed - Mail deactivated ..."); } #try writing to logfile write_log("***********************************************************************\n"); write_log("Starting backup using Perlscript version $pcd_version (using perl ".GetPerlVersion().")\n"); PrintOut("Starting backup using Perlscript version $pcd_version (using perl ".GetPerlVersion().")\n"); write_log("Using configuration $conffile\n"); #now do the dump #more than one db if($cron_dbindex > -1) { $dbname=$cron_db_array[$cron_dbindex]; $dbpraefix=$cron_dbpraefix_array[$cron_dbindex]; $command_beforedump=$cron_command_before_dump[$cron_dbindex]; $command_afterdump=$cron_command_after_dump[$cron_dbindex]; ExecuteCommand(1,$command_beforedump); DoDump(); ExecuteCommand(2,$command_afterdump); } else { $db_anz=@cron_db_array; for(my $ii = 0; $ii < $db_anz; $ii++) { if ($mp>0) { $mp=1; } # Part-Reset if using Multipart (for next database) $dbname=$cron_db_array[$ii]; $dbpraefix=$cron_dbpraefix_array[$ii]; $command_beforedump=$cron_command_before_dump[$ii]; $command_afterdump=$cron_command_after_dump[$ii]; PrintOut("
Starting to backup database `$dbname` (".($ii+1)."/$db_anz)."); if ($dbpraefix ne "") { PrintOut("Scanning for tables with prefix '$dbpraefix')"); } ExecuteCommand(1,$command_beforedump); DoDump(); ExecuteCommand(2,$command_afterdump); } } if($auto_delete>0) { if($max_backup_files>0) { PrintOut("
Starting autodelete function:
Keep the latest $max_backup_files backup files for each database and delete older ones."); find(\&AutoDeleteCount, $backup_path); DoAutoDeleteCount(); DeleteFiles (\@trash_files); } } closeScript(); if ($html_output==0) { print "\nEnd of Cronscript\n"; } ############################################## # Subroutinen # ############################################## sub DoDump { undef(%db_tables); ($Sekunden, $Minuten, $Stunden, $Monatstag, $Monat, $Jahr, $Wochentag, $Jahrestag, $Sommerzeit) = localtime(time); $Jahr+=1900;$Monat+=1;$Jahrestag+=1; my $CTIME_String = localtime(time); my $ret=0; $time_stamp=$Jahr."_".sprintf("%02d",$Monat)."_".sprintf("%02d",$Monatstag)."_".sprintf("%02d",$Stunden)."_".sprintf("%02d",$Minuten); $starttime= sprintf("%02d",$Monatstag).".".sprintf("%02d",$Monat).".".$Jahr." ".sprintf("%02d",$Stunden).":".sprintf("%02d",$Minuten); $fieldlist=""; # Verbindung mit MySQL herstellen, $dbh ist das Database Handle if (trim($dbsocket) eq "") { $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost:$dbport","$dbuser","$dbpass") || die "Database connection not made: $DBI::errstr"; } else { $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost:$dbport;mysql_socket=$dbsocket","$dbuser","$dbpass") || die "Database connection not made: $DBI::errstr"; } # herausfinden welche Mysql-Version verwendet wird $sth = $dbh->prepare("SELECT VERSION()"); $sth->execute; my @mysql_version=$sth->fetchrow; my @v=split(/\./,$mysql_version[0]); if($v[0]>=5 || ($v[0]>=4 && $v[1]>=1) ) { #mysql Version >= 4.1 $sth = $dbh->prepare("SET NAMES '".$character_set."'"); $sth->execute; # get standard encoding of MySQl-Server $sth = $dbh->prepare("SHOW VARIABLES LIKE 'character_set_connection'"); $sth->execute; @ar=$sth->fetchrow; $character_set=$ar[1]; } else { # mysql Version < 4.1 -> no SET NAMES available # get standard encoding of MySQl-Server $sth = $dbh->prepare("SHOW VARIABLES LIKE 'character_set'"); $sth->execute; @ar=$sth->fetchrow; if (defined($ar[1])) { $character_set=$ar[1]; } } PrintOut("Characterset of connection and backup file set to ".$character_set."."); #Statuszeile erstellen my $t=0; my $r=0; my $st_e="\n"; undef(@tables); undef(@tablerecords); my $value=0; my $engine=''; my %db_tables_views; my $query="SHOW TABLE STATUS FROM `$dbname`"; if ($dbpraefix ne "") { $query.=" LIKE '$dbpraefix%'"; PrintOut("Searching for tables inside database `$dbname` with prefix '$dbpraefix'."); } else { PrintOut("Searching for tables inside database `$dbname`."); } $sth = $dbh->prepare($query); $sth->execute || err_trap("Error executing: ".$query." ! MySQL-Error: ".$DBI::errstr); while ( $value=$sth->fetchrow_hashref()) { $value->{skip_data}=0; #defaut -> backup data of table # decide if we need to skip the data while dumping (VIEWs and MEMORY) # check for old MySQL3-Syntax Type=xxx if (defined $value->{Type}) { # port old index type to index engine, so we can use the index Engine in the rest of the script $value->{Engine}=$value->{Type}; $engine=uc($value->{Type}); if ($engine eq "MEMORY") { $value->{skip_data}=1; } } # check for >MySQL3 Engine=xxx if (defined $value->{Engine}) { $engine=uc($value->{Engine}); if ($engine eq "MEMORY") { $value->{skip_data}=1; } } # check for Views - if it is a view the comment starts with "VIEW" if (defined $value->{Comment} && uc(substr($value->{Comment},0,4)) eq 'VIEW') { $value->{skip_data}=1; $value->{Engine}='VIEW'; $value->{Update_time}=''; $db_tables_views{$value->{Name}}=$value; } else { $db_tables{$value->{Name}}=$value; } # cast indexes to int, cause they are used for builing the statusline $value->{Rows}+=0; $value->{Data_length}+=0; $value->{Index_length}+=0; } $sth->finish; @tablenames=sort keys(%db_tables); # add VIEW at the end as they need all tables to be created before @tablenames = (@tablenames,sort keys(%db_tables_views)); %db_tables = (%db_tables,%db_tables_views); $tablename=''; if (@tablenames<1) { PrintOut("There are no tables inside database ".$dbname."! It doesn't make sense to backup an empty database. Skipping this one."); return; } if($optimize_tables_beforedump==1) { optimise_tables(); } $st_e.="-- TABLE-INFO\n"; foreach $tablename (@tablenames) { my $dump_table=1; if ($dbpraefix ne "") { if (substr($tablename,0,length($dbpraefix)) ne $dbpraefix) { # exclude table from backup because it doesn't fit to praefix $dump_table=0; } } if ($dump_table==1) { $r+=$db_tables{$tablename}{Rows}; #calculate nr of records push(@tables,$db_tables{$tablename}{Name}); # add tablename to backuped tables $t++; if (!defined $db_tables{$tablename}{Update_time}) { $db_tables{$tablename}{Update_time}=0; } $st_e.=$mysql_commentstring."TABLE\|$db_tables{$tablename}{Name}\|$db_tables{$tablename}{Rows}\|".($db_tables{$tablename}{Data_length}+$db_tables{$tablename}{Index_length})."\|$db_tables{$tablename}{Update_time}|$db_tables{$tablename}{Engine}\n"; } } $st_e.="-- EOF TABLE-INFO"; PrintOut("Found ".(@tables)." tables with $r records."); #AUFBAU der Statuszeile: # -- Status:tabellenzahl:datensaetze:Multipart:Datenbankname:script:scriptversion:Kommentar:MySQLVersion:Backupflags:SQLBefore:SQLAfter:Charset:EXTINFO # Aufbau Backupflags (1 Zeichen pro Flag, 0 oder 1, 2=unbekannt) # (complete inserts)(extended inserts)(ignore inserts)(delayed inserts)(downgrade)(lock tables)(optimize tables) # $status_start=$mysql_commentstring."Status:$t:$r:"; my $flags="1$optimize_tables_beforedump"; $status_end=":$dbname:perl:$pcd_version:$my_comment:$mysql_version[0]:$flags"; $status_end.=":$command_beforedump:$command_afterdump:$character_set:EXTINFO$st_e\n".$mysql_commentstring."Dump created on $CTIME_String by PERL Cron-Script\n".$mysql_commentstring."Dump by MySQLDumper (http://www.mysqldumper.net/)\n\n"; if($mp>0) { $sql_text=$status_start."MP_$mp".$status_end; } else { $sql_text=$status_start.$status_end; } NewFilename(); $totalrecords=0; foreach $tablename (@tables) { # first get CREATE TABLE Statement if($dbpraefix eq "" || ($dbpraefix ne "" && substr($tablename,0,length($dbpraefix)) eq $dbpraefix)) { PrintOut("Dumping table `$tablename` (Type ".$db_tables{$tablename}{Engine}."):"); $a="\n\n$mysql_commentstring\n$mysql_commentstring"."Table structure for table `$tablename`\n$mysql_commentstring\n"; if ($db_tables{$tablename}{Engine} ne 'VIEW' ) { $a.="DROP TABLE IF EXISTS `$tablename`;\n"; } else { $a.="DROP VIEW IF EXISTS `$tablename`;\n"; } $sql_text.=$a; $sql_create="SHOW CREATE TABLE `$tablename`"; $sth = $dbh->prepare($sql_create); if (!$sth) { err_trap("Fatal error sending Query '".$sql_create."'! MySQL-Error: ".$DBI::errstr); } $sth->execute || err_trap("Couldn't execute ".$sql_create); @ergebnis=$sth->fetchrow; $sth->finish; $a=$ergebnis[1].";\n"; if (length($a)<10) { err_trap("Fatal error! Couldn't read CREATE-Statement of table `$tabelle`! This backup might be incomplete! Check your database for errors."."' MySQL-Error: ".$DBI::errstr,1); $skip=1; } else { $sql_text.=$a; } if ($db_tables{$tablename}{skip_data} == 0) { $sql_text.="\n$mysql_commentstring\n$mysql_commentstring"."Dumping data for table `$tablename`\n$mysql_commentstring\n"; $sql_text.="/*!40000 ALTER TABLE `$tablename` DISABLE KEYS */;"; WriteToFile($sql_text,0); $sql_text=""; $punktzaehler=0; # build fieldlist $fieldlist="("; $sql_create="SHOW FIELDS FROM `$tablename`"; $sth = $dbh->prepare($sql_create); if (!$sth) { err_trap("Fatal error sending Query '".$sql_create."'! MySQL-Error: ".$DBI::errstr); } $sth->execute || err_trap("Couldn't execute ".$sql_create); while ( @ar=$sth->fetchrow) { $fieldlist.="`".$ar[0]."`,"; } $sth->finish; # remove trailing ',' and add ')' $fieldlist=substr($fieldlist,0,length($fieldlist)-1).")"; # how many rows $rct=$db_tables{$tablename}{Rows}; for (my $ttt=0;$ttt<$rct;$ttt+=$perlspeed) { # default beginning for INSERT-String $insert = "INSERT INTO `$tablename` $fieldlist VALUES ("; $first_insert=0; # get rows (parts) $sql_daten="SELECT * FROM `$tablename` LIMIT ".$ttt.",".$perlspeed.";"; $sth = $dbh->prepare($sql_daten); if (!$sth) { err_trap("Fatal error sending Query '".$sql_create."'! MySQL-Error: ".$DBI::errstr); } $sth->execute || err_trap("Couldn't execute \"".$sql_daten."\" - MySQL-Error: ".$DBI::errstr); while ( @ar=$sth->fetchrow) { #Start the insert if($first_insert==0) { $a="\n$insert"; } else { $a="\n("; } # quote all values foreach $inhalt(@ar) { $a.= $dbh->quote($inhalt).","; } # remove trailing ',' and add end-sql $a=substr($a,0, length($a)-1).");"; $sql_text.= $a; if($memory_limit>0 && length($sql_text)>$memory_limit) { WriteToFile($sql_text); $sql_text=""; if($mp>0 && $filesize>$multipart_groesse) {NewFilename();} } } $sth->finish; } $sql_text.="\n/*!40000 ALTER TABLE `$tablename` ENABLE KEYS */;\n"; } # write sql commands to file WriteToFile($sql_text); $sql_text=""; if ($db_tables{$tablename}{skip_data} == 0) { PrintOut("\n
$db_tables{$tablename}{Rows} inserted records (size of backupfile: ".byte_output($filesize).")"); $totalrecords+=$db_tables{$tablename}{Rows}; } else { PrintOut("\n
Dumping structure of `$tablename` (Type ".$db_tables{$tablename}{Engine}." ) (size of backupfile: ".byte_output($filesize).")"); } if($mp>0 && $filesize>$multipart_groesse) {NewFilename();} } } # end WriteToFile("\nSET FOREIGN_KEY_CHECKS=1;\n"); WriteToFile($mysql_commentstring."EOB\n"); PrintOut("\n
Finished backup of database `$dbname`.\n"); write_log("Finished backup of database `$dbname`.\n"); # sent via email if($cronmail==1) { PrintOut("Sending E-Mail ..."); $ret=send_mail(); if ($ret) { write_log("Recipient/s: $cronmailto $cronmailto_cc\n"); PrintOut("Recipient/s: $cronmailto $cronmailto_cc\n"); } } # sent to ftp-server send_ftp(); } #print error message and optional exit sub err_trap { my $error_message = shift(@_); #continue instead of exit my $continue = shift(@_); #don't write to logfile, if we did not read config before my $nolog = shift(@_); PrintOut("Perl Cronscript ERROR: $error_message
\n"); write_log("Perl Cronscript ERROR: $error_message
\n") if !defined $nolog; if (!defined $continue || $continue ==0) { PrintOut("Stopping script because of this fatal error!
\n"); write_log("Stopping script because of this fatal error!
\n") if !defined $nolog; exit(1); } } sub PrintHeader { my $cgi = new CGI; my $perlversion = GetPerlVersion(); if ($html_output==1) { print $cgi->header(-type => 'text/html; charset=utf-8', -cache_control => 'no-cache, no-store, must-revalidate'); print "\n"; print "\n\nMySQLDumper - Perl CronDump [Version $pcd_version (using perl $perlversion)]\n"; print "\n"; print "\n

MySQLDumper - Perl CronDump [Version $pcd_version (using perl $perlversion)]

\n"; } else { #small output for external cronjobs, which expect a small returnvalue print "MySQLDumper - Perl CronDump [Version $pcd_version] started successfully (using perl $perlversion)\n"; } } sub PrintOut { $print_out = shift(@_); if (defined $print_out && length(trim($print_out))>0) { if($complete_log==1) { my $logsize=0; ($Sekunden, $Minuten, $Stunden, $Monatstag, $Monat, $Jahr, $Wochentag, $Jahrestag, $Sommerzeit) = localtime(time); $Jahr+=1900; $Monat+=1;$Jahrestag+=1; $dt=sprintf("%02d",$Monatstag).".".sprintf("%02d",$Monat).".".sprintf("%02d",$Jahr)." ".sprintf("%02d",$Stunden).":".sprintf("%02d",$Minuten).":".sprintf("%02d",$Sekunden); if (-e $completelogdatei) { $logsize=(stat($completelogdatei))[7]; unlink($completelogdatei) if($logsize + length($print_out)>$log_maxsize && $log_maxsize>0); } my $output=$print_out; #$output =~ s/<(.*?)>//gi; $output =~ s/\n//gi; $output =~ s/\r//gi; $output =~ s/
//gi; $output=trim($output); if ( ($logcompression==0) || ($mod_gz==0)) { open(DATEI,">>$completelogdatei") || err_trap('can\'t open mysqldump_perl.complete.log ('.$completelogdatei.').'); print DATEI "$dt $output\n" || err_trap('can\'t write to mysqldump_perl.complete.log ('.$completelogdatei.').'); close(DATEI)|| err_trap('can\'t close mysqldump_perl.complete.log ('.$completelogdatei.').'); chmod(0777,$completelogdatei); } else { $gz = gzopen($completelogdatei, "ab") || err_trap("Cannot open mysqldump_perl.complete.log.gz. "); $gz->gzwrite("$dt $output\n") || err_trap("Error writing mysqldump_perl.complete.log.gz. "); $gz->gzclose ; chmod(0777,$completelogdatei); } } if($cron_printout==1) { #save current autoflush-setting local ($oldbar) = $|; #save current output filehandle and change it to STDOUT $cfh = select (STDOUT); #set autoflush on $| = 1; #remove html-tags if($html_output==0) { $print_out =~ s/<(.*?)>//gi; } print $print_out; #TODO don't print
with the last printout (-> wrong html-syntax) if ($html_output==1){ print "
\n"; } else { print "\n"; }; #restore old autoflush-setting $| = $oldbar; #set default output back to old filehandle select ($cfh); } } } sub write_log { $msg = shift(@_); ($Sekunden, $Minuten, $Stunden, $Monatstag, $Monat, $Jahr, $Wochentag, $Jahrestag, $Sommerzeit) = localtime(time); $Jahr+=1900; $Monat+=1;$Jahrestag+=1; #$dt=sprintf("%02d",$Monatstag).".".sprintf("%02d",$Monat).".".sprintf("%02d",$Jahr)." ".sprintf("%02d",$Stunden).":".sprintf("%02d",$Minuten).":".sprintf("%02d",$Sekunden); $dt=sprintf("%02d.%02d.%02d %02d:%02d:%02d",$Monatstag,$Monat,$Jahr,$Stunden,$Minuten,$Sekunden); my $logsize=0; if (-e $logdatei) { $logsize=(stat($logdatei))[7]; unlink($logdatei) if($logsize+200>$log_maxsize && $log_maxsize>0); } if ( ($logcompression==0) || ($mod_gz==0)) { open(DATEI,">>$logdatei") || err_trap("Can't open file ($logdatei)."); print DATEI "$dt $msg" || err_trap("Can't write to file ($logdatei)."); close(DATEI)|| err_trap("can't close file ($logdatei)."); chmod(0777,$logdatei); } else { $gz = gzopen($logdatei, "ab") || err_trap("Can't open $logdatei."); $gz->gzwrite("$dt $msg") || err_trap("Can't write to $logdatei. "); $gz->gzclose ; chmod(0777,$logdatei); } } sub send_ftp { #save files to ftp-server my $ret=0; my $x=0; for(my $i = 0; $i <3; $i++) { if ($ftp_transfer[$i]==1) { if ($ftp_timeout[$i]<1) { $ftp_timeout[$i]=30; }; if (${ftp_useSSL[$i]}==1 && $mod_ftpssl==1) { #use ftp-ssl $ftp = Net::FTPSSL->new($ftp_server[$i], Encryption => Net::FTPSSL->EXP_CRYPT, Port => $ftp_port[$i], DataProtLevel =>Net::FTPSSL->DATA_PROT_CLEAR, Timeout => $ftp_timeout[$i], Debug => 0) or err_trap( "FTP-SSL-ERROR: Can't connect: $@\n",1); } else { #use 'plain' ftp $ftp = Net::FTP->new($ftp_server[$i], Port => $ftp_port[$i], Timeout => $ftp_timeout[$i], Debug => 1,Passive => $ftp_mode[$i]) or err_trap( "FTP-ERROR: Can't connect: $@\n",1); } $ftp->login($ftp_user[$i], $ftp_pass[$i]) or err_trap("FTP-ERROR: Couldn't login\n",1); $ftp->binary(); $ftp->cwd($ftp_dir[$i]) or err_trap("FTP-ERROR: Couldn't change directory: ".$ftp_dir[$i],1); if($mp==0) { PrintOut("FTP: transferring `$backupfile`"); $ret=$ftp->put($sql_file); if (!$ret) { err_trap("FTP-Error: Couldn't put $backupfile to ".$ftp_server[$i]." into dir ".$ftp_dir[$i]."\n",1); } else { write_log("FTP: transferred `$backupfile` to $ftp_server[$i] into dir $ftp_dir[$i] successfully\n"); PrintOut(" to $ftp_server[$i] into dir $ftp_dir[$i] was successful.\n"); } } else { $dateistamm=substr($backupfile,0,index($backupfile,"part_"))."part_"; $dateiendung=($compression==1)?".sql.gz":".sql"; $mpdatei=""; for ($x=1;$x<$mp;$x++) { $mpdatei=$dateistamm.$x.$dateiendung; PrintOut("FTP: transferring multipart $mpdatei"); $ret=$ftp->put($backup_path.$mpdatei); if (!$ret) { err_trap("Couldn't put $backup_path.$mpdatei to ".$ftp_server[$i]." into dir ".$ftp_dir[$i]."\n",1); } else { #write_log("FTP: transferring of `$mpdatei` to ".$ftp_server[$i]." finished successfully.\n"); #PrintOut("FTP: transferring of `$mpdatei` to $ftp_server[$i] finished successfully."); write_log("FTP: transferred multipart '$mpdatei' to $ftp_server[$i] into dir $ftp_dir[$i] successfully\n"); PrintOut(" to $ftp_server[$i] into dir $ftp_dir[$i] was successful.\n"); } } } } } } sub send_mail { #sent email w/o files $BodyNormal='Find attached a backup of your database `'.$dbname.'`.'; $BodyMultipart="A multipart backup has been made.
You will receive one or more emails with the backup-files attached.
The database `".$dbname."` has been backuped.
The following files have been created:"; $BodyToBig="The backup is bigger than the allowed max-limit of ".byte_output($email_maxsize)." and has not been attached.
Backup of database ".$dbname."

The following files have been created:"; $BodyNoAttach="The backup has not been attached.
I saved your database `".$dbname."` to file
"; $BodyAttachOnly="Here is your backup."; $Body=""; $DoAttach=1; my @mparray; my $ret=0; if($mp==0) { #no multipart if(($email_maxsize>0 && $filesize>$email_maxsize) || $cronmail_dump==0) { #attache files if($cronmail_dump==0) { #The backup has not been attached $Body=$BodyNoAttach.$backupfile." (".byte_output($filesize).")"; } else { #The backup is bigger than the allowed max-limit $Body=$BodyToBig.$backupfile." (".byte_output($filesize).")"; } $DoAttach=0; } else { #Find attached your backup $Body=$BodyNormal." File ".$backupfile." (".byte_output($filesize).")"; } } else { #multipart $Body=$BodyMultipart; $dateistamm=substr($backupfile,0,index($backupfile,"part_"))."part_"; $dateiendung=($compression==1)?".sql.gz":".sql"; $mpdatei=""; for ($i=1;$i<$mp;$i++) { $mpdatei=$dateistamm.$i.$dateiendung; push(@mparray,"$mpdatei|$i"); $filesize=(stat($backup_path.$mpdatei))[7]; $Body.="\n
$mpdatei (".(byte_output($filesize))." )"; } } $Body.="\n\n

Best regards,

MySQLDumper
If you have any questions, feel free and visit the support board at:
http://forum.mysqldumper.de"; if ($cron_use_sendmail==1) { MIME::Lite->send("sendmail", $sendmail_call) || err_trap("Error setting sendmail call!",1); } else { MIME::Lite->send('smtp', $cron_smtp, Timeout=>60) || err_trap("Error setting smtp call !",1); } $msg = MIME::Lite->new( From => $cronmailfrom, To => $cronmailto, Cc => $cronmailto_cc, Subject => "MSD (Perl) - Backup of DB ".$dbname, Type => 'text/html; charset=iso-8859-1', Data => "\n".$Body."\n" ); if($DoAttach==1 && $mp==0) { #attach files, no multipart $msg->attach( Type => "BINARY", Path => "$sql_file", Encoding => "base64", Filename => "$backupfile" ); $ret=$msg->send; if (!$ret) { err_trap("Error 1 sending mail with backup ".$backupfile."!",1); } else { PrintOut("E-Mail with backup ".$backupfile." sent successfully."); write_log("E-Mail with backup ".$backupfile." sent successfully.\n"); } return $ret; } if($DoAttach==1 && $mp>0 && $cronmail_dump>0) { #attach files, multipart foreach $datei(@mparray) { @str=split(/\|/,$datei); $msg = MIME::Lite->new( From => $cronmailfrom, To => $cronmailto, Cc => $cronmailto_cc, Subject => "MSD (Perl) - Backup of DB $dbname File ".$str[1]." of ".@mparray , Type => 'text/html; charset=iso-8859-1', Data => "\n".$Body."\n" ); $msg->attach( Type => "BINARY", Path => $backup_path.$str[0], Encoding => "base64", Filename => $str[0] ); $ret=$msg->send; if (!$ret) { err_trap("Error 2 sending mail with backup ".$str[0]."!",1); } else { PrintOut("E-Mail with backup ".$str[0]." sent successfully."); write_log("E-Mail with backup ".$str[0]." sent successfully.\n"); } } return $ret; } $ret=$msg->send; if (!$ret) { err_trap("Error 3 sending E-Mail!
",1); } else { PrintOut("E-Mail sent successfully."); write_log("E-Mail sent successfully.\n"); } return $ret; } sub NewFilename { $part=""; if($mp>0) { $part="_part_$mp"; $mp++; } if($compression==0) { $sql_file=$backup_path.$dbname."_".$time_stamp.$part.".sql"; $backupfile=$dbname."_".$time_stamp.$part.".sql"; } else { $sql_file=$backup_path.$dbname."_".$time_stamp.$part.".sql.gz"; $backupfile=$dbname."_".$time_stamp.$part.".sql.gz"; } if($mp==0) { PrintOut("\n
Starting to dump data into file `$backupfile`"); write_log("Dumping data into file `$backupfile` \n"); } if($mp==2) { PrintOut("\n
Starting to dump data into multipart-file `$backupfile`"); write_log("Start Perl Multipart-Dump with file `$backupfile` \n"); } if($mp>2) { PrintOut("\n
Continuing Multipart-Dump with file `$backupfile`"); write_log("Continuing Multipart-Dump with file `$backupfile` \n"); } if($mp>0) { $sql_text=$status_start."MP_".($mp-1).$status_end; } else { $sql_text=$status_start.$status_end; } $sql_text.="/*!40101 SET NAMES '".$character_set."' */;\n"; $sql_text.="SET FOREIGN_KEY_CHECKS=0;\n"; WriteToFile($sql_text,1); chmod(0777,$sql_file); $sql_text=""; $first_insert=0; $punktzaehler=0; push(@backupfiles_name,$sql_file); } sub WriteToFile { $inh=shift; my $points=shift; if (!defined($points)) { $points=2; } if(length($inh)>0) { if($compression==0){ open(DATEI,">>$sql_file"); print DATEI $inh; close(DATEI); } else { $gz = gzopen($sql_file, "ab") || err_trap("Cannot open ".$sql_file); $gz->gzwrite($inh) || err_trap("Error writing ".$sql_file); $gz->gzclose ; } if ($points>1) { print "."; } $filesize= (stat($sql_file))[7]; $punktzaehler++; if($punktzaehler>120) { if ($html_output==1) { print "
"; } else { print "\n"; }; $punktzaehler=0; } } } sub AutoDeleteCount { $fpath=$File::Find::name; $fname=basename($fpath); my @fileparts=split(/\./,"$fname"); my $partcount=@fileparts; if ($partcount>1) { my $end=$fileparts[(@fileparts-1)]; # Read Statusline and extract info my $line=''; if ($end eq 'sql') { if (open(DATEI,"<$fpath")) { $line=; close(DATEI); } else { print "
Error: couldn\'t open file: ".$fpath; } } if ($end eq 'gz') { $gz = gzopen("$fpath", "rb"); if ($gz) { $gz->gzreadline($line); $gz->gzclose; } else { print "
Error: couldn\'t open file: ".$fpath; } } if (length($line)>0 && (substr($line,0,10) eq "-- Status:" ||substr($line,0,11) eq "-- Status:")) { #statusline read my @infos=split(/\:/,$line); my $file_multipart=($infos[3])?$infos[3]:''; $file_multipart=~ s/MP_/ /g; $file_multipart=trim($file_multipart); my $file_databasename=($infos[4])?$infos[4]:''; if ($file_multipart eq "" || substr($file_multipart,0,1) eq "0") { #no multipartfile push(@filearr,"$fname|$file_databasename"); } else { push(@filearr,"$fname|$file_databasename|$file_multipart"); } } else { PrintOut("No Statusline in `$fname` found. Seems not to be a MySQLDumper file. Skipping file."); } } } sub DoAutoDeleteCount { my @str; my @dbarray; my $item; my $item2; my %dbanz; my $anz=@filearr; # sort filearr descending -> so the latest backups are at top # multipartfiles sorting is also descending -> part3, part2, part1 @filearr=sort{"$b" cmp "$a"}(@filearr); @multipartfiles=(); if ($anz>0) { foreach $item (@filearr) { @str=split(/\|/,$item); # str[0]=filename, str[1]=databasename, str[2]=multipart number #init db-counter if this index doesn't exist yet if (defined $str[1] && !defined $dbanz{$str[1]}) { $dbanz{$str[1]}=0; @multipartfiles=(); } #PrintOut($max_backup_files.': '.$dbanz{$str[1]}.' -> '.$str[0].' - '.$str[1].' - '.$str[2]); #no multipart file -> update db counter if(defined $str[1] && !defined $str[2]) { # handling for non multipart files $dbanz{$str[1]}++; # is the max number of backups for this database reached? # if yes -> push the actual filename into trash_files if($dbanz{$str[1]}>$max_backup_files) { push(@trash_files, $str[0]); } } else { # keep multipartz filename if(defined $str[1] && $dbanz{$str[1]}>=$max_backup_files) { push(@multipartfiles,$str[0]); } # if it is a multipart file and it is part_1 -> update db counter # multiparts with higher numbers already passed the loop, so we can use # part1 to increase the db-counter if(defined $str[2] && $str[2]==1) { $dbanz{$str[1]}++; # now check if we have reached the limit if($dbanz{$str[1]}>$max_backup_files) { foreach $item2 (@multipartfiles) { push(@trash_files, $item2); } } # clear array for next multipart backup @multipartfiles=(); } } } } } sub DeleteFiles { my $res=0; if(@trash_files==0) { PrintOut("No file to delete."); } else { foreach $datei(@trash_files) { my $file_to_delete = $backup_path.$datei; $res=unlink($file_to_delete); if ($res) { PrintOut("Autodelete: old backup file ".$datei." deleted."); write_log( "Autodelete: old backup file $datei deleted.\n" ) ; } else { err_trap("Autodelete: Error deleting old backup file ".$datei."!
",1); } } undef(@trash_files); } } sub ExecuteCommand { my $cmt = shift(@_); my $command = shift(@_); my (@cad, $errText, $succText, $cd2, $commandDump); my $err=''; $commandDump=$command; if($cmt==1) { #before dump $errText="Error while executing Query before Dump"; $succText="Executing Query before Dump was successful"; } else { $errText="Error while executing Query after Dump"; $succText="executing Query after Dump was successful"; } if(defined $commandDump && length($commandDump)>0) { if(substr($commandDump,0,7) ne "system:") { # prepare command $commandDump = replaceQueryStringSimple($commandDump); if (trim($dbsocket) eq "") { $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost:$dbport","$dbuser","$dbpass") || err_trap("Database connection not made: $DBI::errstr"); } else { $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost:$dbport;mysql_socket=$dbsocket","$dbuser","$dbpass") || die "Database connection not made: $DBI::errstr"; } if(index($commandDump,";")>0) { # more than 1 query @cad=split(/;/,$commandDump); } else { @cad=$commandDump; } for($i=0;$i<@cad;$i++) { if($cad[$i] ne '') { $err=''; # replace $$MSD$$ back to ';' $cad[$i] =~ s/\$\$MSD\$\$/\;/g; $sth = $dbh->prepare($cad[$i]); $sth->execute or $err=$sth->errstr(); if ($err ne '') { write_log("Executing Query '$cad[$i]' caused an error! MySQL returns: '$err'\n"); PrintOut("Executing Query '$cad[$i]' caused an error! MySQL returns: '$err'\n"); } else { write_log("Successfully executed Query: $cad[$i]\n"); PrintOut("Successfully executed Query: $cad[$i]\n"); } $sth->finish; } } } else { #Systembefehl $commandDump=substr($commandDump,7); system($commandDump); PrintOut("

$succText ($commandDump)

"); write_log("$succText ($commandDump)\n"); } } } sub closeScript { my ($Start, $Jetzt, $Totalzeit); $Start = $^T; $Jetzt = (time); $Totalzeit=$Jetzt - $Start; ($Sekunden, $Minuten, $Stunden, $Monatstag, $Monat, $Jahr, $Wochentag, $Jahrestag, $Sommerzeit) = localtime(time); $Jahr+=1900;$Monat+=1;$Jahrestag+=1; $starttime=sprintf("%02d",$Monatstag).".".sprintf("%02d",$Monat).".".$Jahr." ".sprintf("%02d",$Stunden).":".sprintf("%02d",$Minuten).":".sprintf("%02d",$Sekunden); PrintOut("
Everythings is done: closing script $starttime"); PrintOut("total time used: $Totalzeit sec."); PrintOut("#EOS (End of script)
"); # Datenbankverbindung schliessen $sth->finish() if (defined $sth); ($dbh->disconnect() || warn $dbh->errstr) if (defined $dbh); } sub trim { my $string = shift; if (defined($string)) { $string =~ s/^\s+//; $string =~ s/\s+$//; } else { $string=''; } return $string; } sub byte_output { my $bytes= shift; my $suffix="Bytes"; if ($bytes>=1024) { $suffix="KB"; $bytes=sprintf("%.2f",($bytes/1024));}; if ($bytes>=1024) { $suffix="MB"; $bytes=sprintf("%.2f",($bytes/1024));}; my $ret=sprintf "%.2f",$bytes; $ret.=' '.$suffix; return $ret; } sub optimise_tables { my $engine=''; my $ret=0; $opttbl=0; PrintOut("Optimizing tables:"); foreach $tablename (@tablenames) { #optimize table if engine supports optimization $engine=uc($db_tables{$tablename}{Engine}); if ( $engine eq "MYISAM" or $engine eq "BDB" or $engine eq "INNODB") { my $sth_to = $dbh->prepare("OPTIMIZE TABLE `$tablename`"); $ret=$sth_to->execute; if ($ret) { PrintOut("Table ".($opttbl+1)." `$tablename` optimized successfully."); $opttbl++; } else { err_trap("  Error optimizing table `$tablename`",1); } } } PrintOut("$opttbl tables have been optimized
") if($opttbl>0) ; } # replace in querystring all ';' in VALUES with '$$MSD$$' sub replaceQueryStringSimple{ my $string = shift(@_); if ($string =~ m#(.*)\'(.*)\;(.*)\'(.*)#){ # if found search for more ';' return replaceQueryStringSimple($1.'\''.$2.'$$MSD$$'.$3.'\''.$4);; }else{ return $string; } }