· 10 years ago · Jun 13, 2016, 10:03 AM
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
20my $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
28my $absolute_path_of_configdir="/home/zzzzzzzz/www/administ/back/work/config/";
29my $cgibin_path=""; # this is needed for MIME::Lite if it is in cgi-bin
30my $default_configfile="mysqldumper.conf.php";
31
32########################################################################################
33# nothing to edit below this line !!!
34########################################################################################
35# import the necessary modules ...
36use strict;
37use warnings;
38use DBI;
39use File::Find;
40use File::Basename;
41use CGI::Carp qw(warningsToBrowser fatalsToBrowser);
42warningsToBrowser(1);
43use CGI;
44use Data::Dumper;
45use Getopt::Long;
46
47
48########################################################################################
49use 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
89sub 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 ...
100my $eval_in_died;
101$mod_gz=0;
102$mod_ftp=0;
103$mod_mime=0;
104$mod_ftpssl=0;
105push (@INC, "$cgibin_path");
106
107eval { $eval_in_died = 1; require Compress::Zlib; };
108if(!$@){
109 $mod_gz = 1;
110 import Compress::Zlib;
111}
112eval { $eval_in_died = 1; require Net::FTP; };
113if(!$@){
114 $mod_ftp = 1;
115 import Net::FTP;
116}
117eval { $eval_in_died = 1; require Net::FTPSSL; };
118if(!$@){
119 $mod_ftpssl = 1;
120 import Net::FTPSSL;
121}
122eval { $eval_in_died = 1; require MIME::Lite; };
123if(!$@){
124 $mod_mime = 1;
125 import MIME::Lite;
126}
127
128#include config file
129$conffile="";
130
131#read args from command
132GetOptions ("config=s" => \$conffile, "html_output=s" => \$html_output);
133if (!defined $html_output) { $html_output=0; }; # suppress HTML Output
134
135#called via browser or cmd
136if($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
158PrintHeader();
159
160# Security: try to detect wether someone tries to include some external configfile
161die "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
164if($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);
202if($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
210if (!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}
213closedir(DIR);
214
215#add trailing slash to confdir
216if(substr($absolute_path_of_configdir,-1) ne "/") {
217 $absolute_path_of_configdir=$absolute_path_of_configdir."/";
218}
219
220#add conffile extension
221if (substr($conffile,length($conffile)-5,5) eq '.conf') { $conffile.='.php'; };
222if (substr($conffile,length($conffile)-9,9) ne '.conf.php') { $conffile.='.conf.php'; };
223
224# load configuration file
225$datei=$absolute_path_of_configdir.$conffile;
226open(CONFIG,"<$datei") or die "\nI couldn't open the configurationfile:".$datei."\nFile not found or not accessible!\n\n";
227while (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}
235close(CONFIG);
236
237if ($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
243PrintOut("<span style=\"color:#0000FF;\">Configuration '".$conffile."' loaded successfully from ".$config_read_from.".</span>");
244if($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}
250if($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}
256if($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}
263if($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
271write_log("***********************************************************************\n");
272write_log("Starting backup using Perlscript version $pcd_version (using perl ".GetPerlVersion().")\n");
273PrintOut("Starting backup using Perlscript version $pcd_version (using perl ".GetPerlVersion().")\n");
274write_log("Using configuration $conffile\n");
275
276#now do the dump
277
278#more than one db
279if($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}
289else
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
309if($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}
319closeScript();
320if ($html_output==0) { print "\nEnd of Cronscript\n"; }
321
322##############################################
323# Subroutinen #
324##############################################
325sub 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
654sub 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
673sub 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
692sub 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
761sub 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
791sub 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
854sub 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
987sub 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
1038sub 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
1067sub 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
1127sub 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
1195sub 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
1222sub 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
1299sub 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
1315sub 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
1330sub 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
1341sub 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;\"> 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$$'
1370sub 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}