#!/usr/local/bin/perl

#######################
# dbCopy.pl 
# Version 1.0
# This utility can backup the data from one mysql database to another.
# It requires that the table definitions on your production # database
+ are in place in the backup database.
# TODO:    
# 1.  Parse Table Definitions to issue create table
#     statements to backup db.
# 2.  Extend to other DB Platforms
# 3.  Write in Package Format, possibly as DBI::Utils or something
######################    

use DBI;
main();

# Initialize values for DBI connection.  
# $dbHref is the DB to be backed up [eg, production].
# $dbBakHref is the backup database
# Change the hash values to your own needs.

sub initDBHash
{
    my ($dbHRef,$dbBakHRef) = @_;
    %$dbHRef =          (
        dbName          => "dbName",
        dbUser          => "dbUser",
        dbPWord         => "dbPass"
                        );
    %$dbBakHRef =       (
        dbName          => "dbBakName",
        dbUser          => "dbBakUser",
        dbPWord         => "dbBakPass"
                        );
}

# Create DBI object in the accepted hash ref.
sub dbConnect
{
    my $hRef = shift;
    $$hRef{connect}=DBI->connect ("dbi:mysql:$$hRef{dbName}",
           "$$hRef{dbUser}","$$hRef{dbPWord}",
           {AutoCommit=>1,
            PrintError=>0});
}

sub main
{
    my (%dbHash,%dbBakHash);
    initDBHash (\%dbHash,\%dbBakHash);

    #Connect to each DB
    for (\%dbHash,\%dbBakHash)
    {
        dbConnect($_);
    }

    #Get Table Names
    my $tableRefs = getData ("show tables",\%dbHash);

    #Loop through each table
    foreach my $table (@$tableRefs)
    {
    #Get data from current table
        my $rowRefs = getData ("select * from $$table[0]",\%dbHash);

    #Build bind string, using map to get correct number of ?'s -- ?,?,
+?,?.....
        my $str = join (",",map {"?"}(0..$#{$rowRefs->[0]}));

        #Create Handle for DBBackup insert, using $str bind values.
        my $insert = $dbBakHash{connect}->prepare ("insert into $$tabl
+e[0] values ($str)");
 
    #Execute the insertion over each row.
        foreach my $row (@$rowRefs)
        {
             $insert->execute (@$row);
        }
    }

    #Disconnect
    $dbHash{connect}->disconnect();
    $dbBakHash{connect}->disconnect();
}

#Prepare,execute,and retrieve data from SQL statement.
sub getData
{
    my ($sql,$dbHash) = @_;
    my $query = $$dbHash{connect}->prepare("$sql");
    $query->execute();
    my $rows = $query->fetchall_arrayref();
    $rows;
}
[download]