API tools faq
paste
Guest User

Untitled

a guest
Jun 13th, 2016
87
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Perl 50.90 KB | None | 0 0
raw download clone embed print report
  1. #!/usr/bin/perl -w
  2. ########################################################################################
  3. # MySQLDumper CronDump
  4. #
  5. # 2004-2010 by Steffen Kamper, Daniel Schlichtholz
  6. # additional scripting: Detlev Richter, Jonathan Tietz
  7. #
  8. # for support etc. visit http://forum.mysqldumper.de
  9. #
  10. # This file is part of MySQLDumper released under the GNU/GPL 2 license
  11. # http://www.mysqldumper.net
  12. # @package             MySQLDumper
  13. # @version             $Rev: 1371 $
  14. # @author             $Author: dsb1971 $
  15. # @lastmodified     $Date: 2011-01-24 21:15:21 +0100 (Mo, 24. Jan 2011) $
  16. # @filesource         $URL: https://mysqldumper.svn.sourceforge.net/svnroot/mysqldumper/branches/msd1.24.3/msd_cron/crondump.pl $
  17. #
  18. ########################################################################################
  19. # Script-Version
  20. my $pcd_version='1.24.4';
  21.  
  22. ########################################################################################
  23. # please enter the absolute path of the config-dir
  24. # when calling the script without parameters the default_configfile (mysqldumper.conf.php) will be loaded
  25. # e.g. - (zum Beispiel):
  26. # my $absolute_path_of_configdir="/home/www/doc/8176/mysqldumper.de/www/mysqldumper/work/config/";
  27.                                          
  28. my $absolute_path_of_configdir="/home/zzzzzzzz/www/administ/back/work/config/";
  29. my $cgibin_path=""; # this is needed for MIME::Lite if it is in cgi-bin
  30. my $default_configfile="mysqldumper.conf.php";
  31.  
  32. ########################################################################################
  33. # nothing to edit below this line !!!
  34. ########################################################################################
  35. # import the necessary modules ...
  36. use strict;
  37. use warnings;
  38. use DBI;
  39. use File::Find;
  40. use File::Basename;
  41. use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
  42. warningsToBrowser(1);
  43. use CGI;
  44. use Data::Dumper;
  45. use Getopt::Long;
  46.  
  47.  
  48. ########################################################################################
  49. use vars qw(
  50. $pcd_version $dbhost $dbname $dbuser $dbpass $dbport $dbsocket
  51. $cron_dbindex @cron_db_array @ftp_server $dbpraefix @cron_dbpraefix_array
  52. $compression  $backup_path $logdatei $completelogdatei $command_beforedump $command_afterdump
  53. $cron_printout $cronmail $cronmail_dump $cronmailto $cronmailto_cc $cronmailfrom
  54. $cronftp $mp $multipart_groesse $email_maxsize
  55. $auto_delete $max_backup_files $perlspeed $optimize_tables_beforedump $result
  56. @key_value $pair $key $value $conffile @confname $logcompression $log_maxsize $complete_log
  57. $starttime $Sekunden $Minuten $Stunden $Monatstag $Monat $Jahr $Wochentag $Jahrestag $Sommerzeit
  58. $rct $tabelle  @tables @tablerecords $dt $sql_create @ergebnis @ar $sql_daten $inhalt
  59. $insert $totalrecords $error_message $cfh $oldbar $print_out $msg $dt $ftp $dateistamm $dateiendung
  60. $mpdatei $i $BodyNormal $BodyMultipart $BodyToBig $BodyNoAttach $BodyAttachOnly $Body $DoAttach $cmt $part $fpath $fname
  61. $fmtime $timenow $daydiff $datei $inh $gz $search $fdbname @str $item %dbanz $anz %db_dat
  62. $fieldlist $first_insert $my_comment $sendmail_call $config_read_from
  63. $cron_smtp $cron_smtp_port $cron_use_sendmail
  64. @ftp_transfer @ftp_timeout @ftp_user @ftp_pass @ftp_dir @ftp_server @ftp_port @ftp_mode @ftp_useSSL
  65. $output $query $skip $html_output $datei
  66. @trash_files $time_stamp @filearr $sql_file $backupfile $memory_limit $dbh $sth @db_array
  67. @dbpraefix_array @cron_command_before_dump @cron_command_after_dump $db_anz
  68. $record_count $filesize $status_start $status_end $sql_text $punktzaehler @backupfiles_name
  69. @backupfiles_size $mysql_commentstring $character_set $mod_gz $mod_mime $mod_ftp
  70. $mod_ftpssl @multipartfiles %db_tables @tablenames $tablename $opttbl $command $current_dir
  71. );
  72.  
  73. $memory_limit=100000;
  74. $mysql_commentstring="-- ";
  75. $character_set="utf8";
  76. $sql_text='';
  77. $sql_file='';
  78. $punktzaehler=0;
  79. @trash_files=();
  80. @filearr=();
  81. $opttbl=0;
  82. $dbpraefix="";
  83. $complete_log= 0;
  84. $cron_printout = 1;
  85. #config file
  86. $conffile="";
  87.  
  88. #return perl version
  89. sub GetPerlVersion (){
  90.     my $pversion ;
  91.     if ($^V){
  92.         $pversion = sprintf "v%vd", $^V ; # v5.10.1
  93.     }else{
  94.         $pversion = local $];
  95.     }
  96.     return $pversion;
  97. }
  98.  
  99. # import the optional modules ...
  100. my $eval_in_died;
  101. $mod_gz=0;
  102. $mod_ftp=0;
  103. $mod_mime=0;
  104. $mod_ftpssl=0;
  105. push (@INC, "$cgibin_path");
  106.  
  107. eval { $eval_in_died = 1; require Compress::Zlib; };
  108. if(!$@){
  109.     $mod_gz = 1;
  110.     import Compress::Zlib;
  111. }
  112. eval { $eval_in_died = 1; require Net::FTP; };
  113. if(!$@){
  114.     $mod_ftp = 1;
  115.     import Net::FTP;
  116. }
  117. eval { $eval_in_died = 1; require Net::FTPSSL; };
  118. if(!$@){
  119.     $mod_ftpssl = 1;
  120.     import Net::FTPSSL;
  121. }
  122. eval { $eval_in_died = 1; require MIME::Lite; };
  123. if(!$@){
  124.     $mod_mime = 1;
  125.     import MIME::Lite;
  126. }
  127.  
  128. #include config file
  129. $conffile="";
  130.  
  131. #read args from command
  132. GetOptions ("config=s" => \$conffile, "html_output=s"  => \$html_output);
  133. if (!defined $html_output) { $html_output=0; };    # suppress HTML Output
  134.  
  135. #called via browser or cmd
  136. if($ENV{'QUERY_STRING'}) {
  137.     $html_output=1; # turn HTML Output on if called via Browser-Request
  138.     my $querystring=$ENV{'QUERY_STRING'};
  139.     #$querystring=~ s/\?/ /g;
  140.     @key_value = split(/&/,$querystring);
  141.     foreach $pair(@key_value)
  142.     {
  143.         #$pair =~ tr/+/ /;
  144.         ($key,$value) = split(/=/,$pair);
  145.         if($key eq "config")
  146.         {
  147.             $value=~ s/\?/ /g;
  148.             $conffile=$value;
  149.             $config_read_from="Querystring";
  150.         }
  151.         if($key eq "html_output") { $html_output=$value; }; # overwrite var if given in call
  152.     }
  153. }else{
  154.     $config_read_from="shell";
  155. }
  156.  
  157. # Now we know if script was called via HTTP-Requets or from Shell. So output Headers
  158. PrintHeader();
  159.  
  160. # Security: try to detect wether someone tries to include some external configfile
  161. die "Hacking attempt - I wont do anything!\nGo away\n\n" if (lc($conffile) =~ m /:/);
  162.  
  163. #try to guess path if $absolute_path_of_configdir is not filled
  164. if($absolute_path_of_configdir eq "" || ! -d $absolute_path_of_configdir)
  165. {
  166.     #get full path
  167.     if ($config_read_from eq "shell") { $i=$0; } else { $i=$ENV{'SCRIPT_FILENAME'}; };
  168.     if ($i=~m#^(.*)\\#) {
  169.         #windows
  170.             $current_dir = $1;
  171.             $current_dir =~ s/msd\_cron//g;
  172.  
  173.             #set default log-files
  174.             $logdatei= $current_dir ."work\\log\\mysqldump_perl.log";
  175.             $completelogdatei= $current_dir . "work\\log\\mysqldump_perl.complete.log";
  176.  
  177.             $absolute_path_of_configdir = $current_dir ."work\\config\\";
  178.     } elsif ($i=~m#^(.*)\/# ) {
  179.         #*nix
  180.             $current_dir = $1;
  181.             $current_dir =~ s/msd\_cron//g;
  182.  
  183.             #set default log-files
  184.             $logdatei= $current_dir ."work/log/mysqldump_perl.log";
  185.             $completelogdatei= $current_dir . "work/log/mysqldump_perl.complete.log";
  186.  
  187.             $absolute_path_of_configdir = $current_dir."work/config/";
  188.     }
  189.     #$absolute_path_of_configdir =~ s/msd\_cron//g;
  190.     $backup_path = $absolute_path_of_configdir;
  191.     $backup_path =~ s/config/backup/g;
  192.  
  193.     #if zlib is available, set default to compress
  194.     if ($mod_gz){
  195.         $logdatei .= ".gz";
  196.         $completelogdatei .= ".gz";
  197.         $logcompression=1;
  198.     }
  199. }
  200.  
  201. $conffile=trim($conffile);
  202. if($conffile eq "")
  203. {
  204.     $conffile=$default_configfile; # no Parameter for configfile given -> use standardfile "mysqldumper.conf.php"
  205.     $config_read_from="standard configuration";
  206. }
  207.  
  208. # check config-dir
  209. $absolute_path_of_configdir=trim($absolute_path_of_configdir); # remove spaces
  210. if (!opendir(DIR, $absolute_path_of_configdir)){
  211.     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);
  212. }
  213. closedir(DIR);
  214.  
  215. #add trailing slash to confdir
  216. if(substr($absolute_path_of_configdir,-1) ne "/") {
  217.     $absolute_path_of_configdir=$absolute_path_of_configdir."/";
  218. }
  219.  
  220. #add conffile extension
  221. if (substr($conffile,length($conffile)-5,5) eq '.conf') { $conffile.='.php'; };
  222. if (substr($conffile,length($conffile)-9,9) ne '.conf.php') { $conffile.='.conf.php'; };
  223.  
  224. # load configuration file
  225. $datei=$absolute_path_of_configdir.$conffile;
  226. open(CONFIG,"<$datei") or die "\nI couldn't open the configurationfile:".$datei."\nFile not found or not accessible!\n\n";
  227. while (my $line = <CONFIG>)
  228. {
  229.     chomp($line);
  230.     if ($line ne '<?php' && $line ne '1;' && substr($line,0,2) ne '?>' && substr($line,0,1) ne '#')
  231.     {
  232.         eval($line);
  233.     }
  234. }
  235. close(CONFIG);
  236.  
  237. if ($html_output==1) { $cron_printout=1; }; # overwrite output if HTML-Output is activated
  238.  
  239. # more than one conffile?
  240. @confname=split(/\//,$conffile);
  241.  
  242.  
  243. PrintOut("<span style=\"color:#0000FF;\">Configuration '".$conffile."' loaded successfully from ".$config_read_from.".</span>");
  244. if($mod_gz==1) {
  245.     PrintOut("<span style=\"color:#0000FF;\">Compression Library loaded successfully...</span>");
  246. } else {
  247.     $compression=0;
  248.     PrintOut("<span style=\"color:red;\">Compression Library loading failed - Compression deactivated ...</span>");
  249. }
  250. if($mod_ftp==1) {
  251.     PrintOut("<span style=\"color:#0000FF;\">FTP Library loaded successfully...</span>");
  252. } else {
  253.     $cronftp=0;
  254.     PrintOut("<span style=\"color:red;\">FTP Library loading failed - FTP deactivated ...</span>");
  255. }
  256. if($mod_ftpssl==1) {
  257.     PrintOut("<span style=\"color:#0000FF;\">FTP-SSL Library loaded successfully...</span>");
  258.     $cronftp=1;
  259. } else {
  260.     $cronftp=0;
  261.     PrintOut("<span style=\"color:red;\">FTP-SSL Library loading failed - FTP-SSL deactivated ...</span>");
  262. }
  263. if($mod_mime==1) {
  264.     PrintOut("<span style=\"color:#0000FF;\">Mail Library loaded successfully...</span>");
  265. } else {
  266.     $cronmail=0;
  267.     PrintOut("<span style=\"color:red;\">Mail Library loading failed - Mail deactivated ...</span>");
  268. }
  269.  
  270. #try writing to logfile
  271. write_log("***********************************************************************\n");
  272. write_log("Starting backup using Perlscript version $pcd_version (using perl ".GetPerlVersion().")\n");
  273. PrintOut("Starting backup using Perlscript version $pcd_version (using perl ".GetPerlVersion().")\n");
  274. write_log("Using configuration $conffile\n");
  275.  
  276. #now do the dump
  277.  
  278. #more than one db
  279. if($cron_dbindex > -1)
  280. {
  281.     $dbname=$cron_db_array[$cron_dbindex];
  282.     $dbpraefix=$cron_dbpraefix_array[$cron_dbindex];
  283.     $command_beforedump=$cron_command_before_dump[$cron_dbindex];
  284.     $command_afterdump=$cron_command_after_dump[$cron_dbindex];
  285.     ExecuteCommand(1,$command_beforedump);
  286.     DoDump();
  287.     ExecuteCommand(2,$command_afterdump);
  288. }  
  289. else
  290. {
  291.     $db_anz=@cron_db_array;
  292.     for(my $ii = 0; $ii < $db_anz; $ii++)
  293.     {
  294.         if ($mp>0) { $mp=1; } # Part-Reset if using Multipart (for next database)
  295.         $dbname=$cron_db_array[$ii];
  296.         $dbpraefix=$cron_dbpraefix_array[$ii];
  297.         $command_beforedump=$cron_command_before_dump[$ii];
  298.         $command_afterdump=$cron_command_after_dump[$ii];
  299.         PrintOut("<hr>Starting to backup database <strong>`$dbname`</strong> (".($ii+1)."/$db_anz).");
  300.         if ($dbpraefix ne "") {
  301.             PrintOut("Scanning for tables with prefix '<span style=\"color:blue\">$dbpraefix</span>')");
  302.         }
  303.         ExecuteCommand(1,$command_beforedump);
  304.         DoDump();
  305.         ExecuteCommand(2,$command_afterdump);
  306.     }
  307. }
  308.  
  309. if($auto_delete>0)
  310. {
  311.     if($max_backup_files>0)
  312.     {
  313.         PrintOut("<br><b>Starting autodelete function: </b><br>Keep the latest <font color=\"#0000FF\">$max_backup_files</font> backup files for each database and delete older ones.");
  314.         find(\&AutoDeleteCount, $backup_path);
  315.         DoAutoDeleteCount();
  316.         DeleteFiles (\@trash_files);
  317.     }
  318. }
  319. closeScript();
  320. if ($html_output==0) { print "\nEnd of Cronscript\n"; }
  321.  
  322. ##############################################
  323. # Subroutinen                                #
  324. ##############################################
  325. sub DoDump {
  326.     undef(%db_tables);
  327.     ($Sekunden, $Minuten, $Stunden, $Monatstag, $Monat, $Jahr, $Wochentag, $Jahrestag, $Sommerzeit) = localtime(time);
  328.     $Jahr+=1900;$Monat+=1;$Jahrestag+=1;
  329.     my $CTIME_String = localtime(time);
  330.     my $ret=0;
  331.     $time_stamp=$Jahr."_".sprintf("%02d",$Monat)."_".sprintf("%02d",$Monatstag)."_".sprintf("%02d",$Stunden)."_".sprintf("%02d",$Minuten);
  332.     $starttime= sprintf("%02d",$Monatstag).".".sprintf("%02d",$Monat).".".$Jahr."  ".sprintf("%02d",$Stunden).":".sprintf("%02d",$Minuten);
  333.     $fieldlist="";
  334.     # Verbindung mit MySQL herstellen, $dbh ist das Database Handle
  335.     if (trim($dbsocket) eq "")
  336.     {
  337.         $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost:$dbport","$dbuser","$dbpass") || die "Database connection not made: $DBI::errstr";
  338.     }
  339.     else
  340.     {
  341.         $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost:$dbport;mysql_socket=$dbsocket","$dbuser","$dbpass") || die "Database connection not made: $DBI::errstr";
  342.     }
  343.     # herausfinden welche Mysql-Version verwendet wird
  344.     $sth = $dbh->prepare("SELECT VERSION()");
  345.     $sth->execute;
  346.     my @mysql_version=$sth->fetchrow;
  347.     my @v=split(/\./,$mysql_version[0]);
  348.  
  349.     if($v[0]>=5 || ($v[0]>=4 && $v[1]>=1) )
  350.     {
  351.         #mysql Version >= 4.1
  352.         $sth = $dbh->prepare("SET NAMES '".$character_set."'");
  353.         $sth->execute;
  354.         # get standard encoding of MySQl-Server
  355.         $sth = $dbh->prepare("SHOW VARIABLES LIKE 'character_set_connection'");
  356.         $sth->execute;
  357.         @ar=$sth->fetchrow;
  358.         $character_set=$ar[1];
  359.     }
  360.     else
  361.     {
  362.         # mysql Version < 4.1 -> no SET NAMES available
  363.         # get standard encoding of MySQl-Server
  364.         $sth = $dbh->prepare("SHOW VARIABLES LIKE 'character_set'");
  365.         $sth->execute;
  366.         @ar=$sth->fetchrow;
  367.         if (defined($ar[1])) { $character_set=$ar[1]; }
  368.     }
  369.     PrintOut("Characterset of connection and backup file set to <strong>".$character_set."</strong>.");
  370.    
  371.     #Statuszeile erstellen
  372.     my $t=0;
  373.     my $r=0;
  374.     my $st_e="\n";
  375.     undef(@tables);
  376.     undef(@tablerecords);
  377.     my $value=0;
  378.     my $engine='';
  379.     my %db_tables_views;
  380.     my $query="SHOW TABLE STATUS FROM `$dbname`";
  381.     if ($dbpraefix ne "")
  382.     {
  383.         $query.=" LIKE '$dbpraefix%'";
  384.         PrintOut("Searching for tables inside database <strong>`$dbname`</strong> with prefix <strong>'$dbpraefix'</strong>.");
  385.     }
  386.     else
  387.     {
  388.         PrintOut("Searching for tables inside database <strong>`$dbname`</strong>.");
  389.     }
  390.     $sth = $dbh->prepare($query);
  391.     $sth->execute || err_trap("Error executing: ".$query." !  MySQL-Error: ".$DBI::errstr);
  392.     while ( $value=$sth->fetchrow_hashref())
  393.     {
  394.         $value->{skip_data}=0; #defaut -> backup data of table
  395.         # decide if we need to skip the data while dumping (VIEWs and MEMORY)
  396.        
  397.         # check for old MySQL3-Syntax Type=xxx
  398.         if (defined $value->{Type})
  399.         {
  400.             # port old index type to index engine, so we can use the index Engine in the rest of the script
  401.             $value->{Engine}=$value->{Type};
  402.             $engine=uc($value->{Type});
  403.             if ($engine eq "MEMORY")
  404.             {
  405.                 $value->{skip_data}=1;
  406.             }
  407.         }
  408.  
  409.         # check for >MySQL3 Engine=xxx
  410.         if (defined $value->{Engine})
  411.         {
  412.             $engine=uc($value->{Engine});
  413.             if ($engine eq "MEMORY")
  414.             {
  415.                 $value->{skip_data}=1;
  416.             }
  417.         }
  418.  
  419.         # check for Views - if it is a view the comment starts with "VIEW"
  420.         if (defined $value->{Comment} && uc(substr($value->{Comment},0,4)) eq 'VIEW')
  421.         {
  422.             $value->{skip_data}=1;
  423.             $value->{Engine}='VIEW';
  424.             $value->{Update_time}='';
  425.             $db_tables_views{$value->{Name}}=$value;
  426.          }
  427.          else
  428.          {
  429.             $db_tables{$value->{Name}}=$value;
  430.          }
  431.          # cast indexes to int, cause they are used for builing the statusline
  432.          $value->{Rows}+=0;
  433.          $value->{Data_length}+=0;
  434.          $value->{Index_length}+=0;
  435.  
  436.     }
  437.     $sth->finish;
  438.  
  439.     @tablenames=sort keys(%db_tables);
  440.     # add VIEW at the end as they need all tables to be created before
  441.     @tablenames = (@tablenames,sort keys(%db_tables_views));
  442.     %db_tables = (%db_tables,%db_tables_views);
  443.    
  444.     $tablename='';
  445.     if (@tablenames<1)
  446.     {
  447.         PrintOut("There are no tables inside database <b>".$dbname."</b>! It doesn't make sense to backup an empty database. Skipping this one.");
  448.         return;
  449.     }
  450.     if($optimize_tables_beforedump==1)
  451.     {
  452.         optimise_tables();
  453.     }
  454.    
  455.     $st_e.="-- TABLE-INFO\n";
  456.     foreach $tablename (@tablenames)
  457.     {
  458.         my $dump_table=1;
  459.         if ($dbpraefix ne "")
  460.         {
  461.             if (substr($tablename,0,length($dbpraefix)) ne $dbpraefix)
  462.             {
  463.                 # exclude table from backup because it doesn't fit to praefix
  464.                 $dump_table=0;
  465.             }
  466.         }
  467.                        
  468.         if ($dump_table==1)
  469.         {
  470.             $r+=$db_tables{$tablename}{Rows}; #calculate nr of records
  471.             push(@tables,$db_tables{$tablename}{Name}); # add tablename to backuped tables
  472.             $t++;
  473.             if (!defined $db_tables{$tablename}{Update_time})
  474.             {
  475.                 $db_tables{$tablename}{Update_time}=0;
  476.             }
  477.            
  478.             $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";
  479.         }
  480.     }
  481.     $st_e.="-- EOF TABLE-INFO";
  482.    
  483.     PrintOut("Found ".(@tables)." tables with $r records.");
  484.  
  485.     #AUFBAU der Statuszeile:
  486.     #    -- Status:tabellenzahl:datensaetze:Multipart:Datenbankname:script:scriptversion:Kommentar:MySQLVersion:Backupflags:SQLBefore:SQLAfter:Charset:EXTINFO
  487.     #    Aufbau Backupflags (1 Zeichen pro Flag, 0 oder 1, 2=unbekannt)
  488.     #    (complete inserts)(extended inserts)(ignore inserts)(delayed inserts)(downgrade)(lock tables)(optimize tables)
  489.     #
  490.     $status_start=$mysql_commentstring."Status:$t:$r:";
  491.     my $flags="1$optimize_tables_beforedump";
  492.     $status_end=":$dbname:perl:$pcd_version:$my_comment:$mysql_version[0]:$flags";
  493.     $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";
  494.  
  495.  
  496.     if($mp>0)
  497.     {
  498.         $sql_text=$status_start."MP_$mp".$status_end;
  499.     }
  500.     else
  501.     {
  502.         $sql_text=$status_start.$status_end;
  503.     }
  504.     NewFilename();
  505.    
  506.     $totalrecords=0;
  507.     foreach $tablename (@tables)
  508.     {
  509.         # first get CREATE TABLE Statement
  510.         if($dbpraefix eq "" || ($dbpraefix ne "" && substr($tablename,0,length($dbpraefix)) eq $dbpraefix))
  511.         {
  512.             PrintOut("Dumping table `<strong>$tablename</strong>` <em>(Type ".$db_tables{$tablename}{Engine}.")</em>:");
  513.             $a="\n\n$mysql_commentstring\n$mysql_commentstring"."Table structure for table `$tablename`\n$mysql_commentstring\n";
  514.             if ($db_tables{$tablename}{Engine} ne 'VIEW' ) {
  515.                 $a.="DROP TABLE IF EXISTS `$tablename`;\n";
  516.             } else {
  517.                 $a.="DROP VIEW IF EXISTS `$tablename`;\n";
  518.             }
  519.             $sql_text.=$a;
  520.             $sql_create="SHOW CREATE TABLE `$tablename`";
  521.             $sth = $dbh->prepare($sql_create);
  522.             if (!$sth)
  523.             {
  524.                 err_trap("<font color=\"red\">Fatal error sending Query '".$sql_create."'! MySQL-Error: ".$DBI::errstr);
  525.             }
  526.  
  527.             $sth->execute || err_trap("Couldn't execute ".$sql_create);
  528.             @ergebnis=$sth->fetchrow;
  529.             $sth->finish;
  530.             $a=$ergebnis[1].";\n";
  531.             if (length($a)<10)
  532.             {
  533.                 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);
  534.                 $skip=1;
  535.             }
  536.             else
  537.             {
  538.                 $sql_text.=$a;
  539.             }
  540.            
  541.             if ($db_tables{$tablename}{skip_data} == 0)
  542.             {
  543.                 $sql_text.="\n$mysql_commentstring\n$mysql_commentstring"."Dumping data for table `$tablename`\n$mysql_commentstring\n";
  544.                 $sql_text.="/*!40000 ALTER TABLE `$tablename` DISABLE KEYS */;";
  545.  
  546.                 WriteToFile($sql_text,0);
  547.                 $sql_text="";
  548.                 $punktzaehler=0;
  549.  
  550.                 # build fieldlist
  551.                 $fieldlist="(";
  552.                 $sql_create="SHOW FIELDS FROM `$tablename`";
  553.                 $sth = $dbh->prepare($sql_create);
  554.                 if (!$sth)
  555.                 {
  556.                     err_trap("<font color=\"red\">Fatal error sending Query '".$sql_create."'! MySQL-Error: ".$DBI::errstr);
  557.                 }
  558.                
  559.                 $sth->execute || err_trap("Couldn't execute ".$sql_create);
  560.                 while ( @ar=$sth->fetchrow) {
  561.                     $fieldlist.="`".$ar[0]."`,";
  562.                 }
  563.                 $sth->finish;
  564.                
  565.                 # remove trailing ',' and add ')'
  566.                 $fieldlist=substr($fieldlist,0,length($fieldlist)-1).")";
  567.  
  568.                 # how many rows
  569.                 $rct=$db_tables{$tablename}{Rows};
  570.  
  571.                 for (my $ttt=0;$ttt<$rct;$ttt+=$perlspeed)
  572.                 {
  573.                     # default beginning for INSERT-String
  574.                     $insert = "INSERT INTO `$tablename` $fieldlist VALUES (";
  575.                     $first_insert=0;
  576.                    
  577.                     # get rows (parts)
  578.                     $sql_daten="SELECT * FROM `$tablename` LIMIT ".$ttt.",".$perlspeed.";";
  579.                     $sth = $dbh->prepare($sql_daten);
  580.                     if (!$sth)
  581.                     {
  582.                         err_trap("<font color=\"red\">Fatal error sending Query '".$sql_create."'! MySQL-Error: ".$DBI::errstr);
  583.                     }
  584.                     $sth->execute || err_trap("Couldn't execute \"".$sql_daten."\" - MySQL-Error: ".$DBI::errstr);
  585.                     while ( @ar=$sth->fetchrow)
  586.                     {
  587.                         #Start the insert
  588.                         if($first_insert==0)
  589.                         {
  590.                             $a="\n$insert";
  591.                         }
  592.                         else
  593.                         {
  594.                             $a="\n(";
  595.                         }
  596.                        
  597.                         # quote all values
  598.                         foreach $inhalt(@ar) { $a.= $dbh->quote($inhalt).","; }
  599.                        
  600.                         # remove trailing ',' and add end-sql
  601.                         $a=substr($a,0, length($a)-1).");";
  602.                         $sql_text.= $a;
  603.                         if($memory_limit>0 && length($sql_text)>$memory_limit)
  604.                         {
  605.                             WriteToFile($sql_text);
  606.                             $sql_text="";
  607.                             if($mp>0 && $filesize>$multipart_groesse) {NewFilename();}
  608.                         }
  609.                     }
  610.                     $sth->finish;
  611.                 }
  612.                 $sql_text.="\n/*!40000 ALTER TABLE `$tablename` ENABLE KEYS */;\n";
  613.             }
  614.  
  615.             # write sql commands to file
  616.             WriteToFile($sql_text);
  617.             $sql_text="";
  618.  
  619.             if ($db_tables{$tablename}{skip_data} == 0)
  620.             {
  621.                 PrintOut("\n<br><em>$db_tables{$tablename}{Rows} inserted records (size of backupfile: ".byte_output($filesize).")</em>");
  622.                 $totalrecords+=$db_tables{$tablename}{Rows};
  623.             }
  624.             else
  625.             {
  626.                 PrintOut("\n<br>Dumping structure of <strong>`$tablename`</strong> <em>(Type ".$db_tables{$tablename}{Engine}." ) (size of backupfile: ".byte_output($filesize).")</em>");
  627.             }
  628.            
  629.             if($mp>0 && $filesize>$multipart_groesse) {NewFilename();}
  630.         }
  631.     }
  632.     # end
  633.     WriteToFile("\nSET FOREIGN_KEY_CHECKS=1;\n");
  634.     WriteToFile($mysql_commentstring."EOB\n");
  635.     PrintOut("\n<hr>Finished backup of database `$dbname`.\n");
  636.     write_log("Finished backup of database `$dbname`.\n");
  637.  
  638.     # sent via email
  639.     if($cronmail==1) {
  640.         PrintOut("Sending E-Mail ...");
  641.         $ret=send_mail();
  642.         if ($ret)
  643.         {
  644.             write_log("Recipient/s: $cronmailto $cronmailto_cc\n");
  645.             PrintOut("Recipient/s: $cronmailto $cronmailto_cc\n");
  646.         }
  647.     }
  648.  
  649.     # sent to ftp-server
  650.     send_ftp();
  651. }
  652.  
  653. #print error message and optional exit
  654. sub err_trap {
  655.     my $error_message = shift(@_);
  656.    
  657.     #continue instead of exit
  658.     my $continue = shift(@_);
  659.    
  660.     #don't write to logfile, if we did not read config before
  661.     my $nolog = shift(@_);
  662.    
  663.     PrintOut("<font color=\"red\">Perl Cronscript ERROR: <b>$error_message</b></font><br>\n");
  664.     write_log("<font color=\"red\">Perl Cronscript ERROR: <b>$error_message</b></font><br>\n") if !defined $nolog;
  665.     if (!defined $continue || $continue ==0)
  666.     {
  667.         PrintOut("<font color=\"red\"><b>Stopping script because of this fatal error!</b></font><br>\n");
  668.         write_log("<font color=\"red\"><b>Stopping script because of this fatal error!</b></font><br>\n") if !defined $nolog;
  669.         exit(1);
  670.     }
  671. }
  672.  
  673. sub PrintHeader {
  674.     my $cgi = new CGI;
  675.     my $perlversion = GetPerlVersion();
  676.    
  677.     if ($html_output==1)
  678.     {
  679.         print $cgi->header(-type => 'text/html; charset=utf-8', -cache_control => 'no-cache, no-store, must-revalidate');
  680.         print "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">\n";
  681.         print "<html>\n<head>\n<title>MySQLDumper - Perl CronDump [Version $pcd_version (using perl $perlversion)]</title>\n";
  682.         print "<style type=\"text/css\">\nbody { padding:20px; font-family:Verdana,Helvetica,Sans-Serif;font-size: 0.9em !important;}</style>\n";
  683.         print "</head>\n<body><h3>MySQLDumper - Perl CronDump [Version $pcd_version (using perl $perlversion)]</h3>\n";
  684.     }
  685.     else
  686.     {
  687.         #small output for external cronjobs, which expect a small returnvalue
  688.         print "MySQLDumper - Perl CronDump [Version $pcd_version] started successfully (using perl $perlversion)\n";
  689.     }
  690. }
  691.  
  692. sub PrintOut {
  693.     $print_out = shift(@_);
  694.  
  695.     if (defined $print_out && length(trim($print_out))>0)
  696.     {
  697.         if($complete_log==1)
  698.         {
  699.             my $logsize=0;
  700.             ($Sekunden, $Minuten, $Stunden, $Monatstag, $Monat, $Jahr, $Wochentag, $Jahrestag, $Sommerzeit) = localtime(time);
  701.             $Jahr+=1900; $Monat+=1;$Jahrestag+=1;
  702.             $dt=sprintf("%02d",$Monatstag).".".sprintf("%02d",$Monat).".".sprintf("%02d",$Jahr)." ".sprintf("%02d",$Stunden).":".sprintf("%02d",$Minuten).":".sprintf("%02d",$Sekunden);
  703.             if (-e $completelogdatei)
  704.             {
  705.                 $logsize=(stat($completelogdatei))[7];
  706.                 unlink($completelogdatei) if($logsize + length($print_out)>$log_maxsize && $log_maxsize>0);
  707.             }
  708.             my $output=$print_out;
  709.             #$output =~ s/<(.*?)>//gi;
  710.             $output =~ s/\n//gi;
  711.             $output =~ s/\r//gi;
  712.             $output =~ s/<br>//gi;
  713.             $output=trim($output);
  714.            
  715.             if ( ($logcompression==0) || ($mod_gz==0))
  716.             {
  717.                 open(DATEI,">>$completelogdatei") || err_trap('can\'t open mysqldump_perl.complete.log ('.$completelogdatei.').');
  718.                 print DATEI "$dt $output\n" || err_trap('can\'t write to mysqldump_perl.complete.log ('.$completelogdatei.').');
  719.                 close(DATEI)|| err_trap('can\'t close mysqldump_perl.complete.log ('.$completelogdatei.').');
  720.                 chmod(0777,$completelogdatei);
  721.             }
  722.             else
  723.             {
  724.                 $gz = gzopen($completelogdatei, "ab") || err_trap("Cannot open mysqldump_perl.complete.log.gz. ");
  725.                 $gz->gzwrite("$dt $output\n")  || err_trap("Error writing mysqldump_perl.complete.log.gz. ");
  726.                 $gz->gzclose ;
  727.                 chmod(0777,$completelogdatei);
  728.             }
  729.         }
  730.         if($cron_printout==1)
  731.         {
  732.             #save current autoflush-setting
  733.             local ($oldbar) = $|;
  734.            
  735.             #save current output filehandle and change it to STDOUT
  736.             $cfh = select (STDOUT);
  737.  
  738.             #set autoflush on
  739.             $| = 1;
  740.            
  741.             #remove html-tags
  742.             if($html_output==0)
  743.             {
  744.                 $print_out =~ s/<(.*?)>//gi;
  745.             }
  746.            
  747.             print $print_out;
  748.            
  749.             #TODO don't print <br> with the last printout (-> wrong html-syntax)
  750.             if ($html_output==1){ print "<br>\n"; } else { print "\n"; };
  751.            
  752.             #restore old autoflush-setting
  753.             $| = $oldbar;
  754.            
  755.             #set default output back to old filehandle
  756.             select ($cfh);
  757.         }
  758.     }
  759. }
  760.  
  761. sub write_log {
  762.     $msg = shift(@_);
  763.     ($Sekunden, $Minuten, $Stunden, $Monatstag, $Monat, $Jahr, $Wochentag, $Jahrestag, $Sommerzeit) = localtime(time);
  764.     $Jahr+=1900; $Monat+=1;$Jahrestag+=1;
  765.     #$dt=sprintf("%02d",$Monatstag).".".sprintf("%02d",$Monat).".".sprintf("%02d",$Jahr)." ".sprintf("%02d",$Stunden).":".sprintf("%02d",$Minuten).":".sprintf("%02d",$Sekunden);
  766.     $dt=sprintf("%02d.%02d.%02d %02d:%02d:%02d",$Monatstag,$Monat,$Jahr,$Stunden,$Minuten,$Sekunden);
  767.  
  768.     my $logsize=0;
  769.     if (-e $logdatei)
  770.     {
  771.         $logsize=(stat($logdatei))[7];
  772.         unlink($logdatei) if($logsize+200>$log_maxsize && $log_maxsize>0);
  773.     }
  774.  
  775.     if ( ($logcompression==0) || ($mod_gz==0))
  776.     {
  777.         open(DATEI,">>$logdatei") || err_trap("Can't open file ($logdatei).");
  778.         print DATEI "$dt $msg" || err_trap("Can't write to file ($logdatei).");
  779.         close(DATEI)|| err_trap("can't close file ($logdatei).");
  780.         chmod(0777,$logdatei);
  781.     }
  782.     else
  783.     {
  784.         $gz = gzopen($logdatei, "ab") || err_trap("Can't open $logdatei.");
  785.         $gz->gzwrite("$dt $msg")  || err_trap("Can't write to $logdatei. ");
  786.         $gz->gzclose ;
  787.         chmod(0777,$logdatei);
  788.     }
  789. }
  790.  
  791. sub send_ftp {
  792.     #save files to ftp-server
  793.     my $ret=0;
  794.     my $x=0;
  795.     for(my $i = 0; $i <3; $i++)
  796.     {
  797.         if ($ftp_transfer[$i]==1)
  798.         {
  799.             if ($ftp_timeout[$i]<1) { $ftp_timeout[$i]=30; };
  800.                 if (${ftp_useSSL[$i]}==1 && $mod_ftpssl==1)
  801.                 {    #use ftp-ssl
  802.                     $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);
  803.                 }
  804.                 else
  805.                 {    #use 'plain' ftp
  806.                     $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);
  807.                 }
  808.             $ftp->login($ftp_user[$i], $ftp_pass[$i]) or err_trap("FTP-ERROR: Couldn't login\n",1);
  809.             $ftp->binary();
  810.             $ftp->cwd($ftp_dir[$i]) or err_trap("FTP-ERROR: Couldn't change directory: ".$ftp_dir[$i],1);
  811.            
  812.             if($mp==0)
  813.             {
  814.                 PrintOut("FTP: transferring `$backupfile`");
  815.                 $ret=$ftp->put($sql_file);
  816.                 if (!$ret)
  817.                 {
  818.                     err_trap("FTP-Error: Couldn't put $backupfile to ".$ftp_server[$i]." into dir ".$ftp_dir[$i]."\n",1);
  819.                 }
  820.                 else
  821.                 {
  822.                     write_log("FTP: transferred `$backupfile` to $ftp_server[$i] into dir $ftp_dir[$i] successfully\n");
  823.                     PrintOut(" to $ftp_server[$i] into dir $ftp_dir[$i] was successful.\n");
  824.                 }
  825.             }
  826.             else
  827.             {
  828.                 $dateistamm=substr($backupfile,0,index($backupfile,"part_"))."part_";
  829.                 $dateiendung=($compression==1)?".sql.gz":".sql";
  830.                 $mpdatei="";
  831.                 for ($x=1;$x<$mp;$x++)
  832.                 {
  833.                     $mpdatei=$dateistamm.$x.$dateiendung;
  834.                     PrintOut("FTP: transferring multipart $mpdatei");
  835.                    
  836.                     $ret=$ftp->put($backup_path.$mpdatei);
  837.                     if (!$ret)
  838.                     {
  839.                         err_trap("Couldn't put $backup_path.$mpdatei to ".$ftp_server[$i]." into dir ".$ftp_dir[$i]."\n",1);
  840.                     }
  841.                     else
  842.                     {
  843.                         #write_log("FTP: transferring of `$mpdatei` to ".$ftp_server[$i]." finished successfully.\n");
  844.                         #PrintOut("FTP: transferring of `$mpdatei` to $ftp_server[$i] finished successfully.");
  845.                         write_log("FTP: transferred multipart '$mpdatei' to $ftp_server[$i] into dir $ftp_dir[$i] successfully\n");
  846.                         PrintOut(" to $ftp_server[$i] into dir $ftp_dir[$i] was successful.\n");
  847.                     }
  848.                 }
  849.             }
  850.         }
  851.     }
  852. }
  853.  
  854. sub send_mail {
  855.     #sent email w/o files
  856.     $BodyNormal='Find attached a backup of your database `'.$dbname.'`.';
  857.     $BodyMultipart="A multipart backup has been made.<br>You will receive one or more emails with the backup-files attached.<br>The database `".$dbname."` has been backuped.<br>The following files have been created:";
  858.     $BodyToBig="The backup is bigger than the allowed max-limit of ".byte_output($email_maxsize)." and has not been attached.<br>Backup of database ".$dbname."<br><br>The following files have been created:";
  859.     $BodyNoAttach="The backup has not been attached.<br>I saved your database `".$dbname."` to file<br>";
  860.     $BodyAttachOnly="Here is your backup.";
  861.     $Body="";
  862.     $DoAttach=1;
  863.     my @mparray;
  864.     my $ret=0;
  865.     if($mp==0)
  866.     {    #no multipart
  867.         if(($email_maxsize>0 && $filesize>$email_maxsize) || $cronmail_dump==0)
  868.         {
  869.             #attache files
  870.             if($cronmail_dump==0)
  871.             {    #The backup has not been attached
  872.                 $Body=$BodyNoAttach.$backupfile." (".byte_output($filesize).")";
  873.             }
  874.             else
  875.             {    #The backup is bigger than the allowed max-limit
  876.                 $Body=$BodyToBig.$backupfile." (".byte_output($filesize).")";
  877.             }
  878.             $DoAttach=0;
  879.         }
  880.         else
  881.         {    #Find attached your backup
  882.             $Body=$BodyNormal." File ".$backupfile." (".byte_output($filesize).")";
  883.         }
  884.     }
  885.     else
  886.     {    #multipart
  887.         $Body=$BodyMultipart;
  888.         $dateistamm=substr($backupfile,0,index($backupfile,"part_"))."part_";
  889.         $dateiendung=($compression==1)?".sql.gz":".sql";
  890.         $mpdatei="";
  891.         for ($i=1;$i<$mp;$i++)
  892.         {
  893.             $mpdatei=$dateistamm.$i.$dateiendung;
  894.             push(@mparray,"$mpdatei|$i");
  895.             $filesize=(stat($backup_path.$mpdatei))[7];
  896.             $Body.="\n<br>$mpdatei (".(byte_output($filesize))." )";
  897.         }
  898.     }
  899.     $Body.="\n\n<br><br>Best regards,<br><br>MySQLDumper<br>If you have any questions, feel free and visit the support board at:<br><a href=\"http://forum.mysqldumper.de\">http://forum.mysqldumper.de</a>";
  900.  
  901.     if ($cron_use_sendmail==1)
  902.     {    
  903.         MIME::Lite->send("sendmail", $sendmail_call) || err_trap("Error setting sendmail call!",1);
  904.     }
  905.     else
  906.     {
  907.         MIME::Lite->send('smtp', $cron_smtp, Timeout=>60) || err_trap("Error setting smtp call !",1);    
  908.     }
  909.  
  910.     $msg = MIME::Lite->new(
  911.         From    => $cronmailfrom,
  912.         To      => $cronmailto,
  913.         Cc    => $cronmailto_cc,
  914.         Subject => "MSD (Perl) - Backup of DB ".$dbname,
  915.         Type    => 'text/html; charset=iso-8859-1',
  916.         Data    => "<body>\n".$Body."</body>\n"
  917.     );
  918.  
  919.     if($DoAttach==1 && $mp==0)
  920.     {    #attach files, no multipart
  921.         $msg->attach(
  922.             Type     => "BINARY",
  923.             Path     => "$sql_file",
  924.             Encoding => "base64",
  925.             Filename => "$backupfile"
  926.         );
  927.         $ret=$msg->send;
  928.         if (!$ret)
  929.         {
  930.             err_trap("Error 1 sending mail with backup ".$backupfile."!",1);
  931.         }
  932.         else
  933.         {
  934.             PrintOut("<font color=\"green\">E-Mail with backup ".$backupfile." sent successfully.</font>");
  935.             write_log("<font color=\"green\">E-Mail with backup ".$backupfile." sent successfully.</font>\n");
  936.         }
  937.         return $ret;
  938.     }
  939.    
  940.     if($DoAttach==1 && $mp>0 && $cronmail_dump>0)
  941.     {    #attach files, multipart
  942.         foreach $datei(@mparray)
  943.         {
  944.             @str=split(/\|/,$datei);
  945.             $msg = MIME::Lite->new(
  946.                 From    => $cronmailfrom,
  947.                 To      => $cronmailto,
  948.                 Cc    => $cronmailto_cc,
  949.                 Subject => "MSD (Perl) - Backup of DB $dbname File ".$str[1]." of ".@mparray ,
  950.                 Type    => 'text/html; charset=iso-8859-1',
  951.                 Data    => "<body>\n".$Body."</body>\n"
  952.             );
  953.  
  954.             $msg->attach(
  955.                 Type     => "BINARY",
  956.                 Path     => $backup_path.$str[0],
  957.                 Encoding => "base64",
  958.                 Filename => $str[0]
  959.             );
  960.             $ret=$msg->send;
  961.             if (!$ret)
  962.             {
  963.                 err_trap("Error 2 sending mail with backup ".$str[0]."!",1);
  964.             }
  965.             else
  966.             {
  967.                 PrintOut("<font color=\"green\">E-Mail with backup ".$str[0]." sent successfully.</font>");
  968.                 write_log("<font color=\"green\">E-Mail with backup ".$str[0]." sent successfully.</font>\n");
  969.             }
  970.         }
  971.         return $ret;
  972.     }
  973.  
  974.     $ret=$msg->send;
  975.     if (!$ret)
  976.     {
  977.         err_trap("<font color=\"error\">Error 3 sending E-Mail!</font><br>",1);
  978.     }
  979.     else
  980.     {
  981.         PrintOut("<font color=\"green\">E-Mail sent successfully.</font>");
  982.         write_log("<font color=\"green\">E-Mail sent successfully.</font>\n");
  983.     }
  984.     return $ret;
  985. }
  986.  
  987. sub NewFilename {
  988.     $part="";
  989.     if($mp>0)
  990.     {
  991.         $part="_part_$mp";
  992.         $mp++;
  993.     }
  994.     if($compression==0)
  995.     {
  996.         $sql_file=$backup_path.$dbname."_".$time_stamp.$part.".sql";
  997.         $backupfile=$dbname."_".$time_stamp.$part.".sql";
  998.     }
  999.     else
  1000.     {
  1001.         $sql_file=$backup_path.$dbname."_".$time_stamp.$part.".sql.gz";
  1002.         $backupfile=$dbname."_".$time_stamp.$part.".sql.gz";
  1003.     }
  1004.     if($mp==0)
  1005.     {
  1006.         PrintOut("\n<br>Starting to dump data into file <strong>`$backupfile`</strong>");
  1007.         write_log("Dumping data into file <strong>`$backupfile`</strong> \n");
  1008.     }
  1009.     if($mp==2)
  1010.     {
  1011.         PrintOut("\n<br>Starting to dump data into multipart-file <strong>`$backupfile`</strong>");
  1012.         write_log("Start Perl Multipart-Dump with file `$backupfile` \n");
  1013.     }
  1014.     if($mp>2)
  1015.     {
  1016.         PrintOut("\n<br>Continuing Multipart-Dump with file <strong>`$backupfile`</strong>");
  1017.         write_log("Continuing Multipart-Dump with file `$backupfile` \n");
  1018.     }
  1019.     if($mp>0)
  1020.     {
  1021.         $sql_text=$status_start."MP_".($mp-1).$status_end;
  1022.     }
  1023.     else
  1024.     {
  1025.         $sql_text=$status_start.$status_end;
  1026.     }
  1027.     $sql_text.="/*!40101 SET NAMES '".$character_set."' */;\n";
  1028.     $sql_text.="SET FOREIGN_KEY_CHECKS=0;\n";
  1029.    
  1030.     WriteToFile($sql_text,1);
  1031.     chmod(0777,$sql_file);
  1032.     $sql_text="";
  1033.     $first_insert=0;
  1034.     $punktzaehler=0;
  1035.     push(@backupfiles_name,$sql_file);
  1036. }
  1037.  
  1038. sub WriteToFile {
  1039.     $inh=shift;
  1040.     my $points=shift;
  1041.     if (!defined($points)) { $points=2; }
  1042.    
  1043.     if(length($inh)>0) {
  1044.         if($compression==0){
  1045.             open(DATEI,">>$sql_file");
  1046.             print DATEI $inh;
  1047.             close(DATEI);
  1048.         } else {
  1049.             $gz = gzopen($sql_file, "ab") || err_trap("Cannot open ".$sql_file);
  1050.             $gz->gzwrite($inh)  || err_trap("Error writing ".$sql_file);
  1051.             $gz->gzclose ;
  1052.         }
  1053.         if ($points>1)
  1054.         {
  1055.             print ".";
  1056.         }
  1057.         $filesize= (stat($sql_file))[7];
  1058.         $punktzaehler++;
  1059.         if($punktzaehler>120)
  1060.         {
  1061.             if ($html_output==1) { print "<br>"; } else { print "\n"; };
  1062.             $punktzaehler=0;
  1063.         }
  1064.     }
  1065. }
  1066.  
  1067. sub AutoDeleteCount {
  1068.     $fpath=$File::Find::name;
  1069.     $fname=basename($fpath);
  1070.     my @fileparts=split(/\./,"$fname");
  1071.     my $partcount=@fileparts;
  1072.     if ($partcount>1)
  1073.     {
  1074.         my $end=$fileparts[(@fileparts-1)];
  1075.         # Read Statusline and extract info
  1076.         my $line='';
  1077.         if ($end eq 'sql')
  1078.         {
  1079.             if (open(DATEI,"<$fpath"))
  1080.             {
  1081.                 $line=<DATEI>;            
  1082.                 close(DATEI);
  1083.             }
  1084.             else
  1085.             {
  1086.                 print "<br>Error: couldn\'t open file: ".$fpath;
  1087.             }
  1088.         }
  1089.         if ($end eq 'gz')
  1090.         {
  1091.             $gz = gzopen("$fpath", "rb");
  1092.             if ($gz)
  1093.             {
  1094.                 $gz->gzreadline($line);
  1095.                 $gz->gzclose;
  1096.             }
  1097.             else
  1098.             {
  1099.                 print "<br>Error: couldn\'t open file: ".$fpath;
  1100.             }
  1101.         }
  1102.         if (length($line)>0 && (substr($line,0,10) eq "-- Status:" ||substr($line,0,11) eq "--  Status:"))
  1103.         {
  1104.             #statusline read
  1105.             my @infos=split(/\:/,$line);
  1106.             my $file_multipart=($infos[3])?$infos[3]:'';
  1107.             $file_multipart=~ s/MP_/ /g;
  1108.             $file_multipart=trim($file_multipart);
  1109.             my $file_databasename=($infos[4])?$infos[4]:'';
  1110.             if ($file_multipart eq "" || substr($file_multipart,0,1) eq "0")
  1111.             {
  1112.                 #no multipartfile
  1113.                 push(@filearr,"$fname|$file_databasename");
  1114.             }
  1115.             else
  1116.             {
  1117.                 push(@filearr,"$fname|$file_databasename|$file_multipart");
  1118.             }
  1119.         }
  1120.         else
  1121.         {
  1122.             PrintOut("No Statusline in `<strong>$fname</strong>` found. Seems not to be a MySQLDumper file. Skipping file.");
  1123.         }
  1124.     }
  1125. }
  1126.  
  1127. sub DoAutoDeleteCount {
  1128.     my @str;
  1129.     my @dbarray;
  1130.     my $item;
  1131.     my $item2;
  1132.     my %dbanz;
  1133.     my $anz=@filearr;
  1134.     # sort filearr descending -> so the latest backups are at top
  1135.     # multipartfiles sorting is also descending -> part3, part2, part1
  1136.     @filearr=sort{"$b" cmp "$a"}(@filearr);
  1137.     @multipartfiles=();
  1138.     if ($anz>0)
  1139.     {
  1140.         foreach $item (@filearr)
  1141.         {
  1142.             @str=split(/\|/,$item);
  1143.             # str[0]=filename, str[1]=databasename, str[2]=multipart number
  1144.                    
  1145.             #init db-counter if this index doesn't exist yet
  1146.             if (defined $str[1] && !defined $dbanz{$str[1]})
  1147.             {
  1148.                 $dbanz{$str[1]}=0;
  1149.                 @multipartfiles=();
  1150.             }        
  1151.             #PrintOut($max_backup_files.': '.$dbanz{$str[1]}.' -> '.$str[0].' - '.$str[1].' - '.$str[2]);
  1152.                
  1153.             #no multipart file -> update db counter
  1154.             if(defined $str[1] && !defined $str[2])
  1155.             {
  1156.                 # handling for non multipart files
  1157.                 $dbanz{$str[1]}++;
  1158.                 # is the max number of backups for this database reached?
  1159.                 # if yes -> push the actual filename into trash_files
  1160.                 if($dbanz{$str[1]}>$max_backup_files)
  1161.                 {
  1162.                     push(@trash_files, $str[0]);
  1163.                 }
  1164.             }
  1165.             else
  1166.             {
  1167.                 # keep multipartz filename
  1168.                 if(defined $str[1] && $dbanz{$str[1]}>=$max_backup_files)
  1169.                 {
  1170.                     push(@multipartfiles,$str[0]);
  1171.                 }
  1172.                
  1173.                 # if it is a multipart file and it is part_1 -> update db counter
  1174.                 # multiparts with higher numbers already passed the loop, so we can use
  1175.                 # part1 to increase the db-counter
  1176.                 if(defined $str[2] && $str[2]==1)
  1177.                 {
  1178.                     $dbanz{$str[1]}++;
  1179.                     # now check if we have reached the limit
  1180.                     if($dbanz{$str[1]}>$max_backup_files)
  1181.                     {
  1182.                         foreach $item2 (@multipartfiles)
  1183.                         {
  1184.                             push(@trash_files, $item2);
  1185.                         }
  1186.                     }
  1187.                     # clear array for next multipart backup
  1188.                     @multipartfiles=();
  1189.                 }
  1190.             }
  1191.         }
  1192.     }
  1193. }
  1194.  
  1195. sub DeleteFiles
  1196. {
  1197.     my $res=0;
  1198.     if(@trash_files==0)
  1199.     {
  1200.         PrintOut("<font color=red><b>No file to delete.</b></font>");
  1201.     }
  1202.     else
  1203.     {
  1204.         foreach $datei(@trash_files)
  1205.         {
  1206.             my $file_to_delete = $backup_path.$datei;
  1207.             $res=unlink($file_to_delete);
  1208.             if ($res)
  1209.             {
  1210.                 PrintOut("Autodelete: old backup file <font color=red><b>".$datei."</b></font> deleted.");
  1211.                 write_log( "Autodelete: old backup file <b>$datei</b> deleted.\n" ) ;
  1212.             }
  1213.             else
  1214.             {
  1215.                 err_trap("Autodelete: <font color=red>Error deleting old backup file <b>".$datei."</b></font>!<br>",1);
  1216.             }
  1217.         }
  1218.         undef(@trash_files);
  1219.     }
  1220. }
  1221.  
  1222. sub ExecuteCommand
  1223. {
  1224.     my $cmt = shift(@_);
  1225.     my $command = shift(@_);
  1226.     my (@cad, $errText, $succText, $cd2, $commandDump);
  1227.     my $err='';
  1228.     $commandDump=$command;
  1229.     if($cmt==1)
  1230.     {  #before dump
  1231.         $errText="Error while executing Query before Dump";
  1232.         $succText="Executing Query before Dump was successful";
  1233.     }
  1234.     else
  1235.     {
  1236.         $errText="Error while executing Query after Dump";
  1237.         $succText="executing Query after Dump was successful";
  1238.     }
  1239.    
  1240.     if(defined $commandDump && length($commandDump)>0)
  1241.     {
  1242.         if(substr($commandDump,0,7) ne "system:")
  1243.         {    
  1244.             # prepare command
  1245.             $commandDump = replaceQueryStringSimple($commandDump);
  1246.             if (trim($dbsocket) eq "")
  1247.             {
  1248.                 $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost:$dbport","$dbuser","$dbpass") || err_trap("Database connection not made: $DBI::errstr");
  1249.             }
  1250.             else
  1251.             {
  1252.                 $dbh = DBI->connect("DBI:mysql:$dbname:$dbhost:$dbport;mysql_socket=$dbsocket","$dbuser","$dbpass") || die "Database connection not made: $DBI::errstr";
  1253.             }
  1254.  
  1255.             if(index($commandDump,";")>0)
  1256.             {
  1257.                 # more than 1 query
  1258.                 @cad=split(/;/,$commandDump);
  1259.             }
  1260.             else
  1261.             {
  1262.                 @cad=$commandDump;
  1263.             }
  1264.  
  1265.             for($i=0;$i<@cad;$i++)
  1266.             {
  1267.                 if($cad[$i] ne '')
  1268.                 {
  1269.                     $err='';
  1270.                     # replace $$MSD$$ back to ';'
  1271.                     $cad[$i] =~ s/\$\$MSD\$\$/\;/g;
  1272.                     $sth = $dbh->prepare($cad[$i]);
  1273.                     $sth->execute or $err=$sth->errstr();
  1274.                     if ($err ne '')
  1275.                     {
  1276.                         write_log("<span style=\"color:red;\">Executing Query '$cad[$i]' caused an error! MySQL returns: '$err'</span>\n");
  1277.                         PrintOut("<span style=\"color:red;\">Executing Query '$cad[$i]' caused an error! MySQL returns: '$err'</span>\n");
  1278.                     }
  1279.                     else
  1280.                     {
  1281.                         write_log("<span style=\"color:green;font-size:11px;\">Successfully executed Query: $cad[$i]</span>\n");
  1282.                         PrintOut("<span style=\"color:green;font-size:11px;\">Successfully executed Query: $cad[$i]</span>\n");
  1283.                     }
  1284.                     $sth->finish;
  1285.                 }
  1286.             }
  1287.         }
  1288.         else
  1289.         {
  1290.             #Systembefehl
  1291.             $commandDump=substr($commandDump,7);
  1292.             system($commandDump);
  1293.             PrintOut("<p style=\"color:blue;\">$succText ($commandDump)</p>");
  1294.             write_log("$succText ($commandDump)\n");
  1295.         }
  1296.     }
  1297. }
  1298.  
  1299. sub closeScript
  1300. {
  1301.     my ($Start, $Jetzt, $Totalzeit);
  1302.     $Start = $^T; $Jetzt = (time);
  1303.     $Totalzeit=$Jetzt - $Start;
  1304.     ($Sekunden, $Minuten, $Stunden, $Monatstag, $Monat, $Jahr, $Wochentag, $Jahrestag, $Sommerzeit) = localtime(time);
  1305.     $Jahr+=1900;$Monat+=1;$Jahrestag+=1;
  1306.     $starttime=sprintf("%02d",$Monatstag).".".sprintf("%02d",$Monat).".".$Jahr."  ".sprintf("%02d",$Stunden).":".sprintf("%02d",$Minuten).":".sprintf("%02d",$Sekunden);
  1307.     PrintOut("<hr>Everythings is done: closing script <strong>$starttime</strong>");
  1308.     PrintOut("<em>total time used: $Totalzeit sec.</em>");
  1309.     PrintOut("#EOS (End of script)<hr></body></html>");
  1310.     # Datenbankverbindung schliessen
  1311.     $sth->finish() if (defined $sth);
  1312.     ($dbh->disconnect() || warn $dbh->errstr) if (defined $dbh);
  1313. }
  1314.  
  1315. sub trim
  1316. {
  1317.         my $string = shift;
  1318.         if (defined($string))
  1319.         {
  1320.                 $string =~ s/^\s+//;
  1321.                 $string =~ s/\s+$//;
  1322.         }
  1323.         else
  1324.         {
  1325.                 $string='';
  1326.         }
  1327.         return $string;
  1328. }
  1329.  
  1330. sub byte_output
  1331. {
  1332.     my $bytes= shift;
  1333.     my $suffix="Bytes";
  1334.     if ($bytes>=1024) { $suffix="KB"; $bytes=sprintf("%.2f",($bytes/1024));};
  1335.     if ($bytes>=1024) { $suffix="MB"; $bytes=sprintf("%.2f",($bytes/1024));};
  1336.     my $ret=sprintf "%.2f",$bytes;
  1337.     $ret.=' '.$suffix;
  1338.     return $ret;
  1339. }
  1340.  
  1341. sub optimise_tables
  1342. {
  1343.     my $engine='';
  1344.     my $ret=0;
  1345.     $opttbl=0;
  1346.     PrintOut("Optimizing tables:");
  1347.     foreach $tablename (@tablenames)
  1348.     {
  1349.         #optimize table if engine supports optimization
  1350.         $engine=uc($db_tables{$tablename}{Engine});
  1351.         if ( $engine eq "MYISAM" or $engine eq "BDB" or $engine eq "INNODB")
  1352.         {
  1353.             my $sth_to = $dbh->prepare("OPTIMIZE TABLE `$tablename`");
  1354.             $ret=$sth_to->execute;
  1355.             if ($ret)
  1356.             {
  1357.                 PrintOut("<span style=\"color:green;font-size:11px;\">Table ".($opttbl+1)." `$tablename` optimized successfully.</span>");
  1358.                 $opttbl++;
  1359.             }
  1360.             else
  1361.             {
  1362.                 err_trap("<span style=\"color:red;font-size:12px;\">&nbsp;&nbsp;Error optimizing table `$tablename`</span>",1);
  1363.             }
  1364.         }
  1365.     }
  1366.     PrintOut("<span style=\"font-size:11px;\">$opttbl tables have been optimized</span><br>") if($opttbl>0) ;
  1367. }
  1368.  
  1369. # replace in querystring all ';' in VALUES with '$$MSD$$'
  1370. sub replaceQueryStringSimple{
  1371.     my $string = shift(@_);
  1372.    
  1373.     if ($string =~ m#(.*)\'(.*)\;(.*)\'(.*)#){
  1374.         # if found search for more ';'
  1375.         return replaceQueryStringSimple($1.'\''.$2.'$$MSD$$'.$3.'\''.$4);;
  1376.     }else{
  1377.         return $string;
  1378.     }
  1379. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!