#!/usr/bin/perl # (c) Odeurs Dieter # Script to back-up the databases use strict; use warnings; use Getopt::Long; use Net::Ping; use Time::Local; # Variables my $host; my $engine; my $user; my $password; my $port; my $cmd; my @args = (); my $ping; my $all; my $timestamp; my $filename; my $help; my $error; my $result = ""; my @result; # Read Options GetOptions( "host=s" => \$host, "engine=s" => \$engine, "password=s" => \$password, "user=s" => \$user, "port=s" => \$port, "all" => \$all, "help" => \$help ); if ($help){ showUsage(); } #Creating timestamp my ($sec, $min, $hour, $day, $month, $year, $wday, $yday, $isdst) = gmtime(time); $timestamp = sprintf("%4d-%02d-%02d_%02d-%02d", $year + 1900, $month + 1, $day, $hour, $min); #------Check options----- # Check if host is defined and alive if (!defined $host){ print "\nNo host specified\n\n"; showUsage(); } else { print "\nChecking if $host is alive.. \n"; $ping = Net::Ping->new(); if ($ping->ping($host)){ print "$host is alive\n\n"; }else { print "$host is unreachable\n\n"; exit; } $ping->close(); } #If a password is set we need to set PGPASSWORD environment variable if ($password && $password ne ''){ if ($engine eq "postgres" ){ $ENV{'PGPASSWORD'} = $password; } else { push(@args, qq{--password=$password}); } } if ($host && $host ne ''){ push(@args, qq{--host $host}); } if ($port && $port ne ''){ push(@args, qq{--port $port}); } if ($user && $user ne ''){ if ($engine eq "postgres" ){ push(@args, qq{--username $user}); } else { push(@args, qq{--user $user}); } } #Check if database engine is defined and valid if (!defined $engine) { print "Please specify the database engine postgres|mysql\n"; showUsage(); } else { # When a engine is defined the trigt sub will be called if ($engine eq "postgres" ){ postgres(); } elsif ($engine eq "mysql" ){ mysql(); } else { print "Unknown database engine\n"; showUsage(); } } #------------------------------------------------ # Postgres #------------------------------------------------ sub postgres{ if (!$all) { #Find al the databases on the specified host print "Checking databases.. \n"; $cmd = "psql -At @args -c 'SELECT datname FROM pg_database'"; #Print the non-standard databases and store them in an array my @databases; @result = `$cmd`; if ($? !=0) { print "Some errors occured..\n\n"; exit; } print "Databases to back-up: \n"; foreach $result (@result) { chomp $result; if ($result !~ "template" && $result !~ "postgres"){ print " - " . $result . "\n"; push(@databases, $result); } } #Back-up each database foreach my $database (@databases){ #Generate a filename for each database $filename = "/home/dieter/dumps/postgres/" . $database . "_" . $timestamp . ".sql"; $cmd = "pg_dump -C @args $database > $filename"; print $cmd . "\n" ; @result = `$cmd`; if ($? !=0) { print "Done but with errors\n\n"; exit; }else { print "DONE \n"; } } #-------All database in one file------ } else { $filename = "/home/dieter/dumps/postgres/" . "Fulldump" . "_" . $timestamp . ".sql"; print "Taking dump of all databases...\n"; $cmd = "pg_dumpall @args > $filename"; @result = `$cmd`; if ($? !=0) { print "Done but with errors\n\n"; } else { print "Dump saved in $filename\n\n"; } } } #----------------------------------------------- # MySQL #----------------------------------------------- sub mysql{ #-------One database in one file------ if (!$all) { #list databases and exclude the default my @databases; print "Checking databases..\n"; $cmd = "mysql @args -Bse 'show databases'"; @result = `$cmd`; if ($? !=0) { print "Some errors occured..\n\n"; exit; } print "Databases to back-up: \n"; foreach $result (@result) { if ($result !~ "information_schema" && $result !~ "mysql"){ print " - $result \n"; push (@databases, $result); } } push(@args, qq{--single-transaction}); #back-up of the selected databases foreach my $database (@databases){ chomp $database; $filename = "/home/dieter/dumps/mysql/" . $database . "_" . $timestamp . ".sql"; print "Taking dump of $database ...\n"; $cmd = "mysqldump @args $database > $filename"; @result = `$cmd`; if ($? !=0) { print "Done but with errors\n\n"; exit; }else { print "Dump saved in $filename\n"; } } #-------All database in one file------ } else { $filename = "/home/dieter/dumps/mysql/" . "Fulldump_" . $timestamp . ".sql"; print "Taking dump of all databases...\n"; $cmd = "mysqldump @args --all-databases > $filename"; @result = `$cmd`; if ($? !=0) { print "Done but with errors\n\n"; exit; }else { print print "Full dump saved in $filename\n"; } } print "Dump completed!\n\n"; } #----------------------------------------------- # Show usage #----------------------------------------------- sub showUsage{ print "\n------------------------------------------\n"; print "-- Usage for the database backup script --\n"; print "------------------------------------------\n"; print "\n--host \n"; print " Specify the host IP\n\n"; print "--engine \n"; print " Specify the database engine. This could be postgres or mysql. \n\n"; print "--password \n"; print " Give the password needed to connect with the database server\n\n"; print "--user \n"; print " Specify user to connect to the database server.\n"; print " The user must have permissions to connect to the database(s)\n\n"; print "--port \n"; print " If another port than the standard must be used to connect you could \n"; print " specify this with the --port option\n\n"; exit; } exit;