previously aku kene buat intergration utk one of aku punya client. maklumat asal kene buat intergration dari client2 (ada dalam 17 system/server) yg run atas platform linux. but bila dah start project, baru tau ada 2/3 server run on top of Ms Windows. So aku terpaksala pikirkan camna nak buat untuk dump mysql dari client tu dan send ke main server. kalau linux base mmg pakai scp jer.. lebih mudah.
so utk windows ni aku terpaksa gunakan Net::FTP yg aku rasa paling memudahkan kerja aku. oleh kerana x nak mengcomplicatedkan kerja dikemudian hari aku compile kan perl script kepada EXE format utk memudahkan ia dilarikan diatas windows base system. aku gunakan
Perl2EXE.
aku pisahkan config file dgn main file utk memudahkan configuration tanpa perlu compile byk kali.
source code dibawah.
client.pl:
#!/usr/bin/perl
use Net::FTP;
require "config.cfg";
#########################do not change anthing below this line, unless u know what are u doing##################################
my($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
##########################dump mysql database#################################
$year += 1900 ; $mon += 1;
$mysqldumpfile = $parm{mysqldatabase} . "-" . sprintf("%04d%02d%02d", $year, $mon, $mday, ) . ".sql" ;
$parm{mysqlpass} = "-p$parm{mysqlpass}" if $parm{mysqlpass};
print "dumping mysql db\n";
system( "$parm{mysqlbin} --opt -h$parm{mysqlhost} -u$parm{mysqluser} $parm{mysqlpass} --port=$parm{mysqlport} $parm{mysqldatabase} --table $parm{tabletobackup} > $parm{mysqldumpdir}/$mysqldumpfile");
my @fileinfos = stat "$parm{mysqldumpdir}/$mysqldumpfile";
open(STATUS,">$parm{mysqldumpdir}/status.log");
print STATUS "File Name :". $mysqldumpfile . "\n";
print STATUS "File Size :". $fileinfos[7] . " Bytes\n";
print STATUS "Tansfer Date :". sprintf("%04d%02d%02d", $year, $mon, $mday, ) . "\n";
close STATUS;
print "FTP dump file($mysqldumpfile) to server\n";
#########################FTP to server##################################
my %file = (
source => "$parm{mysqldumpdir}/$mysqldumpfile",
dest => "$parm{ftpclient}/$mysqldumpfile",
statusfile => "$parm{mysqldumpdir}/status.log",
statusfiledest => "$parm{ftpclient}/status.log",
log => 'ftpgp.log',
);
open (LOG, ">>$file{log}") or die "Error opening $file{log}: $!";
LogIt('noexit', "\n---------------------------------------------------\nStarted run ");
print LOG"
Sourcefile $file{source}
Destination file $file{dest}
Destination server $parm{ftphost}
Destination user $parm{ftpuser}
Log file $file{log}
Upload script $0
Perl $]
Net::FTP $Net::FTP::VERSION
Local OS $^O\n",
;
unless (-r $file{source} && -T _) {
LogIt('exit',
"Error with source file $file{source}.\nIs not readable or ASCII\n"
);
}
my $ftp = Net::FTP->new($parm{ftphost}) or LogIt('exit', "Error connecting.\nNetwork or server problem?\n");
$ftp->login($parm{ftpuser}, $parm{ftppass}) or LogIt('exit', "Error logging in.\nHas ID or password changed?\n");
$ftp->mkdir("$parm{ftpclient}");
$ftp->ascii();
##################################
print "Put Status File\n";
$ftp->delete($file{statusfiledest});
$ftp->put($file{statusfile}, $file{statusfiledest}) or LogIt('exit', "Error uploading Status file.\nDisk space or permissions problems\n");
$ftp->ascii();
##################################
print "Put Dump File\n";
$ftp->delete($file{dest});
$ftp->put($file{source}, $file{dest}) or LogIt('exit', "Error uploading.\nDisk space or permissions problems\n");
##################################
open(STATUS,">>$parm{mysqldumpdir}/status.log");
print STATUS "Status :Success\n";
close STATUS;
##################################
print "Put Status File Completed\n";
$ftp->ascii();
$ftp->delete($file{statusfiledest});
$ftp->put($file{statusfile}, $file{statusfiledest}) or LogIt('exit', "Error uploading Status file.\nDisk space or permissions problems\n");
##################################
$ftp->quit() or LogIt('exit', "Error disconnecting.\n???\n");
LogIt('noexit', "Finished FTP put ");
close LOG or die "Error closing $file{log}: $!";
unlink("$file{source}");
unlink("$file{statusfile}");
print "All operation completed\n";
exit;
######################################################################
# Print message with date+timestamp to logfile.
# Abort program if instructed to.
sub LogIt {
my $exit = $_[0];
my $msg = $_[1];
print LOG "$msg";
print LOG $year,"-",$mon,"-$mday $hour:$min:$sec\n";
;
exit if ($exit eq 'exit' );
}
######################################################################
config.cfg:
%parm = (
ftpclient => 'clientID',
ftphost => 'ftp.somehost.com',
ftpuser => 'ftpuser',
ftppass => 'ftppassword',
mysqlbin => 'C:/path/to/mysql/bin/mysqldump',
mysqldumpdir => '.',
mysqlhost => 'localhost',
mysqlport => '3306',
mysqluser => 'mysqlusername',
mysqlpass => 'mysqlpassword',
mysqldatabase => 'databasetobackup',
tabletobackup => 'tabletobackupsplitwithspace',
);
1;
requirement :
Perl 5.6>
Net:::FTP
how it's works:
Dump MySQL
-> send sending status
-> FTP MySQL dump file
-> send complete status
-> Delete temporary files cerated.
after convert to EXE format, u only used this 2 file without need to install perl into ur client system. changes can only be done at config.cfg.
happy coding...
