Re: DBI -- Synchronize table diffs?

[gmax]

Keeping in mind the risks that VSarkiss has already explained, there are two possibilities.
Short (cheating) answer, without using Perl :-)

$ mysqldump --add-drop-table -h host1 db1 | mysql -h host2 db2

If you want to add one column without erasing the data, you can modify your master database and then do like this:

# copies the target data without the structure. Creates a
# complete SQL statement including all the field names.
$ mysqldump -t -c -h host1 db2 > db2.sql 

# copies the structure from db1 to db2, without data
$ mysqldump -d -h host1 db1 | mysql -h host2 db2

# reinserts the data
$ mysql -h host2 db2 < db2.sql

Notice that this code does not work if you want to remove a column. If you just add or modify a column, it should work fine.

The (long) Perl answer is more flexible, exploiting some DBI features to check the differences between two databases and eventually fix them.
This node shows how to do a basic comparison between two databases.
For something more structure oriented, the following script can achieve most of what you want. It can determine if one table is in one database and missing from the other one, and warn you if there are differences.
Since you want to have a copy of the same database in many locations, I assume that you you want all the "satellites" to be like the central one. If this is the case, it should be easy to modify the check_diff sub to drop the offending table and re-create it.
To create an "ALTER TABLE" statement, you should loop inside the table structure. An example of how to do it is in the code of DBSChema::Normalizer.
If your goal is to have the same table structure in both databases, you can just run this script twice:

$ ./dbdiff host1 db1 host2 db2
$ ./dbdiff host2 db2 host1 db1

This way, tables that did not exist in host 1 will be created in host 2 and vice versa.
Notice that the script does not modify your databases, but only creates the necessary statements that you should execute.
It is not a complete solution, but it should be a good start.
HTH

#!/usr/bin/perl -w
use strict;
use DBI;
sub help {
        print STDERR "syntax dbidiff host1 db1 host2 db2\n";
        print STDERR "reverse the order of hosts and DBs for",
                "a complete synch\n";
        exit;
}

my $host1 = shift || help();
my $db1 = shift || help();
my $host2 = shift || help();
my $db2 = shift || help();

#change connection parameters to suit your needs.
#username and password are taken from the configuration
#file in the user's home dir
my $dbh1 = DBI->connect("DBI:mysql:$db1;host=$host1;"
    ."mysql_read_default_file=$ENV{HOME}/.my.cnf",
        undef,undef,{RaiseError => 1});

my $dbh2 = DBI->connect("DBI:mysql:$db2;host=$host2;"
    ."mysql_read_default_file=$ENV{HOME}/.my.cnf",
        undef,undef,{RaiseError => 1});

# reads the table list from both databases

my %tables1 = map {$_,1} $dbh1->tables();
my %tables2 =  map {$_, 1} $dbh2->tables();

sub get_table_struct {
        my $host_num = shift;
        my $dbname = shift;
        my $tablename = shift;
        my $dbh = $host_num == 1 ? $dbh1 : $dbh2;
        my $sth = $dbh->prepare(qq{SHOW CREATE TABLE $dbname.$tablenam
+e});
        $sth->execute();
        my ($dummy, $table_creation) = $sth->fetchrow_array();
        $sth->finish();
        return $table_creation;
}

sub copy_table {
        my $tablename = shift;
        print "# New table to create on $host2 - $db2\n";
        print "#\n# CREATING TABLE $tablename\n#\n\n";
        print get_table_struct(1, $db1,$tablename), "\n";
}

sub check_diff {
        my $tablename = shift;
        my $tc1 = get_table_struct(1, $db1, $tablename);
        my $tc2 = get_table_struct(2, $db2, $tablename);
        if ($tc1 ne $tc2) {
                print "# `$tablename` is different\n";
        #uncomment  the next two lines to fix the differences "brute f
+orce"
        #print "DROP TABLE $tablename;\n";
        #print "$tc1\n"        }
}

#
# The main loop. Here the two databases get compared.
#
for (keys %tables1) {
        if(exists $tables2{$_}) {
                check_diff($_);
        }
        else {
                copy_table ($_);
        }
}

$dbh1->disconnect();
$dbh2->disconnect();

update Jan 22, 2003 I found an utility on CPAN, MySQL::Diff , which does exactly what is requested.

 _  _ _  _  
(_|| | |(_|><
 _|