in reply to DBI -- Synchronize table diffs?
If you want to add one column without erasing the data, you can modify your master database and then do like this:$ mysqldump --add-drop-table -h host1 db1 | mysql -h host2 db2
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.# 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
This way, tables that did not exist in host 1 will be created in host 2 and vice versa.$ ./dbdiff host1 db1 host2 db2 $ ./dbdiff host2 db2 host1 db1
update Jan 22, 2003 I found an utility on CPAN, MySQL::Diff , which does exactly what is requested.#!/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();
_ _ _ _ (_|| | |(_|>< _|
|
|---|