Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

Re: DBI -- Synchronize table diffs?

by gmax (Abbot)
on Nov 05, 2002 at 17:00 UTC ( [id://210517]=note: print w/replies, xml ) Need Help??


in reply to DBI -- Synchronize table diffs?

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.
_ _ _ _ (_|| | |(_|>< _|

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://210517]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (3)
As of 2024-07-12 21:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.