#!/usr/local/bin/perl -w

use strict;
use DBI;
use Data::Dumper;

# Connect to database
my $dbh;
eval {
    $dbh = DBI->connect("dbi:mysql:;mysql_read_default_file=/home/user
+/.my.cnf", 
                        "",
                        "",
                        { RaiseError => 1, PrintError => 0 }
                       );
    1;
} or die "Couldn't connect to database: $DBI::errstr";

# Get a list of the tables
my @table_names;
eval {
    @table_names = @{$dbh->selectcol_arrayref("SHOW TABLES")};
    1;
} or die "Couldn't retrieve list of MySQL tables: $DBI::errstr";

# Get data from each table
my (@tables, $table);
eval {
    foreach $table (@table_names) {
        # Record how the table is layed out
        my $layout = $dbh->selectall_arrayref("DESCRIBE $table");
        # Store the data in a 2D array reference
        my @fields = map ${$_}[0], @{$layout};
        my $data = $dbh->selectall_arrayref("SELECT " .
                                            (join ',', @fields) .
                                            " FROM $table");
        push @tables, [$layout, $data];
    }
    1;
} or die "Failed while obtaining data from $table: $DBI::errstr";

# Print data to STDOUT
my %database;
@database{@table_names} = @tables;

$Data::Dumper::Useqq = 1;
$Data::Dumper::Indent = 0;
print Data::Dumper->Dump([\%database], ['database']);

eval {
    $dbh->disconnect;
    1;
} or die "Couldn't disconnect properly from database: $DBI::errstr";
[download]

Removed by the admins... strange, isn't it? I asked them about how I could get a copy of my database, and they said I couldn't, flat out. Got me to thinking that if I ever tried to switch providers perhaps they wouldn't give me back my data. In any case, this seemed an interesting thing to try through DBI.

Yikes!

Reading this made my hair stand on end. I used to work with a client who did exactly that (refused to give his client their data). We left his sorry $#& in a hurry.

Russ

Since you're using MySQL qhy not just use its built-in functions for dumping and loading data? In my experience built-in DB tools such as there are often significantly faster than iterating through a DB by hand for replication (typically 10x faster if not more). mysqldump does a great job of dumping a snapshot of a MySQL DB. It can be configured to dump the schemas along with the contents. mysqlimport can then be used to load the dump file back into another MySQL on another server (after you move the dump file over). At the very least, since your source DB is MySQL you can use mysqldump and load from that dump into the other DB. I've used mysqldump and mysqlimport in conjunction with dumps to/from other DB's (most DB's have similar tools) and had very good success. Both tools are very configurable to handle a variety of common methods of formatting the data. IMHO you should alway's use a DB's native import and export functions, if necessary use a perl program to translate the format if both don't use the same format.

If you are bound-and-determined to do this in perl, you are going to have trouble because there is no standard cross-db way to get a listing of all the tables and to dump their schemas.