How to backup multiple databases using Perl
5.00/5 (1 vote)
How to backup multiple databases using Perl
Introduction
During the last 5 months, I have been learning Perl, so I decided to create a generic script to backup several databases, and be able to use it from other languages as well.
About the script:
The following script will create and execute a command line with the user provided options for the specified database in the option engine. The final result will be a backup file stored in the path you provided in the option dir.
In order to execute the script from a command line, you just need to create the command-line and define the required options, Example:
perl backup.pl utility="C:\Program Files (x86)\PostgreSQL\9.0\bin\pg_dump.exe" engine=postgres host=localhost port=5432 dbname=test user=dbuser password=user's password dir="D:\backup"
To backup another database, you just need to change the engine option and set the other options.
Script code
# -------------------------------------------------------------------------
# Package
# backup.pl
#
# Purpose
# Creates backup files for MySQL, SQL Server, Oracle and PostgreSQL
#
# Engineer
# Carlos Luis Rojas Aragones
#
# -------------------------------------------------------------------------
package backup;
# -------------------------------------------------------------------------
# Includes
# -------------------------------------------------------------------------
use utf8;
use strict;
use warnings;
use Getopt::Long;
# -------------------------------------------------------------------------
# Variables
# -------------------------------------------------------------------------
my $utility;
my $dbName;
my $host;
my $port;
my $dbEngine;
my $destinationPath;
my $user;
my $password;
# -------------------------------------------------------------------------
# Read Options
# -------------------------------------------------------------------------
GetOptions(
"utility=s" => \$utility,
"engine=s" => \$dbEngine,
"host=s" => \$host,
"port=i" => \$port,
"dbname=s" => \$dbName,
"user=s" => \$user,
"password=s" => \$password,
"dir=s" => \$destinationPath,
) or die;
sub main{
my @args = ();
if($dbEngine eq "mysql"){
@args = mysqlCommandLine();
}elsif($dbEngine eq "sqlserver"){
@args = sqlserverCommandLine();
}elsif($dbEngine eq "oracle"){
@args = oracleCommandLine();
}elsif($dbEngine eq "postgres"){
@args = postgresCommandLine();
}
if(@args > 0){
my $commandLine = createCommandLine(\@args);
print $commandLine;
my $result = qx{$commandLine};
print $result;
}else{
print "Unable to backup the database";
exit 1;
}
return;
}
# -------------------------------------------------------------------------
# Mysql
# -------------------------------------------------------------------------
sub mysqlCommandLine{
my @args = ();
if($utility && $utility ne ""){
push(@args, '"' . $utility. '"');
}
if($host && $host ne ""){
push(@args, qq{-h "$host"});
}
if($user && $user ne ''){
push(@args, qq{-u "$user"});
}
if($password && $password ne ''){
push(@args, qq{-p"$password"});
}
if($port && $port ne ''){
push(@args, qq{-P $port});
}
if($dbName && $dbName ne ''){
push(@args, $dbName);
}
if($destinationPath && $destinationPath ne ''){
if(!($destinationPath =~ m/\.sql/)){
$destinationPath .= ".sql";
}
push(@args, qq{> "$destinationPath"});
}
return @args;
}
# -------------------------------------------------------------------------
# Sql Server
# -------------------------------------------------------------------------
sub sqlserverCommandLine{
my $backupStatement = "BACKUP DATABASE";
my @args = ();
if($utility && $utility ne ""){
push(@args, '"' . $utility. '"');
}
if($user && $user ne ''){
push(@args, qq{-U "$user"});
}
if($password && $password ne ''){
push(@args, qq{-P "$password"});
}
if($dbName && $dbName ne ''){
push(@args, qq{-d $dbName});
$backupStatement .= " $dbName TO DISK = '";
}
if($host && $host ne ""){
push(@args, qq{-S "$host"});
}
if($destinationPath && $destinationPath ne ''){
if(!($destinationPath =~ m/\.dat/)){
$destinationPath .= ".dat";
}
$backupStatement .= $destinationPath;
}
if($backupStatement && $backupStatement ne ''){
push(@args, qq{-Q "$backupStatement'"});
}
return @args;
}
# -------------------------------------------------------------------------
# Oracle
# -------------------------------------------------------------------------
sub oracleCommandLine{
my @args = ();
my $authString = "";
if($utility && $utility ne ''){
push(@args, '"' . $utility. '"');
}
if($user && $user ne ''){
$authString = "userid=$user";
}
if($password && $password ne ''){
if($host && $host ne ""){
$authString .= "/$password@".$host;
}else{
$authString .= "/$password";
}
}
if($authString && $authString ne ''){
push(@args, $authString);
}
if($destinationPath && $destinationPath ne ''){
# dump file
if(!($destinationPath =~ m/\.dmp/)){
$destinationPath .= ".dmp";
}
push(@args, qq{file='$destinationPath'});
#log file
$destinationPath =~ s/.dmp/.log/;
push(@args,qq{log='$destinationPath'});
}
if($user && $user ne ''){
push(@args, "owner=$user");
}
push(@args, qq{statistics="none"});
return @args;
}
# -------------------------------------------------------------------------
# Postgres
# -------------------------------------------------------------------------
sub postgresCommandLine{
my @args = ();
if($utility && $utility ne ""){
push(@args, '"' . $utility. '"');
}
if($host && $host ne ""){
push(@args, qq{--host $host});
}
if($port && $port ne ''){
push(@args, qq{--port $port});
}
if($user && $user ne ''){
push(@args, qq{--username $user});
}
if($password && $password ne''){
#we need to set the PGPASSWORD environment variable
$ENV{'PGPASSWORD'} = $password;
}
push(@args, "--format custom --blobs --verbose");
if($destinationPath && $destinationPath ne ''){
if(!($destinationPath =~ m/\.backup/)){
$destinationPath .= ".backup";
}
push(@args, qq{--file "$destinationPath"});
}
if($dbName && $dbName ne ''){
push(@args, qq{"$dbName"});
}
return @args;
}
# -------------------------------------------------------------------------
# Returns a ready to execute command line
# -------------------------------------------------------------------------
sub createCommandLine {
my ($arr) = @_;
return join(" ", @$arr);
}
main();
1;
Parameters
I chose those options because they are "generic" and almost every database supports them; You could easily modify the script to provide special features. The script can be called from a commandline and receives the following parameters:
- utility: Absolute path to the back up utility (osql, exp, mysqldump or pgdump).
- engine: Provide the database engine you want to use (mysql, sqlserver, oracle or postgres)
- host: Database server name.
- port: Database server port.
- dbname: Name of the database you want to backup.
- user: Database user.
- password: User's password.
- dir: Place where you want to store the backup file example: "D:\backup"
Using the script from C#
class BackUp
{
public BackUp(){}
public void CreateBackup(string scriptLocation, string args)
{
try
{
System.Diagnostics.ProcessStartInfo p = new System.Diagnostics.ProcessStartInfo(scriptLocation);
p.Arguments = args;
System.Diagnostics.Process proc = new System.Diagnostics.Process();
proc.StartInfo = p;
proc.Start();
proc.WaitForExit();
}
catch (Exception e)
{
//handle the possible exceptions here
}
}
}
Using the class:
//options
string path = Directory.GetCurrentDirectory() + "\\backup.pl";
string utility = @"""C:\Program Files\PostgreSQL\9.0\bin\pg_dump.exe""";
string dbEngine = "postgres";
string host = "localhost";
int port = 5432;
string dbName = "Test";
string destinationPath = @"""D:\backup""";
string user = "postgres";
string password = "12345";
//create the args string
StringBuilder options = new StringBuilder();
options.Append("-utility=").Append(utility).Append(" ");
options.Append("-engine=").Append(dbEngine).Append(" ");
options.Append("-host=").Append(host).Append(" ");
options.Append("-port=").Append(port).Append(" ");
options.Append("-dbname=").Append(dbName).Append(" ");
options.Append("-user=").Append(user).Append(" ");
options.Append("-password=").Append(password).Append(" ");
options.Append("-dir=").Append(destinationPath).Append(" ");
//Instance the class we created before
BackUp oBackUp = new BackUp();
oBackUp.CreateBackup(path, options.ToString());
Conclusion:
Perl is a powerful language that allows us to create great things fast and easy, and it can run in the most popular operating systems.